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

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:54 AM
Points: 24, Visits: 128
Hi,

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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
) ON [PRIMARY]


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:

http://www.sqlservercentral.com/Forums/Topic637794-148-1.aspx

3 part series on using excel files in SSIS
http://dougbert.com/blog/post/excel-in-integration-services-part-1-of-3-connections-and-components.aspx
Post #1472272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse