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.

RenameThePackage1

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.

Variables1

Double click FLC and:

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

FLC1

Map the FileName variable:

FLC2

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)

Variables2

Add Execute SQL Task (EST) inside the FLC:

EST1

Double click EST and:

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

EST2

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

AssignVariables1

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

We add an expression.

AssignVariables2

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

AssignVariables3

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

Map the variables in EST.

EST3

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

CreateTestFiles1

Run the package by clicking F5 in SSIS.

RunPackage1

Check the Log table.

CheckLogTable1

Expression for VariableB:

Expression for VariableC:

CheckLogTable2

Delete database.

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *