Foreach Loop Archives » SQLErudition.com

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.

Foreach Loop Container is one of the looping constructs available in SSIS packages. One of its enumerator options is the Foreach Item Enumerator. It enumerates through a list of items populated at design time. The list is static at execution time, i.e. you cannot load the list items dynamically from a configuration file or another source like an SQL table. It is probably due to the static nature of the item list, that this enumerator type is not used too often in packages.

On the other hand, there are some scenarios where a static list can prove to be useful. For example –

no need to create a database table just to pass on values for the Foreach Loop
enforce a specific order to the parameters passed to the loop
initialize variables values with the Item Enumerator instead of doing that in a Script task

Another frequent requirement in SSIS packages is to execute a set of operations against different SQL Servers or databases. The package should change the OLE DB connection string dynamically during execution to point towards the appropriate target. A looping construct iterates through the list of servers and databases, and the package Expressions change the OLE DB connection properties.

I am creating this step-by-step scenario to demonstrate the use of a Foreach Item Enumerator and use it to make a dynamic OLEDB connection. I will execute a single query in a loop against three different SQL databases and export the results to a single text file.

The Steps

Add a Foreach Loop Container to your SSIS package. Double-click on it to open its editor. Click on the Collections tab on the left of the editor. Select the enumerator as Foreach Item Enumerator. Click on the Columns… button. Add two columns of String type. As you add columns, the editor gives them names like Column0, Column1 and so on, and there is no way to customize these names.

Populate the column values by simply typing in the rows. Use Column0 for server names and Column1 for corresponding database names on that server. I have used my local server for all three rows but you can use your own server names here. This is the list of items that are iterated by the Foreach Loop.

Now you need two variables that will hold the values shredded from the item list as the Foreach Loop iterates through the rows. These variables are used later in the Property Expressions of the OLEDB connection to make it dynamic.

Click on Variable Mappings tab on the left and then on the <New Variable…>

Add two variables of String type and name them ServerName and DatabaseName.

The Index 0 and 1 of the variables correspond to the Column0 and Column1 of the Foreach Item list respectively.

That is all there is to it. Now the loop is ready to iterate through the item rows.

Let us move on to the dynamic OLEDB connection. Add a Data Flow Task to the Foreach Loop Container.

Double-click the Data Flow Task to view its design surface. Now add one OLE DB Source.

Double-click OLE DB Source to set its properties. The first window is of its connection manager.

Once the connection information is done, view its properties editor. Set the Data Access Mode to SQL Command and put the following query in the SQL Command Text –

select
@@SERVERNAME as ServerName
,DB_NAME() as DatabaseName
,GETDATE() as LogDateTime
,COUNT(*) as NumberOfIndexes
from sys.indexes;

Clicking the Columns tab is important so that the SSIS can find the metadata like column names, data types etc. from the underlying tables/views. If you don’t do this then you’ll find later that SSIS does not know of this metadata.

Again, it is a good idea to drag the connector from OLEDB source to the Flat File destination before modifying the Flat File destination properties. In this way, the SSIS Designer helps you by matching the destination with the source metadata automatically. Otherwise, you have to perform some extra steps to match columns. I hope you like to save time as much as I do!

Give a file path and check the box for Column Names in the First Row.

Verify the column mappings in the Mappings tab.

The trick to make the OLEDB connection string dynamic lies in the next couple of steps where you edit the properties of the OLEDB connection and assign expressions to its ServerName and InitialCatalog properties.

The property ServerName gets the variable @[User::ServerName] and the property InitialCatalog gets the variable @[User::DatabaseName]. Remember that these variables will get their values modified during each iteration of the Foreach Loop and as a result, the OLEDB connection string will get modified too.

As the last step, make sure that data will append to the contents of the destination text file. Otherwise, each iteration will overwrite the contents with the new source connection.

SQLErudition.com

Learning SQL Server

Foreach Loop

How to Concatenate SSIS Variables with a For Loop Container

Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection

The Steps