SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import data from multiple Excel sheets to multiple Tables using SSIS


Import data from multiple Excel sheets to multiple Tables using SSIS

Author
Message
anand.mind
anand.mind
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 181
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?
keebler96
keebler96
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search