SSIS - Import Excel files into SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS - Import Excel files into SQL

Author

Topic

onebite2
Starting Member

2 Posts

Posted - 2011-12-02 : 12:44:26

SSIS process
1. ForEachLoop container
2. Execute Sql task within the container.
3. Execute Stored Proc, which would import the *.xls file into sql.
Package level Variable: @FileName

Getting errors executing the package.
[Execute SQL Task] Error: Executing the query "Execute ImportExcelIntoSQL ?"
failed with the following error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
does not contain the table "Sheet1$". The table either does not exist or the current user does not
have permissions on that table.". Possible failure reasons: Problems with the query, "ResultSet" property
not set correctly, parameters not set correctly, or connection not established correctly.

Stored Procedure:
Create Procedure ImportExcelIntoSQL
@FileName varchar(30)
AS
Set NoCount ON

DECLARE @sqlStr VARCHAR(4000);

Set @sqlStr='select * into ' + @FileName + ' FROM
OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=C:\ExcelFiles\' + @FileName + ''', [Sheet1$])'

exec(@sqlStr)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 12:52:21

how are you giving parameter name inside parameter mapping tab of execute sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

onebite2
Starting Member

2 Posts

Posted - 2011-12-02 : 13:48:02

I found solution. All my excel files need to have Sheet1 but some of them don't have that.

To your question i pass Filename 0 for(new parameter).

quote:
Originally posted by visakh16

how are you giving parameter name inside parameter mapping tab of execute sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 00:26:06

oh ok...so I think that caused OPENROWSET to fail??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources