Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS–Dynamically set File Mask : FileSpec

Sep 29 2010 4:10AM by Sudeep Raj   

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.


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.


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.

Tags: SSIS, For EAch loop, MSBI, #BI, #SQLSERVER, BRH, SQL Server 2008, Recordset Destination, FileSpec, Dynamic File Mask, #TSQL,

Sudeep Raj
12 · 13% · 4306



  • Very helpful article.

    One question though, how can one specify the a file mask like pur[0-9] or pur_?

    commented on Mar 11 2011 2:42AM
    Ramesh Saive
    119 · 1% · 434
  • Very helpful article.

    One question though, how can one specify a file mask like pur[0-9] or pur_?

    commented on Mar 11 2011 2:42AM
    Ramesh Saive
    119 · 1% · 434
  • @Ramesh, for pur_ it is simple just use pur_* and it will work. For something like pur[0-9] you will not be able to do that directly from the solution I provide. What you could do is set the filter to pur* in the inner foreach loop and then use a script task(before other tasks) with regular expression to check the complete file name. If the file name suites your requirement set flag which can be checked in the procedence constraint joining the next task.

    commented on Mar 11 2011 5:01AM
    Sudeep Raj
    12 · 13% · 4306
  • is it possible to loop through both different files at same time (two flat file sources) with one foreach loop container? for example, square files with square patterns and circle files with circle patterns (square1.txt, square2.txt, square3.txt, circle1.txt, circle2.txt, circle3.txt)

    commented on Apr 10 2013 12:37PM
    2916 · 0% · 3
  • Yes it is possible if the number of Square files is same as the number of circle files and the number after each square file is same in the circle file. For example if you have 2 square files with names Square1.txt, square2.txt, you should have 2 circle files with names: Circle1.txt, circle2.txt). Loop through the folder with a file mask set to "Square*". This will make the for each loop to iterate for each file with name like Square. Now in the for each loop save the full file path to a variable "SqPath".

    Now in the data flow task use the above value (SqPath) saved for Square files in the Square flat file source.

    For the Circle flat file source, set up another connection and the connection string you can set dynamically using the expression: REPLACE(@[User::SqPath],"SQUARE",Cirlce) and your solution should work.

    commented on Apr 12 2013 8:44AM
    Sudeep Raj
    12 · 13% · 4306

Your Comment

Sign Up or Login to post a comment.

"SSIS–Dynamically set File Mask : FileSpec" rated 5 out of 5 by 1 readers
SSIS–Dynamically set File Mask : FileSpec , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]