Table driven foreach loops(zz)

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
In an earlier blog I mentioned that we'd have a fix for the ForEachADOEnumerator. Before the fix there were some pretty involved steps required to build an ADO.Net recordset and enumerate over it in the foreachloop.
This is no longer the case.
Now with a few clicks and a query or two, you can drive whole packages inside a foreach loop with rows from any table. This is a powerful integration between the workflow of IS and your existing data wherever it may exist.

I've built a deceptively simple package you can run that illustrates this. The package has an Execute SQL Task and a ForEach Loop in it. The SQL Task queries the local server's AdventureWorks database to get some names and titles into a record set. It looks like this:

The Execute SQL Task script looks like this:

USE ADVENTUREWORKS
GO

SELECT Con.FirstName, Con.LastName, Emp.Title
FROM HumanResources.Employee Emp, Person.Contact Con
WHERE Con.ContactID = Emp.ContactID
AND Con.FirstName LIKE 'D____'

The resulting data from this query looks something like this:

No big deal so far, just a SQL Task executing a query. The trick is how to get the rows of the result set into the workflow. How to enumerate over those and do meaningful work in the package that is driven by the data in the result. For this simple example, the “meaningful work” is to execute a script task that shows a message box with the data. Applications for this integration however are limitless. Any time you need to execute a section of work flow per row of a result, this method provides a way.

Here are some examples:

• Sending out email notifications to clients when it's time to renew subscriptions.
• Executing sub-packages that have been queued up through a management console that stores resulting queue requests in tables.
• Processing employee edge events as described in human resources databases. Events such as Birthdays, children turning 18, option expirations, etc.
• CRM stale dates. Reminding sales people to contact customers who haven't been contacted within N weeks.
• Processing sales logs to find potential fraud, bad checks or stolen visas.

The trick is to get the value of the columns of each row into a form that it can be used inside the package. For Integration Services, the way to do that is to get the columns values into variables. To do this, I need to take the recordset returned from the SQL Task and somehow iterate over it. The ForEachADOEnumerator was created just for that purpose. Here's what the settings look like in the ForEach Loop editor:

The query only selects results that constitute one table. So I'm only interested in the first table. Also, note that the ADO object source variable is is the User::ADORecords variable. That means that the ForEachADOEnumerator will look for the recordset in that variable. Go back to the SQL Task and you'll find it specifies the ADORecords variable as the destination for the result set.

So here's how it works. The SQL Task executes a query. It builds a recordset with the results from the query and places the recordset object into the ADORecords variable. Then the ForEachLoop executes and initializes the ForEachADOEnumerator. The ForEachADOEnumerator has been configured to retrieve the ADORecords variable and enumerate over the recordset stored there. Next, we need to get the values of the columns in the recordset into variables. I do that on the variable mappings page of the ForEach Loop.

This tells the ForEachLoop to map the columns from the recordset to the variables as described. In this case, the first column, column at index 0, should be “mapped” to the variable named Column1 and so forth. To see this in action I've set a breakpoint to fire at the beginning of every loop iteration. Like this:

Then, when running the package and I hit the breakpoint, it looks something like this:

I've also set up my watch to show me what the variable values look like before the first execution of the loop. The watch with the three mapped variables look like this:

See the correlation between the first row of the results from the query above and the first values to be set for the variables? Each iteration of the ForEachLoop will now correspond to one row in the result and the ForEach Loop will iterate 14 times for each row in the resulting recordset.

The code in the script task to show the variable values is trivial, but I'll add it here for completeness. As I've noted in other blog posts, you'll need to remember to place the name of the variables in either the “ReadOnlyVariables“ or “ReadWriteVariables“ property to make this code work.

Dim Message As String
Message = Dts.Variables("Column1").Value + " " + Dts.Variables("Column2").Value + ", " + Dts.Variables("Column3").Value
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success

Now when I execute the package, I get a messagebox like this:

Driving loops with tables this way is extremely powerful. Now, thanks to the recent modifications to the ForEachADOEnumerator, it's also very easy to do.

I've posted the package here so you don't have to recreate it.

Hope this helps you understand enumerators better, especially the class of enumerators we call matrix, multi-valued or enumerator enumerators. That is, enumerators that produce more than one value per iteration. The ForEachADOEnumerator provides an enormous opportunity to build work flow that is driven by existing data in its existing form, in a database.

If you have any comments or input on this, I'd love to hear it. I know some of you have been asking for a simpler way to do this. I hope this satisfies. :)

Universe.Earth.Software.Microsoft.SQLServer.SSIS.KirkHaselden