Click here to Skip to main content
15,860,844 members
Articles / Database Development / SQL Server

Using the Foreach ADO Enumerator in SSIS

Rate me:
Please Sign up or sign in to vote.
4.69/5 (12 votes)
4 Jun 20062 min read 333.7K   27   20
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


Written By
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

 
QuestionHow to load sqlserver tables using foreach loop in ssis Pin
sri2 201317-Dec-14 8:19
sri2 201317-Dec-14 8:19 
QuestionThis saved me so much time Pin
defwebserver14-Jul-14 14:11
defwebserver14-Jul-14 14:11 
QuestionHow to call 2 fields from sql task Pin
ANKIT GARG10-Apr-14 6:34
ANKIT GARG10-Apr-14 6:34 
SuggestionHeader or subject as Variable source Name Pin
ANKIT GARG8-Apr-14 0:19
ANKIT GARG8-Apr-14 0:19 
QuestionGood tutorial, but can't use it. Can you give step by step instructions ? Pin
etl_monkey11-May-13 6:44
etl_monkey11-May-13 6:44 
QuestionError on setting up Source connection Pin
John Waclawski29-Nov-12 8:19
John Waclawski29-Nov-12 8:19 
QuestionWhat connection string is needed for SSIS to SQL Server 2000? Pin
GiuseppeMe12-Mar-10 5:14
GiuseppeMe12-Mar-10 5:14 
GeneralIt was my connection strings Pin
lianaent26-Jun-09 10:50
lianaent26-Jun-09 10:50 
GeneralThis package won't even compile Pin
lianaent26-Jun-09 9:57
lianaent26-Jun-09 9:57 
GeneralForeach File Enumerator using Excel files Pin
blackpower2k718-Jun-09 8:49
blackpower2k718-Jun-09 8:49 
Generalfaster extraction and loading on SSIS. Pin
blackpower2k725-Apr-09 19:54
blackpower2k725-Apr-09 19:54 
GeneralRe: Using the Foreach ADO enumerator in SSIS Pin
Member 422791120-Mar-09 3:25
Member 422791120-Mar-09 3:25 
QuestionWhy is my version of package failing with "User::ConnectionString" cannot be applied Pin
Amber Robertson22-Sep-08 2:16
Amber Robertson22-Sep-08 2:16 
AnswerRe: Why is my version of package failing with "User::ConnectionString" cannot be applied Pin
Amber Robertson23-Sep-08 5:25
Amber Robertson23-Sep-08 5:25 
It was failing because the ConnectionString field was left blank in the Server SQL table and the ConnectionString SSIS variable was getting this value in the Foreach ADO enumerator from the Server table.
GeneralError to the ole db source connection after adding the @connectionString to the 'Source' expression Pin
Member 6813509-Apr-08 5:05
Member 6813509-Apr-08 5:05 
GeneralProblem with Foreach ADO enumerator in SSIS [modified] Pin
David Branscome12-Feb-08 13:13
David Branscome12-Feb-08 13:13 
GeneralGreat article Pin
Tingu Abraham30-Nov-06 10:18
Tingu Abraham30-Nov-06 10:18 
GeneralInformative Pin
T-C3-Aug-06 11:54
T-C3-Aug-06 11:54 

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

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