So why do data people tend to avoid (or even actively dislike) loops? (Can you say cursor anyone?). Scaleability! Loops just don’t scale well. A loop that is fast at 100 loops is going to take twice as long at 200 loops, five times as long at 500 loops and one hundred times as long at 10,000 loops. That’s a problem in the database world when at 10,000 rows a table is still considered small and depending on your experience a mid-sized table might be 1,000,000 rows or more. As in all things I like examples, so here’s a simple one.

I’m creating a table with an identity column (the primary key/clustered index) and a date column. I’m going to record times spent updating each row one at a time and just updating the entire table. Then I’m going to add 10 rows and run again, 10 rows and run again, etc until I have 7500 rows. (Not a lot but it takes a while to run.) Quick note to everyone who reads this and thinks “But …”. I’m aware this is a really simple example. If you have buts that you think will significantly change the outcome feel free to run a test yourself and if by some odd chance (unlikely but hey, could happen) feel free to put the results in the comments, or even better blog them and link the blog in the comments :).

-- Create work table & log table
CREATE TABLE LoopSpeed ([RowCount] INT, LoopTimeInMS INT, BatchTimeInMS INT);
CREATE TABLE LoopSpeedTest (Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ADate DATETIME);
GO
DECLARE @RowCount INT = 0;
DECLARE @LoopCount INT = 0;
DECLARE @StartTime DATETIME, @EndTime DATETIME 
DECLARE @LoopTimeInMS INT, @BatchTimeInMS INT

-- 750 loops at 10 row increments.
WHILE @RowCount < 7501
BEGIN
	-- Add 10 rows
	INSERT INTO LoopSpeedTest (ADate) 
			VALUES (GetDate()), (GetDate()), (GetDate()), (GetDate()), 
					(GetDate()), (GetDate()), (GetDate()), (GetDate()), 
					(GetDate()), (GetDate());
	
	SELECT @RowCount = COUNT(1) FROM LoopSpeedTest;
	SET @LoopCount = 0;

	SET @StartTime = GetDate();
	-- Loop that updates each row in the table one at a time.
	WHILE @LoopCount < @RowCount
	BEGIN
		SET @LoopCount = @LoopCount + 1;
		-- Update one row.
		UPDATE LoopSpeedTest SET ADate = GetDate()
		WHERE Id = @LoopCount;
	END
	-- Get duration of loop;
	SET @LoopTimeInMS = DATEDIFF(millisecond,@StartTime,GetDate());

	SET @StartTime = GetDate();
	-- Update all of the rows in the table in a batch.
	UPDATE LoopSpeedTest SET ADate = GetDate();
	-- Get duration of batch;
	SET @BatchTimeInMS = DATEDIFF(millisecond,@StartTime,GetDate());

	INSERT INTO LoopSpeed VALUES (@RowCount, @LoopTimeInMS, @BatchTimeInMS);

END

First let me point out that I’m running this on a very old, cheap laptop and I’m measuring milliseconds. It doesn’t take much to get wild variations in the numbers. That’s why the loop time has such wild variations at times. That said, I’m sure you notice the slow but steady increase in time taken to run the loop. Now imagine if I wanted to run an update over a million rows? We’d no longer be measuring in milliseconds, we’d be measuring in minutes or even hours. Lastly, I want you to notice that pretty flat line along the bottom. That is the very very slow increase in time that the batch update takes. At 10 rows it was approximately 0ms and at 7500 it was 20. Yes, the duration does increase with the number of rows. If nothing else, I/O does take time after all. But SQL is built to handle large numbers (these are really pretty minuscule numbers for SQL to be honest) of rows at a time.

Let me give you an analogy. Let’s say we are baking a cake and I ask you to go to the store to get the ingredients.

• 1/2 cup unsalted butter, softened
• 1 1/2 cups sugar
• 3 large eggs
• 2 1/4 cups all-purpose flour
• 1 teaspoon salt
• 3 1/2 teaspoons baking powder
• 1 1/4 cups whole milk
• 1 teaspoon vanilla
• 2 1/2 cups buttercream frosting

 
I start by asking you to get me the butter. You run to the store, get the butter and come back. Then I ask for some sugar. Then eggs, etc. Assuming you don’t just get frustrated and start yelling at some point it’s going to take a while right?

