In most ETL we use Foreach loop to iterate through each file in a folder or records fetched from a table. Now the question arises how do we use Foreach loop based on each record in a text file.
Scenario: We have a text file having 2 columns, one containing source file relative path and 2nd column having the destination file relative path. The objective is to fetch each file from the source folder that are there in the our text file and move them to destination based on the connections available in the text file on the same record.
Caution: Do NOT use this method to read each record in the file if it caontain data and try to emulate a cursor. Use a data flow task instead.
The overall package:
The first Data Flow Task(DFT) uses a Flat File Source to read the data and send it to an object type user variable. The Foreach loop consumes this object variable and iterates through each record in the object variable. In the foreach loop the variable are mapped to 2 string user variables one to store the source file relative path and the other to store the Destination file relative path. At the end of the package execution all the files mentioned in the 1st column of the text file needs to be moved to the destination based on the 2nd column value.
Variables used in the package:
obj: Object type variable to store the records from the text file as an object.
strBaseFolder: The base folder where all the other files and folder reside.(not necessary to have this inn case the source and destination locations are in different place)
strInputFile: Store the relative file path of the source file. Details will be discussed when I talk about the Foreach Loop.
strOutputFile: Store the relative file path of the destination file. Details will be discussed when I talk about the Foreach Loop.
Connections used in the package:
All the connection used are Flat File Connections
Connections: It provides the connection to connect to the flat file having all the connections. The connection string is set by the expression @[User::strBaseFolder] + “\\Connections.txt”
Input: It provides the connection to the input file which needs to be moved to the output file. The connection string is set by the expression @[User::strBaseFolder] + "\\" + @[User::strInputFile]
Output: It provides the connection for the destination file. The connection string is set by the expression @[User::strBaseFolder] + "\\" + @[User::strOutputFile]
In the Flat file source we use a connection to read the text file as a comma separated file.
Component Properties tab
Note: The variable obj is a user variable of object type.
In the Input Properties tab only check the columns you wish to sent to the object variable.
No need to make any changes in the 3rd tab “Input and output properties”. We now have the obj variable which can be used in the foreach loop to iterate through each record. The remaining logic is same as applied to any other foreach loop.
Now we come to the Foreach loop and set it up. Set the enumerator to “"Foreach ADO Enumerator”. Select the ADO obj Source variable from the drop down box and set it to “User::obj” variable. The Enumerator mode is set to default “Rows in first table”.
We now need to read the value present in each row and assign it to string variables to be consumed later. Add the user variables by selecting them from the drop down list the user variables. Set the index to 0 for the first column data and index to 2 for the 2nd column variable.
In the DFT 2 we have a flat file source and a flat file destination. I have used no transforms in this scenario which you could include as per your requirement.
The Flat file source uses the connection “Input” while the destination uses the connection “Output”mentioned earlier.
The package is ready for execution.
I have uploaded the package Recordset for you reference.