Saturday, 12 August 2017

SSIS For Each Loop Container to Load Multiple Flat Files Data to SQL Server Destination

How to load multiple Source Text Files Data to SQL Server Destination using SSIS Package
ForEach Loop Container is used repeat the tasks in a Pacakge. For example load data from each file in a directory to the destination table.
Scenario :
Suppose if we want load the source data from multiple Text .txt files to SQL Server destination and also need to update the Source File name in one column of the destination table, this can be done using the SSIS Foreach Loop container as follows :

Source Files (.txt) :
The Source files which we want to load to the Destination

 Destination Table :
The Destination Table ( Emp_Temp1) into which the above source files need to load.

Step 1: Creating Variables :
First create the two variables "FileName", "FilePath" and assign the one of the Source File to the variable "FileName" and assign the Source Files folder path to the variable "FilePath" as shown below :

Step 2: Create the Connections for Source and Destination:

Tamatam_DB : It is the Connection the SQL Server Database
Input_Src_Txt : It is the Connection for the Source Text files. 

Step 3: Creating dynamic Connection String for Source:
For Input_Src_Txt connection we have to make the Connection String dynamic to pick each source file as follows :
Go to connection properties and define the "Connection String" in Expressions as follows :

Step 4: Design the Control Flow:
Excute SQL Task : 
In this task we Truncate the destination SQL Table by defining the SQL  Statement as "Truncate Table Emp_Temp1"
For Each Loop Container : 
Add the For Each Loop Container in the Control Flow and set the Properties as follows:
In Collection tab, choose the Folder and Files ( you can use wild cards based on your files naming ) value as below.

Next in the Variable Mappings tab assign the dynamic file name variable "FileName" to store the filename in each loop.

Step 5: Design the Data Flow :
Next design the Data Flow to send the data from Source to Destination as follows :
In the above Data Flow, we have to use the Data Conversion to convert the data from Source format to Destination.

Also we have used the Derived Column transformation to Define a Column to Capture the Source File name, using the Variables as follows :

Now map the Columns to the respective Columns in Destination.

Step 6 : Final Output :
Now all the Text Files data with Source Files name loaded to the destination as follows :

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts