Variables and expressions help make any SSIS package dynamic and flexible. It is always a best practice to use variables in a package, especially when the information is used more than once in the package. In any package you will most likely have Connections, Tasks, Containers, Data Flows and Event Handlers. Variables can be used in any of these items and can be passed between them.

Let’s look at some examples of when variables should be used. Let say we have a package that is supposed to load a lot of flat files into a table. We have received these flat files from vendors or sales people in the field. The files all have the same file layout. This is necessary to load them all into the same table with a “For Each Loop”. 

On the package we are going to need a connection in the connection manager to the one of the flat files. But this file name is going to need to change for each iteration of the loop. We will use expressions to do this.

1.    Right click in the connection manager and select New Flat File Connection.
2.    Name it a name that will be meaningful for all the files we will loop through.
3.    Select a file name of one of the file files.(Note: Change the file type to all files if needed)
4.    Make adjustments to the text qualifier and any other properties needed.
5.    Click on the preview node on the left to ensure the data is in the correct format.
6.    Click ok

After the connection is created we will now create a data flow to load the data into a database. We will do this in a data flow and place this data flow into a For Each Loop.

1.    Drag a data flow task into the control flow.
2.    Double click on the data flow to open it.
3.    Drag in a Flat File Source into the data flow.
4.    Set the connection in the Flat File Source to the Flat File connection.
5.    Drag in an OLEDB destination and double click on it.
6.    Click on the New button next to OLEDB Connection Manager
7.    Create a connection to the database with the table to load the data into.
8.    Select the table to load in the drop down menu below, or click new and create the table.
9.    Click on mappings and ensure the rows are mapped correctly.
10.    Click Ok and return to the Control Flow

Now we are going to drag in a For Each Loop and connect create a string variable called strFileName.  This will hold the file name that is found from the For Each Loop.

1.    Open the variables window (View > Other Windows > Variables).
2.    Create a String Variable named strFileName, leave the value empty.
3.    Close the Variable window.
4.    Drag in a For Each Loop to the control flow.
5.    Drag the Data Flow into the For Each Loop.
6.    Double click on the For Each Loop.
7.    Click on the connection node on the left.
8.    Set the enumerator to For Each File Enumerator.
9.    Set the Folder location to the location where the flat files exist.
10.    Set the File properties to the file type(Example: *.DAT, *.CSV).
11.    Leave the retrieve file name to Fully Qualified name.
12.    Click on the variable mappings node.
13.    Select the strFileName variable and leave the index at 0.
14.    Click ok
Now we have the For Each Loop complete and the data flow finished, but there is still one very important thing left. We need to set the connection string property of the flat file connection in the connection manager to the strFileName variable. The For Each Loop sets the value of this variable each time it looks at a file. The data flow source is using this connection so a different file will be loaded for each iteration of the loop.
 

1.    Single click on the flat file connection in the connection manager.
2.    Click on the Expressions property in the properties window.
3.    Click on the ellipse next to Expressions.
4.    In the Property expressions editor select the connection string property.
5.    Click the first ellipse in the expression column.
6.    Drag the strFileName variable into the expression window
7.    Click ok in both windows
    

Your package is complete. Now you can run this package and it will load all of the files in the folder you selected into the selected table. There are still several places we can use variables in this package, the server name, initial catalog (database), Directory of flat files, and file type. Setting these to variables will allows us to change the package and use these in other For Each Loops we create in the package. This also allows us to make changes to these properties from outside the package using configuration files or tables.