Load data from multiple tabs in excel to different tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Load data from multiple tabs in excel to...

Load data from multiple tabs in excel to different tables

Rate Topic

Display Mode

Topic Options

Author

Message

sqlstud

Posted Thursday, October 11, 2012 12:01 AM

SSC 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

robert.gerald.taylor

Posted Thursday, October 11, 2012 8:22 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 759, Visits: 802

Have you tried creating three seperate Excel data sources?

Post #1371534

kl25

Posted Thursday, October 11, 2012 9:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 262, Visits: 1,167

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

sqlstud

Posted Friday, October 12, 2012 3:54 AM

SSC 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

sqlusers

Posted Friday, October 12, 2012 5:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 196, Visits: 468

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='"& &lt;Your Source Path&gt; &"';Extended Properties='Excel 12.0;HDR=" & &lt;FirstRowIsHeader = YES&gt; & ";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 »

Permissions