Foreach Loop in SSIS

Foreach loop Container in SSIS is used for repeating Control flow for each member of the specified enumerator.
Here in this simple example we will demonstrate that how it works.
This simple example shows that how to set ETL process of extracting data from Flat File to OLE DB destination repeatedly for each file in the particular folder.
To begin with we drop onto the Control flow a Foreach loop Container and then a Data Flow Task within the same Foreach loop Container. Structure may look like this.

Now double click or right click and select Edit in Foreach loop Container, a Foreach loop Editor Window appears. In Collection Tab you may choose the Enumerator Types. In our case we are going to Iterate the process on the basis of text file from our source folder.
Select the Collection Tab, In Enumerator drop down box the default selection is “Foreach File Enumerator” we would leave this as it is and in Folder text box point the Source Folder. After that In File Text Box simply type “*.txt” this is telling Foreach Loop container to enumerate the process for every text file available in your Source folder. This only we need to run Foreach loop container successfully

Now on every iteration I want to pick up the name with complete path location of current text file in to my variable, so can use it in Flat File Source tool in Data flow Task and load it to OLE DB Destination each time. For this go to Variable mapping tab and map your variable with index 0, you can use your predeclared variable here or simply create new variable by selecting drop down in variable drop down box.

After completing all steps in Foreach loop, select the data flow task within the Foreach loop container and come to Data Flow tab. here drop onto the designer a Flat File Source and a OLE DB Destination. Create connection managers for both of them and connect Flat file with OLE DB Destination.

Now here the all tricks come in to scenario. we will make the Flat file connection manager dynamic by passing the value of Foreach loop variable into it. 
Logic is very simple that at every iteration of Foreach Loop, variable's value will be updated with new File Location for which Foreach loop is iterating. on very iteration of Foreach loop Data flow task is executing and in Data flow task Flat file source taking value from that variable for pointing to txt file. So on every execution the flat file Source is addressing new Text file and loading it to OLE DB Destination.
right click and go to property of Flat file connection, Click here Expression property box, the property express editor open. In property drop down box choose Connection String. 

 Now click button  in expression box , again new window Expression Builder open, select here your variable declared in Foreach loop container. And click OK.

  Execute the package and at the end you will find that all data of every txt file available into the Source folder is filled into OLE DB Destination table.

let me know for any further query regarding this SSIS package , happy ETL :)

No comments:

Post a Comment