Welcome To TechBrothersIT: SSIS - Read Multiple Sheets from Excel File and Load into Table

Scenario:

We have our source Excel file that has sale history. Each sheet will has data for a year. We can get data for any number or years. In this example we have data for three years. We need to load this data to our destination table.

Source Excel File looks like this

Destination Table definition:

CREATE TABLE dbo.SaleHisTory

(

ID INT IDENTITY(1, 1),

SalePersonName VARCHAR(100),

SaleCountry VARCHAR(50),

SaleAmt INT,

SheetName VARCHAR(100)

)

Solution :

We will be using Foreach Loop Container to perform this requirement. Let's start with step by step approach

Step 1:

Create connection to our Excel source file as shown below

Choose Microsoft Office 12.0 Access Database Engine OLE DB Provided as shown below

Press Ok as shown above in number 3. Provide the File Path as shown below

Click on All and Provide values as shown below

We are all done with configuring our Excel File Connection.

Step 2:

Bring For each loop container to the Control Flow Pane and configure as shown below

Enumerator : Foreach ADO.NET Schema Rowset Enumerator

Connection String : The OLE DB Connection String that we have created in Step 1

Schema : Tables As we are interested to get the names of sheets

Go to Variable Mapping and map the variable to Table_Name as shown below

Set Index from 0 to 2 as Table_Name is on third number in List

Step 3:

Bring Data Flow Task to Control Flow pane inside Foreach Loop Container. Open Data Flow by double Clicking and then bring ADO.NET Source as shown below and configure. One thing to notice here, we do not have option to provide variable in which we have saved our SheetName , For now Choose any sheet you like as all Sheets in Excel are with same metadata.

Drag Derived Column Transformation to Data Flow pane and Connect ADO NET Source to it. Perform data conversation according to destination columns and also generate new column DER_SheetName by using SheetName variable.

After that bring OLE DB Destination and connect Derived Column Transformation to it and map the columns.

Step 4:

Till here our package is ready but only Table Name(SheetName) is hard coded in our ADO NET Source. We want to change it to SheetName Variable so with each iteration it should change so we can load all the Excel sheets.

To do that , Click on Data Flow Task and go to properties and then Expression and configure as shown.

Set the data flow property Delay Validation=True so it should not validation as sheet names will be provided while execution not at start of package validation.