But if I had you that entire list, you run to the store, get each item and come back. Much faster right? And if I double the length of the list it’s going to take longer to get but not nearly as long as if I ask you to go to the store for each of the additional items one at a time. (Fortunately computers are very patient and don’t actually get angry about being asked to do one thing at a time.)

Conclusion
There is a good reason that database professionals tend to avoid loops. They are slow, inefficient, and scale poorly when compared to batch processing. That said, you still need to know how to use them as occasionally you have no choice. A simple example is changing the read/write status of every database on the instance. Those situations are pretty rare though. Batches really are the way to go if at all possible.

1. Starting from Step 2.
   
2. First let’s do something I should have done from the beginning. Rename all of the objects to something meaningful.
   
3. Create a table in the DataStore instance to hold the list of instances.

   CREATE TABLE InstanceList (
   InstanceName varchar(255) NOT NULL
   CONSTRAINT pk_InstanceList PRIMARY KEY,
   GetDatabaseList bit NOT NULL,
   Active bit NOT NULL
   )

4. Add rows to your table pointing to the servers you are interested in. Make sure that GetDatabaseList and Active are both set to 1.
5. Create a new variable in the package called “ServerList” of type Object
6. Add a new Execute SQL task to pull the list of instances from our new table.
   1. Under the General tab
      1. Set the name to “Get Instance List”
      2. Change the ResultSet to “Full result set”
      3. Change the Connection property to “DataStore”
      4. Put the following in the SQL Statement property:

         SELECT InstanceName
         FROM InstanceList
         WHERE Active = 1
         AND GetDatabaseList = 1

         

   2. On the Result Set
      1. tab Click the Add button
      2. Change the Result Name to 0
      3. Change the Variable Name to User::ServerList.

      

   3. Attach the task to the previous one.
      
7. In the ForEach loop properties under the Collection tab
   1. Change the Enumerator to a “ForEach ADO Enumerator”
   2. Change the variable to “User::ServerList”
   3. Leave Enumeration Mode “Rows in the first table”

   

At this point we now have a package that will loop through a control table containing a list of instances. Then for each instance it pulls a list of databases and stores it into a central location. Hopefully I’ve managed to make these posts clear enough that everyone can follow and easily duplicate what I’ve done. If not then please feel free to comment and I’ll do my best to help you get up and running.

Note: Unfortunately the package file got corrupted somehow and I had to get rid of it. I’ll try to re-create it (and may even re-do this series) and get it reposted one of these days. Of course this was using the SQL 2005 version of SSIS which is no longer really up to date.

1. First create an OLE DB connection manager pointing to some random server.
   
2. Next create a FOR EACH LOOP container. 
   For simplicities sake I’m going to show this loop using a Foreach Item Enumerator.  Personally, I like to use a table and a Foreach from Variable Enumerator but it takes longer to explain. 
   1. Under the Collection “tab” change the Enumerator to “Foreach Item Enumerator”. 
   2. Add a single column.
   3. In that column list out the servers you want to loop through.

   

3. Under the Variable Mappings “tab” add a new variable called “ServerName” with a value type of “String”.  Make sure the Index is set to 0.
   
4. Next go to the properties of the connection manager.  Look for one called Expressions.  Select it and click on the ellipsis (…).
   1. Under properties select ServerName.
   2. Then Click on the ellipsis (…) under Expression.
   3. Select the variable User::ServerName and drag it into the Expression box.

   

At this point when you run your Foreach Loop Container each loop it makes will cause the Connection Manager ConnMan1 to point to a different server.

I realize this was rather fast, and I would have loved to have shown some sample output.  Unfortunately as I stated at the beginning I’m rather pressed for time today and want to get this out quickly to answer a post.

EDIT:

Here are links to the other two posts in this series. Step three has the actual DTSX for download. You will have to rename it to get it to work as it has a DOCX extension on the end of it. I couldn’t figure out how to get a DTSX extension to load.

Looping through multiple servers in SSIS part 2
Looping through multiple servers in SSIS Part 3