2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
iainc
  • 862
  • 6
  • 20

1 Answers1

0

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:

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Grrr! Thanks. That makes perfect tortured SSIS sense. Create an Object variable as an array and then DON'T use the variable, use the ADO iterator with the variable. Thanks again. Handy link too - about the only web page I didn't find! – iainc Sep 30 '19 at 20:46
  • @iainc Since the execute sql task store the result as an ADO Recordset within the Object variable. – Hadi Sep 30 '19 at 20:51
  • 1
    @iainc variable enumerator is used when the Object variable contains a list or an array created from a Script. I updated my answer and added this info – Hadi Sep 30 '19 at 20:52