SSIS: FileName into Variables and Foreach Loop Container

Very often while exchanging data files with the clients, a useful information is stored in the file name. This can be a client name prefix, date, time, the period in the file and so on.
While loading the data into the database, we need to extract the portions of the filename, store them into variables and use these variables during the data load.

Let’s say the client uploads their files in a folder D:\Temp.
The file name is delimited by underscore (_) and is predefined as:

  1. VariableA
  2. (delimiter)
  3. VariableB
  4. (delimiter)
  5. VariableC

The client uploads the files:

  • VariableA_VariableB_VariableC.csv
  • VariableAA_VariableBB_VariableCC.csv
  • VariableAAA_VariableBBB_VariableCCC.csv
  • VariableAAAA_VariableBBBB_VariableCCCC.csv
  • VariableAAAAAA_VariableBBBBB_VariableCCCCC.csv

Create SSIS project (I use Visual Studio 2008 in this example).

First rename the package.


In Control Flow add Foreach Loop Container. As this example is relatively simple I will use abbreviations for the objects. Foreach Loop Container will be FLC.

Select FLC and add 2 variables in its scope:

  • FolderPath (String) – to store the path of the client’s folder and change it only once when needed
  • FileName (String) – to store the filename while looping the FLC.


Double click FLC and:

  1. Add FolderPath variable to Directory expression
  2. Point to .csv files only
  3. Read the file name only


Map the FileName variable:


We can insert the values of the variables, stored in the filename into SQL log table.

Create database.

Create the log table.

Add the variables that will store the extracted values from the file name.

Click the FLC to add the variables in its scope.

  1. VariableA (String)
  2. VariableB (String)
  3. VariableC (String)


Add Execute SQL Task (EST) inside the FLC:


Double click EST and:

  1. Set the SQL connection
  2. Write the SQL statement to insert the data


4. Map the variables, collected from the file name.

We need to assign the values from the different parts of the file name into variables and manipulate them in the loop.

Let’s look at the first filename –> VariableA_VariableB_VariableC


We need to cut the string from position 1 to 9 and assign its value into VariableA variable.

We add an expression.


and we use SUBSTRING() and FINDSTRING() built-in functions.

Putting the arguments into

ends to

and the result is VariableA

, but… we need a flexible CharacterCount, based on the position of the first underscore (_).

To find it, we use

and we can invoke it in our example like

or “what is the position of the first occurrence of underscore (_)?”.

The result is 10.

As we need 9 as CharactersCount, we add “-1” to FINDSTRING():

Putting both functions together

ends to VariableA.

Test the 5th file VariableAAAAAA_VariableBBBBB_VariableCCCCC

ends to VariableAAAAAA.

Replace the hard-coded string with the first variable and test

Ups! An error occurs


Here is the tricky part – add ABS() to convert the negative value to positive

Map the variables in EST.


Before the test, we need to create the test files in D:\Temp


Run the package by clicking F5 in SSIS.


Check the Log table.


Expression for VariableB:

Expression for VariableC:


Delete database.

Keep it simple :-)