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.