|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:42 AM
Points: 40,
Visits: 47
|
|
Hi friends,
Can anyone help me out how to extract data from multiple excel sheets (same excel doc having multiple sheets) using SSIS 2005
Thanks,
Purushoth
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Friday, March 19, 2010 3:33 AM
Points: 2,551,
Visits: 2,412
|
|
1. You need 1 Data Flow Task, containing an EXCEL source Connection and a SQL destination connection
2. Script Task to get list of sheets within the excel file
3. For Each ADO Loop Enumerator to loop through sheets
You also need to dynamically set source connection using variables/expressions. Currently, I don't have a working example/sample of it. May be you can look BOL for information on above.
--Ramesh
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:42 AM
Points: 40,
Visits: 47
|
|
Ramesh,
Thanku very much for ur help.. i ll try and get back to u if i get any doubt..
Happy Programming!!!
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, November 22, 2009 2:12 PM
Points: 1,
Visits: 23
|
|
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.
Hope this helps.
-- Adnan
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 8:44 AM
Points: 4,
Visits: 26
|
|
I made an example for those people that still need:
http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx
I hope this help.
regards,
Pedro
www.pedrocgd.blogspot.com
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 8:23 AM
Points: 179,
Visits: 393
|
|
Pedro Perfeito (8/6/2009)
I made an example for those people that still need:
http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx
I hope this help.
regards,
Pedro
www.pedrocgd.blogspot.com
That URL no longer seems to work, I don't suppose you still have that information posted somewhere else?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 8:44 AM
Points: 4,
Visits: 26
|
|
The site is temporarly down... I can send the paper I made to all those interesed on it.
Check my email in my BLOG profile and I imediatly send it to you while the site is down.
Regards,
Pedro
http://www.pedrocgd.blogspot.com
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, January 04, 2010 9:03 PM
Points: 1,
Visits: 0
|
|
Hi there,
Thanks for posting the link http://technet.microsoft.com/en-us/library/ms345182.aspx.
However, you also wrote that we can add an Excel Source task in the Data Flow afterwards. And in the Excel Source, select the excel connection that we've already made.
I think it's incorrect. If we follow the link, the connection we create is an ADO.NET connection, not a OLE DB connection, which is what the Excel Source required.
Could you please correct that and advise what to do after the Foreach Loop is configured?
Thanks heaps.
|
|
|
|
