How to Concatenate SSIS Variables with a For Loop Container

Variable value concatenation in SSIS is usually done with a Script Task. I am going to demonstrate variable concatenation with a For Loop Container in this post while working on a real world scenario of sending all files from a folder as email attachments.

Drag a ForEach Loop Container in the Control Flow. Then drag a For Loop Container within the ForEach Loop Container. There are no other tasks in these two containers. Finally add a Send Mail Task to the Control Flow and join it to the ForEach Loop Container with a precedence constraint.

The final package looks like this.


ForEach Loop Container is used to iterate through all files in a folder and return their full paths. For Loop Container will concatenate the file paths in a variable. Send Mail Task will use the concatenated file paths as an attachment list in an email.

Declare two variables of string type at the package level. Variable @[User::FELC_Iterate] will hold the single file path returned by the ForEach Loop. Variable @[User::FLC_Concat] will hold the concatenated list of file paths passed from the other variable.


Double click the ForEach Loop Container to open the properties editor. In the Collections tab, give the folder path and select the radio button for fully qualified file name. You can also set the filter for the file types (e.g. *.txt, *.xls etc.). I let it be at its default of all files (i.e. *.*).


In the Variable Mappings tab, set the variable @[User::FELC_Iterate] to index zero so that it can hold the file paths returned by the ForEach Loop Container. Click OK to close the editor.


Now double click the For Loop Container to open its properties editor. Set the InitExpression to –

@[User::FLC_Concat] = @[User::FLC_Concat] + @[User::FELC_Iterate] + "|"

This expression will append the @[User::FELC_Iterate] value coming from the outer ForEach Loop along with a pipe symbol to the @[User::FLC_Concat] in each iteration. The pipe symbol is the separator required by the Send Mail Task if there are multiple files as attachments.

One iteration of the For Loop will suffice our requirement to append the variable value. So set EvalExpression to 1<0, or any improbable boolean condition. The For Loop executes once and then evaluates this condition to see if it can go for another execution. The improbable condition will exit the loop on second iteration because the condition will evaluate to FALSE. Click OK to close the editor.


Double click the Send Mail Task to open its editor. Setup the SMTP connection and other fields in the Mail tab. In the Expressions tab, add an expression for FileAttachments to use the @[User::FLC_Concat] variable. Click OK to close the editor.


Execute the package and the file paths are concatenated as shown on the Locals window. These files would be emailed as attachments.


SSIS Variable Naming Convention Based on Origin of Value

There are many schools of thought about naming conventions, not just about SSIS variables, but about naming anything that can be given a name! One point that everyone agrees on is – use a convention that suits your environment, and stick to it.

Related Blog Post – SSIS component naming convention

One issue that I face while looking at packages with many variables is that there is no straightforward way to get a summary or a quick glance of all variables that have expressions defined for them. The BIDS Helper add-in for Visual Studio assists in this regard by adding a triangular icon overlay over the variables with an expression. SQL Server 2012 is also said to have a similar feature but I haven’t tried that yet. On the other hand, if I need to see a summary of variables that have values coming from a config file, I have to use the Configuration Editor to edit the config file and reach its last summary screen.

I’ve started using a prefix-based convention to name my SSIS variables recently based on the origin of the variable value. I distinguish between the variables as having –

  1. a value set from a config file at execution time.
  2. a value calculated in an expression at execution time. The expression could be based on other user variables, system variables, functions or literals.
  3. a static value that will not change from its design time value.
  4. a value assigned in a Script Task, a For Each Loop Container, Row Count etc.

If I know at design time that the value of the variable will be passed from a configuration file, I give it a prefix of “config”, e.g. configEmailDistributionList. If the value would be set via an expression then the variable gets a prefix “expr”, e.g. exprDirectoryForArchive. Other variables do not have any prefix, simply a name that is descriptive enough.


  • At the time of creating a config file, the prefix makes it clear that all variables with a “config” prefix must be included in the configuration. So the config file now has only such variables along with other task or connection manager properties.
  • On opening up a package, I can easily figure out where should I be looking for the value of the variable, the config file or the Expression Editor.
  • Alphabetical ordering in the Variables window groups similar variables together.
  • Helps in development because I don’t have to constantly check back variable properties if it has an expression or not.
  • Helps in standardizing the names and expressions for some of the most frequently used variables.
  • Helps in debugging.


The argument against this convention would be the same as given for any prefix-based convention (e.g. Hungarian Notation) that if the origin of the variable value changes then the prefix would also need to change. Which in turn, may need a change at all those places where the variable is used. As an example, if a variable was getting its value from a config file but would now have a static value, then the “config” prefix would have to go away and all the expressions, properties etc. using that variable would have to be updated. Right now I’m counting on doing a Find/Replace in the XML code of the package.

I’ll try to see if using variable Namespaces would be a better idea than prefixes and share the experience in a blog post later.

References –

(not specifically for Hungarian Notation, but in context of any prefix-based convention)

Hungarian notation (Wikipedia)

Hungarian Notation (MSDN)

Hungarian Notation – The Good, The Bad, and The Ugly