Click here to Skip to main content
Click here to Skip to main content

Using the Foreach ADO Enumerator in SSIS

, 4 Jun 2006
Rate this:
Please Sign up or sign in to vote.
SSIS has a built in function for loops. In this blog article, I want to demonstrate how to use SSIS's Foreach ADO enumerator to loop through a list of SQL server names and collect some basic data (e.g. name, language, version, etc.) about them.

Introduction

The goal of this example is to collect some basic data like server name, service name, language, etc. about a list of SQL servers. The server list is stored in a SQL server table ("Server"); the collected data should be stored in table "ServerSetup":

Tables

We start by creating a SSIS package in Visual Studio 2006. The list of servers that should be examined is read from our SQL Server database by using an Execute SQL Task. We need to write the resultset of a SQL query into a variable. The loop will later iterate through all rows of all tables in this variable. It is important to specify Full result set; if you do not set this option, you will not be able to store the resultset into a variable.

Execute SQL Task

After you have entered the SQL statement and you have set the ResultSet option, you have to switch to the Result Set pane and specify the name of the variable in which you want to store the result set. The variable has to be of type Object:

Execute SQL Task

The next step is to create our Foreach ADO Loop. As you can see in the picture below, you have to specify the variable into which we wrote the result set of our Execute SQL Task

Loop Configuration

In addition to that, we have to specify a second variable (of type String!) into which the loop will write the content of each row of the result set. The variable's content will change from iteration to iteration:

Loop Configuration

This picture shows a summary about the variables you have to define for running this sample:

Variables Overview

Inside the loop, we use a data flow task to get the basic data from our various SQL Servers. By now, our control flow should look like this:

SSIS Control Flow

The next step is to create our source connection. Firstly we create a static connection that points to any of the SQL Servers which we want to collect data from:

Source Connection

Secondly, we use the connection's properties to specify an expression through which the connection string of the connection will be changed to the connection string in the variable that is changed by the Foreach ADO loop:

Connection Expression

Last but not least, we define the destination connection and map the fields:

Destination Connection

At the end, the data flow of our package should look like this:

SSIS Data Flow

Try it! You will see that the Foreach ADO Loop changes the content of the variable ConnectionString automatically and you receive the basis data about all your SQL Servers in the target table.

History

  • 4th June, 2006: Initial post

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

r.stropek
software architects
Austria Austria
Hi, my name is Rainer Stropek. I am living a small city named Traun in Austria. Since 1993 I have worked as a developer and IT consultant focusing on building database oriented solutions. After being a freelancer for more than six years I founded a small IT consulting company together with some partners in 1999. In 2007 my friend Karin and I decided that we wanted to build a business based on COTS (component off-the-shelf) software. As a result we founded "software architects" and developed the time tracking software "time cockpit" (http://www.timecockpit.com). If you want to know more about our companies check out my blogs at http://www.software-architects.com and http://www.timecockpit.com or take a look at my profile in XING (http://www.openbc.com/hp/Rainer_Stropek2/).
 
I graduated the Higher Technical School for MIS at Leonding (A) in 1993. After that I started to study MIS at the Johannes Kepler University Linz (A). Unfortunately I had to stop my study because at that time it was incompatible with my work. In 2005 I finally finished my BSc (Hons) in Computing at the University of Derby (UK). Currently I focus on IT consulting, development, training and giving speeches in the area of .NET and WPF, SQL Server and Data Warehousing.

Comments and Discussions

 
QuestionThis saved me so much time Pinpremiumdefwebserver14-Jul-14 14:11 
QuestionHow to call 2 fields from sql task PinmemberANKIT GARG10-Apr-14 6:34 
SuggestionHeader or subject as Variable source Name PinmemberANKIT GARG8-Apr-14 0:19 
QuestionGood tutorial, but can't use it. Can you give step by step instructions ? Pinmemberetl_monkey11-May-13 6:44 
QuestionError on setting up Source connection PinmemberJohn Waclawski29-Nov-12 8:19 
QuestionMessage Automatically Removed PinmemberMember 375892218-Sep-12 4:48 
QuestionWhat connection string is needed for SSIS to SQL Server 2000? PinmemberGiuseppeMe12-Mar-10 5:14 
GeneralIt was my connection strings Pinmemberlianaent26-Jun-09 10:50 
GeneralThis package won't even compile Pinmemberlianaent26-Jun-09 9:57 
GeneralForeach File Enumerator using Excel files Pinmemberblackpower2k718-Jun-09 8:49 
Generalfaster extraction and loading on SSIS. Pinmemberblackpower2k725-Apr-09 19:54 
GeneralRe: Using the Foreach ADO enumerator in SSIS PinmemberMember 422791120-Mar-09 3:25 
QuestionWhy is my version of package failing with "User::ConnectionString" cannot be applied PinmemberAmber Robertson22-Sep-08 2:16 
Hi, I have tried this SSIS project but I think I have got the User:ConnectionString variable value incorrect. I typed in the following:
Provider=SQLOLEDB;Data Source=RemoteServerName;User ID=Login; Password=Password
 
I am getting the following errors in Execution Results:
1) Error: ForEach Variable Mapping number 1 to variable "User::ConnectionString" cannot be applied.
2) Error: The type of the value being assigned to variable "User::ConnectionString" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
 
Would you mind telling me where this variable is picking up the connection string from? I suspect I am using the incorrect connection string for my environment.
 
Any suggestions much appreciated.
 
Kind Regards
Amber
AnswerRe: Why is my version of package failing with "User::ConnectionString" cannot be applied PinmemberAmber Robertson23-Sep-08 5:25 
GeneralError to the ole db source connection after adding the @connectionString to the 'Source' expression PinmemberMember 6813509-Apr-08 5:05 
GeneralProblem with Foreach ADO enumerator in SSIS [modified] PinmemberDavid Branscome12-Feb-08 13:13 
GeneralGreat article PinmemberTiNgZ aBrAhAm30-Nov-06 10:18 
GeneralInformative PinmemberT-C3-Aug-06 11:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140718.1 | Last Updated 4 Jun 2006
Article Copyright 2006 by r.stropek
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid