Sunday, October 22, 2017

SSIS ForEach Loop to load Data from specific Sheets of an Excel Workbook to different SQL Server Tables

SSIS For Each Loop Container to Loop through specific Sheets of an Excel and load data to different SQL Server Destinations
Scenario :
Suppose we have an Excel Workbook(xlsx,2016 version) with multiple Sheets having the Sales data by Product and Service, as follows :

Source:
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1\Source

From this Workbook, we needs to Load the data only from the Tabs "Product" , "Service" ; and other tabs need to Skip.

Destination:
The Data from the above Source tabs, we need to load to below SQL Server Destination tables :
We need to make sure that data from tab "Product$" should go to the table "Tbl_SalesByProd" and data from tab "Service$" should go to the table "Tbl_SalesBySvc"



--------------------------------------------------------------------------------------------------------------
SSIS Package Design :

Now we need to design the package as above based on scenario as follows :

Phase- 1 : Creation of the Variables Needed :
Create a Variable to store the Sheet Names of the Workbook and assign one existing Sheet Name as a Value. This value will change in the run time.

Phase- 2 : Creation of the Connections to Database, Excel Source, and an ADO.Net OLE DB Connection for Excel:

here, db_TAMATAM - The connection to the SQL Server DB.
SalesData.xlsx - The connection Excel Source data.

OleDb_SalesData.xlsx :
This is a ADO.Net OleDb Connection to the Excel Source to read the Tabs/Sheets of Workbook
This connection is very important to loop through each Sheet/Tab of the Workbook by ForEach Loop using the "Foreach ADO.NET Schema Rowset Enumerator"

This ADO.NET connection can be created as follows:
Select New ADO.NET Connection from Connection Mangers block
Next select the ".Net Prodividers for OleDB\Microsoft Office 12.0 Access Database Engine OLE DB Provider"


Next Select the Excel Source File 

Next set the Extended Properties of the OLEDB Connection String as "Excel 12.0 XML;HDR=YES"

HDR=YES means the file has Headers.

Note :
If you are connecting to Microsoft Office Excel Data, add the appropriate Extended Properties of the OLEDB Connection String based on the Excel File type, as follows :


Phase-3 : Add the ForEach Loop Container to Control Flow and Set the Properties:
Enumerator Foreach ADO.NET Schema Rowset Enumerator
Connection : The ADO.NET Connetion(OleDb_SalesData.xlsx)
Schema : Tables


Next do the Variable Mapping "v_SheetName" to Store the Sheet Name dynamically.
Variable :  User:: v_SheetName
Index : 2
Make sure to select Index as "2" to read the Tabs/Sheets of Excel by ForEach Loop.


Phase- 4 : Add the Expression Task to validate the Sheets names which passed by the ForEach Loop:
Expression : 
@[User::v_SheetName]=="Product$"||@[User::v_SheetName]=="Service$"

Add the same Expression to the Precedence Constraint as well.
Expression : 
@[User::v_SheetName]=="Product$"||@[User::v_SheetName]=="Service$"

Evaluation Expression : Expression and Constraint

Phase- 5 : Add the Data Flow Task and define the flow:
Excel Source :
Add the Excel Source and set the properties as follows:
Data Access Mode : Table Name or View Name Variable
Variable Name : User::v_SheetName

This Variable value is Passed by the ForEach Loop to the Data Flow > Excel Source


Next Add the Data Conversion if needed to Convert the Source Data

Conditional Split :
Next Add the Conditional Split to route the Source Data to the different destinations. 

Ouput Name :                Conditon :
Product_Data                 @[User::v_SheetName] == "Product$"
Service_Data                 @[User::v_SheetName] == "Service$"

The data from Sheet "Product$" will go to the table "Tbl_SalesByProd" and the data from Sheet "Service$" will go to the table "Tbl_SalesBySvc".

Final Data Flow :

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog