Sep 29, 2010

SSIS–Dynamically set File Mask : FileSpec - Sudeep's Domain

SSIS–Dynamically set File Mask : FileSpec - Sudeep's Domain

In the SSIS Forum I cams 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.

1.b*.txt
2.a*.txt
3.d*
4.*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.