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. 

5 comments:

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

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

    ReplyDelete
  3. Thank you for sharing wonderful information with us to get some idea about that content.
    Msbi Developer Course
    Best Msbi Online Training

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. The guidance on loading data from multiple Excel sheets is outstanding. The step-by-step approach simplifies an otherwise daunting task. How To Download Discovery App On LG Smart TV It's evident that considerable effort went into creating this well-organized tutorial.

    ReplyDelete