Sep 9, 2010

Loop through each record in a text file : Recordset Destination

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.

File Layout:

InputFilePath,OutputFilePath
\Input\Input1.txt,Output\Output1.txt
\Input\Input2.txt,Output\Output2.txt
\Input\Input3.txt,Output\Output3.txt

The overall package:

Package_thumb

The first Data Flow Task(DFT) uses a Flat File Source to read the data and send it to an object type user variable using Recordset Destination.

Continue……