Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Load data from multiple tabs in excel to different tables Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 12:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
Hi,

I have an Excel File and has 3 tabs.

Need to load the 3 tabs data to 3 different tables using SSIS 2005.

Could you please let me know the steps?


Regards

Sqlstud


Post #1371223
Posted Thursday, October 11, 2012 8:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 1,184, Visits: 1,219
Have you tried creating three seperate Excel data sources?
Post #1371534
Posted Thursday, October 11, 2012 9:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:57 AM
Points: 504, Visits: 1,869
You'll need to execute a SQL task for each tab using an EXCEL connection manager pointing to the output spreadsheet. The SQL task contains the DDL to do the CREATE TABLE used to generate the Excel tab. Then complete a separate data flow to populate each tab. Wire them up sequentially and you'll generate a multi-tab spreadsheet.
Post #1371567
Posted Friday, October 12, 2012 3:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
robert.gerald.taylor (10/11/2012)
Have you tried creating three seperate Excel data sources?


Excel file is 2007 version and am using ssis 2005. We dont have option to use EXCEL 2007 directly. So using OLEDB source, i created connection.

Regards
Sqlstud
Post #1371987
Posted Friday, October 12, 2012 5:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Step 1: You can read the excel file based on below connection string in your Script Task.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source='"& <Your Source Path> &"';Extended Properties='Excel 12.0;HDR=" & <FirstRowIsHeader = YES> & ";IMEX=1

Step 2: Read all the Excel data's and Create any CSV format for each tab.

Step 3: Using Flat File Source load it to your destination database.
Post #1372037
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse