The SSIS package I have written uses an Execute SQL task to read a small table of survey values into a package object variable. A Foreach ADO Enumerator then loops over a Script plus Execute SQL pair to make some calculations on groups of survey values and then updates the calculated values into additional fields in the original survey table. It is all working great except one thing. If one of the survey values is null, then the null value will not clear the User::myvar package variable in the Variable mappings of the Foreach Loop. Instead, the value from the preceding row with a value persists. Is this something that should be handled with the Expressions in the Foreach Loop?

This is something you should be handling entirely inside a single Data Flow Task. It'll run faster and handle NULLs better. However, the only way to solve the issue you have right now is to add a column to your original SQL query that uses the ISNULL function to return an indicator as to whether or not your "myvar" column is null. Place that result into a "myvar_isnull" variable in your Foreach Loop, and use it appropriately to detect "null" values (that aren't really null) in myvar. Talk to me now on

I found a solution to this. Specifically, I converted all the nulls the string value "0" in the Execute SQL query that populates the recordset. For example, SELECT IsNull(c_crm2,0), instead of SELECT c_crm2. "0" is not a value normally occurring in the survey values and the Script inside the enumerator was written to handle it. I'm still curious how this would be solved at the Foreach enumerator level.