Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Import data from multiple Excel sheets to multiple Tables using SSIS Expand / Collapse
Posted Wednesday, July 10, 2013 3:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2016 10:39 AM
Points: 26, Visits: 181

I have a excel file that has multiple sheets and I need to import data from each seperate sheet to a seperate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B. We are doing some data unpivoting also before sending it to table.

Can somebody help me to achieve this?
Post #1472021
Posted Wednesday, July 10, 2013 11:50 AM


Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22, Visits: 147
One way I've done this which may help you is to create a control table in SQL Server.

Something like this:

CREATE TABLE [dbo].[ExcelSource](
[WorkbookName] [varchar](255) NULL,
[FilePath] [varchar](255) NULL,
[SheetName] [varchar](255) NULL,
[CellRangeToQuery] [varchar](25) null,
[Active] [varchar](5) NULL

In SSIS, I created a SQL Task that queries the control table to get the list of all worksheets in a workbook, the cell range for particular data and target information. Loop through this data values and dynamically set SSIS variables to connect to the worksheet, then query that worksheet with its corresponding cell range to import. Then after the transformation use the control tables target value to set where that worksheet data is going.

So essentially you loop through the above data and build a sql statement that you store in an SSIS variable. So in your data flow task in your excel data source task, your data access type is SQL Command from variable.

So in above table it you had a record like

Workbook: Workbook1
SheetName: MyWorkSheet
CellRangeToQuery: $A1:Z25

your query will be built like the following: when you connect to Workbook1, you will pull all the data from the cells in the CellRangeToQuery field.

Select * from [Sheet1$A1:Z25]

You can also get fancier and maintain your list of workbooks/worksheets in excel and have SSIS import that list into the ExcelSource table and then loop through it.

Here are a couple of other articles that outline similar process:

3 part series on using excel files in SSIS
Post #1472272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse