SSIS–Dynamically set File Mask : FileSpec

sudeep — Tue, 28 Sep 2010 19:10:00 GMT

In the SSIS Forum I came across the following query and thought of writing a post on the same.

“Can anyone give guidance on how to set the mask for a Foreach File Enumerator programmatically? I have a DB that has a list of masks. I am retrieving that into an ADO recordset. For each record I retrieve the value into a variable. I would like to look at a directory and copy all files that begin with the chars for each item in my recordset variable.”

The solution I provide uses a Flat File to store the various file mask in stead of a Table as required in the above case.

b*.txt
a*.txt
d*
*z.xlsx

The above scenario could be broken into 3 parts:

Read the flat file and save the values to a object type variable.
Loop through each row of the object variable(which contains the file masks).
For each of the mask traverse the folder and copy/move(or any other operation you need) the files that match the current file mask.

Description:

The input folder has the following files:

For the first part check my other blog post on “Loop through each record in a text file”.

Next you need to set up the 1st foreach loop which will iterate though each row in the object variable and save the file mask to the user variable "strFileMask".

Put another Foreach loop inside the above foreach loop. containing a file system task to copy the file source file to destination location.

While setting up this foreach loop set the enumerator to Foreach File Enumerator. In the expressions set FileSpec to the expression @[User::strFileMask] this will change the file mask as per the mask set by the previous foreach loop. In variable mapping map the variable set up another user variable strFileName to store the file path of the filtered file.

Above is the basic setup that is required to to achieve our goal.

Now in the 2nd for each loop we may put any task that we need to use this settings. It could be a data flow task File System task or email task or any other task. I will be using a file system task to copy the file filtered based on the file mask set here and send those files to destination folder.

Set up the file system task as shown below:

The connection used above for the destination has the expression set for the connection string as:

"I:\\SSIS\\BLOGS\\Dynamic File Mask\\Output\\"+REVERSE(SUBSTRING(REVERSE(@[User::strFileName]),1, FINDSTRING(REVERSE(@[User::strFileName]),"\\",1)-1))

The package would look like this:

On package execution the destination folder would have the following files:

The package(SSIS 2008) is available for download here.

Let me know if you need any specific clarification.

Loop through each record in a text file : Recordset Destination

sudeep — Wed, 08 Sep 2010 19:43:00 GMT

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.

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:

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]

DFT1 settings:

In the Flat file source we use a connection to read the text file as a comma separated file.

Recordset Destination:

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.

Search results matching tag 'Recordset Destination'

SSIS–Dynamically set File Mask : FileSpec

Loop through each record in a text file : Recordset Destination

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.