Friday, 29 May 2015

Load data from multiple Excel sheets to Database in SSIS

Introduction:
In this article, I will explain How to Load data from multiple Excel sheets (5 Sheets) in one Excel file into single table by using SSIS.
Let us consider the following MS-Excel spreadsheet with data in 5 different sheets. Each sheet contains 3 columns of data.

Solution:
• First to create a SQL Table which will store excel sheets data.
CREATE TABLE [OLE DB Destination] (
[productname] nvarchar(255),
[sale] float,
[saledate] datetime
)
• Now create a variable @Sheetname of string type and assign the value Jan2010$. Ensure the variable value ends with “$”.

• Create two tasks in control flow as follows.
• Double click the Foreach loop container, go to the Collection tab.
• Select “Foreach ADO.Net Schema Rowset Enumerator” in Enumerator Configuration list.

• From the drop down of Connection, select “New connection...”

• Click on the “New” button of the Configure ADO.Net Connection Manager.
• You get another popup window Connection Manager. In Connection Manager list we have selected “Microsoft Jet 4.0 OLE DB Provider” from “.Net Providers for OleDb” drop down.

• Browse the file which has multiple Excel sheets from which we will load data.

• Go to the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".

• And press Test the connection. You got the message “Test Connection succeeded”.
• In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container.

• Added index value “2” to the variable of @User::SheetName in Foreach loop container.

• In DataFlow Task, add an “Excel Source” and double click on Source connection to Configure the excel connection manager as follows

• Select “Table name or view name variable” in Data Access mode, and assign
the variable value to variable name.

• Added “OLEDB Destination” and assign the SQL table to it.
Finally the Data Flow Task as shown below.


Note: all Excel sheets in the source and SQL Table in destination must have the same structure. 

Let me know what you think about this article. 

2 comments:

  1. nice info ..you can find more here..msbiinfo.com

    ReplyDelete
  2. nice info ..you can find more here..msbiinfo.com

    ReplyDelete