Click here to Skip to main content
15,860,943 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

 
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 

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.