October 5, 2010 at 6:21 pm
Hi All
I am a Newbie. I am trying to extract data from multiple worksheets in an excel file using SSIS. Can Somebody guide me through this?
Satish
October 5, 2010 at 7:26 pm
Utilize an Execute SQL Task (connected to an Excel Connection), and run a "CREATE TABLE " statement. If the xls file doesn't already exist, it will be created. It will also create a worksheet AND a named range of the name specified in the CREATE TABLE statement (the difference is the worksheet has a $ appended to the end).
Repeat for each worksheet that you want to create.
For each worksheet, use a Data Flow task, with a Source and Destination. If your source has char/varchar columns, you will need to use a derived column task to make nvarchar columns. (Alternatively, for your source use a query and convert(nvarchar()) all the char/varchar columns).
Note that the create table statement needs to use the "`" instead of the ' character, and the data types need to be what excel supports.
The easiest way to set up your initial package is to utilize the Export Wizard, and export your source to an Excel file. Save it as a package, and you'll have a good baseline to work with for all the remaining worksheets.
Hope this helps. Come on back if you have any more questions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 5, 2010 at 7:28 pm
And in re-reading your question, are you trying to import data from multiple worksheets of an Excel file into Sql Server?
If so, most of the previous post still applies. Use the Import Wizard to create a package that will load in one of the worksheets. Again, save as a package. Then copy the data flow tasks to load each of the worksheets.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 5, 2010 at 8:58 pm
Hi Wayne Thank You Much for the answer
I was wondering is their any way we can accomplish this task using the for each loop container ( i am not sure i might be wrong ) that will read all the worksheets??
Thank You In Advance
October 5, 2010 at 9:26 pm
SSIS can only work with known worksheets.
However, you CAN use a Script Task, use some Excel Automation, and get a list of worksheets. However, utilizing Excel Automation on a Server is not supported by MS.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply