Post reply

Import data from multiple Excel sheets to multiple Tables using SSIS

  • July 10, 2013 at 3:33 am

    #275072

    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?

  • July 10, 2013 at 11:50 am

    #1631331

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply