SSIS Foreach ADO Enumerator: Mapping Columns to Variables by Index

This post was originally published as a complaint that the Foreach ADO Enumerator on the Foreach Container did not map bit columns to Boolean variables properly. I was getting this error:

The type of the value being assigned to variable "User::XXXXXXX" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Turns out the problem was not data type mismatching (though there are documented problems with BigInt mapping as a string) but rather that the indexes I was using were off. Silly me, I assumed that the first column in my SELECT list would be index 0. Nope. It's index 1. (Not sure what 0 is being used for yet.) I discovered this by reading the results in a script task, using DataReader.GetOrdinal to see where my columns were:

Dim msg As String
msg += "Column1: " + reader.GetOrdinal("Column1").ToString() + vbNewLine
msg += "Column2: " + reader.GetOrdinal("Column2").ToString() + vbNewLine
System.Windows.Forms.MessageBox.Show(msg)

Once I knew the right indexes, I was able to update the Foreach task variable mappings. (It sure would be nice if we didn't have to mess with ordinal indexes anyway--what's wrong with column names?)

(I'll soon post a new blog entry that shows how to get to the DataSet result from the Execute SQL task in a downstream script task.)

Check your index mapping. Update: Doh! Turns out I had a column in my SELECT list at index 0 after all. Lesson learned: triple check the index mapping. This index ordinal stuff is a bit of a pain since the SQL task is disconnected from the Foreach that's using the results. If the query is in an stored proc, you're even more disconnected. If someone else changes the order of the select list in the stored proc (or, , if you're using SELECT *, then you're screwed.

Update: Something else interesting I learned: if your underlying column is nullable, you may need to map to an Object variable to allow for the null. I had a nullable date column mapped to a DateTime SSIS variable and received the above error. I changed the variable to Object and it works. I used a script tasl to check the type of the variable when the value is null and it is System.DBNull. So it looks like you can do something like this to check what you have in the variable:

If TypeOf Dts.Variables("MyNullableDate") Is System.DBNull Then
  'Value in underlying column is null.
Else
  'Treat as DateTime
End If

Dan