SSIS ForEach - Get current value of collection

Question

I have a Variable User:AllBatches (ObJect) populated from an ADO.NET SQL Query. The SQL query returns a single column of Integers. That part of my package is working.

The next step is a ForEach Loop using the User:AllBatches as the variable enumerator.

In the variable mapping section choose a variable to map to the collection variable index 0. However if I choose any variable type other than Object, the package errors.

If I choose an Object variable then my SQL Task fails with Unsupported Data Type on Parameter binding and I can't then get the value of the Object to use in the SQL Task inside the loop.

If I set it to be an Int32 then I get The Tyoe of the Variable (DBNULL) differs from the current variable type (INT32).

SO if the variable collection mapping will only support an Object Type how do I get the INTEGER value from that output object variable.

Answer 1

You don't have to use a Variable enumerator, you have to use ADO enumerator instead, and select to loop over rows in the first table. Check the following example:

• Looping Through a Result Set with the ForEach Loop

Note that the Execute SQL Task result is stored as an ADO Recordset within the Object variable. On the other hand, variable enumerator is used to loop over an object variable that contains a list or an array generated from a script.

You can read more about enumerator types in the following link:

• Foreach Loop Container