Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection

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.

ItemEnumeratorDemo1

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.

ItemEnumeratorDemo2

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…>

ItemEnumeratorDemo3

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

ItemEnumeratorDemo4

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

ItemEnumeratorDemo5

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.

ItemEnumeratorDemo6

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

ItemEnumeratorDemo7

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

ItemEnumeratorDemo8

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;

ItemEnumeratorDemo9

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.

ItemEnumeratorDemo10

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!

ItemEnumeratorDemo11

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

ItemEnumeratorDemo12

Verify the column mappings in the Mappings tab.

ItemEnumeratorDemo13

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.

ItemEnumeratorDemo14

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.

ItemEnumeratorDemo15

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.

ItemEnumeratorDemo16

10 thoughts on “Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection

    • Aalam Rangi February 24, 2015 / 7:05 pm

      I’m glad you like it Pundrick! I’d love to see you blog too! 🙂

    • Achint April 17, 2017 / 9:11 pm

      Hi Pundrick,

      I have some sql scripts in 1 folder and I want these scripts should run at some point in time and the result of each script has to be loaded to multiple excel sheets.Is it possible to do it through for each loop?If yes can you please explain how we can accomplish that??

      Thanks,
      Achint

      • Aalam Rangi April 17, 2017 / 9:47 pm

        Hi Achint,
        Are the number of output columns and their datatype in all scripts similar? What’s the difference in the scripts? Do you really need them in a folder or is it acceptable to put them in a stored proc or SSIS OLEDB source query?

  1. Sachin Khot April 27, 2015 / 6:15 am

    Its very nice article. Keep it up

  2. Aaron June 24, 2015 / 11:12 am

    I’ve tried this solution and I can’t seem to make it work. Everytime I try to run the package I get the following error:

    TITLE: Package Validation Error
    ——————————

    Package Validation Error

    ——————————
    ADDITIONAL INFORMATION:

    Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “local.tempdb” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error at Data Flow Task [SSIS.Pipeline]: OLE DB Source failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Error at Package [Connection manager “local.tempdb”]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Login timeout expired”.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Named Pipes Provider: Could not open a connection to SQL Server [2]. “.

    (Microsoft.DataTransformationServices.VsIntegration)

    • Aalam Rangi July 30, 2015 / 1:50 am

      Hi Aaron, I’m sorry that I’m so late in responding to you and hope your issue is resolved!

      Is SQL Server installed on your local machine? As the error message states, the TempDB of your local machine is not accessible. If you don’t have SQL Server installed on your local machine then try pointing the OLEDB connection in the SSIS to a known SQL Server instance.

      If you have an installation then check if the SQL Server service running. Start the SQL Server Management Studio and try to log on to your local instance. Also, on the Windows machine, go to START > RUN and type “services.msc” without the quotes. This will bring up a window that shows all the services and their status. Find the MSSQLSERVER service and start it if is in stopped status.

      Lastly, verify that the login name in the SSIS OLEDB connection actually exists on the SQL Server and can log on to the server.

  3. Sarat July 29, 2015 / 2:47 am

    Enjoyed it !
    Hoping that some more scenarios can do with item enumerator.
    Thank you

  4. Deon February 8, 2016 / 10:17 am

    Great article by the way. I would like to know how to do this getting the server names from a view or table.

  5. Arulraj July 10, 2018 / 12:21 pm

    The first connection is follow to all iteration. That means DB not change in run time. same db connected for all iteration. 🙁

Leave a Reply

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