January 18, 2012 at 6:10 am
Hello -
I'm looking to import multiple excel sheets in one file into multiple SQL tables using SSIS, at the end of the package run, I want to move the file to my archive folder.
Any help would be greatly appreciated.
January 18, 2012 at 7:01 am
subahan_syed (1/18/2012)
Hello -I'm looking to import multiple excel sheets in one file into multiple SQL tables using SSIS, at the end of the package run, I want to move the file to my archive folder.
Any help would be greatly appreciated.
This is a straightforward SSIS task for which there are numerous resources out there already. Google is your friend - I tried searching for "import multiple excel sheets in one file into multiple SQL tables using SSIS" and got lots of relevant hits.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2012 at 7:04 am
There is a reasonably useful article on TechNet that describes most of the work needed to import data from multiple worksheets in an Excel file with SSIS.
http://technet.microsoft.com/en-us/library/ms345182.aspx
However, the last step in this article leaves you with a number of assumptions. Once you have followed all the steps listed in the technet article, this is what you have to do.
1. Add a Data Flow task to the For Each loop container.
2. In the Data Flow container, add an Excel Source component.
3. Set the OLE DB connection manager to the Excel connection manager that you would've already setup.
4. Change Data access mode for the Excel Source component to 'Table name or view name variable'. This will allow you to use the variable name to specify the name of worksheet, instead of hard coding it.
Note: You may want to use a specific sheet within the Excel file first in order for SSIS to pickup the various column names. Once you are done with the column mapping, you can change this setting back to variable name.
Now you can continue on with whatever you want to do with the data that is read from each worksheet, one worksheet at a time.
January 18, 2012 at 9:50 am
My apologies.
I'm new to SSIS. I got partial information on google. Any links or videos where I can get a step by step download.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply