I am working on a SSIS 2008 package. I have an Execute SQL Task that runs a Sproc, which brings Result(Int), RefrenceRange(String), and Units(String) in the result data set. The result set goes in an Object type variable. Then I have a foreach loop that assigns Results, RefrenceRange and Units from each row to respective SSIS variables for further processing. I declared Results to be Int, RefrenceRange as String and Units as String. Some of the rows in the result data set have a NULL value in the RefrenceRange and Units column. Therefore, when I execute my package I get the following errors: The type of the value being assigned to variable "User::RefrenceRange" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object The type of the value being assigned to variable "User::Units" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. How can I handle Null values in SSIS variables. Do I have to declare them as Object types and then convert while processing or is there a better way? Thank You.

SSIS does not allow NULLs. Why did you need the Object type of the variable? The solution is in rewriting your Stored Procedure to handle the NULLs. E.g. using the IsNULL() T-SQL function to strip it out and replace with something you CAN handle in SSIS.Arthur My Blog

The best way is, take care, that the resultset return no NULL values. You can work with isnull() or coalesce() in the select statement.

The best way is, take care, that the resultset return no NULL values. You can work with isnull() or coalesce() in the select statement. I cannot change the sproc because it is being used by some other processes as well, they might be handling nulls in different way. Is there another way I can handle this problem? Thanks.

SSIS does not allow NULLs. Why did you need the Object type of the variable? The solution is in rewriting your Stored Procedure to handle the NULLs. E.g. using the IsNULL() T-SQL function to strip it out and replace with something you CAN handle in SSIS. Arthur My Blog The Object Type variable is storing the result set which is then getting processed in the foreach loop. I cannot change the sproc because it is being used by some other processes as well, they might be handling nulls in different way. Is there another way I can handle this problem? Thanks.

Then you may use the split component to redirect such a raw and handle it somehow (I am not aware of what you need to do in case you encounter the NULL somewhere). I will point you actually to a blog post by Todd McDermid who discusses this topic in depth and offers several solutions: Dealing with NULLs in SSIS at http://toddmcdermid.blogspot.com/2008/12/dealing-with-nulls-in-integration.htmlArthur My Blog

Then you may use the split component to redirect such a raw and handle it somehow (I am not aware of what you need to do in case you encounter the NULL somewhere). I will point you actually to a blog post by Todd McDermid who discusses this topic in depth and offers several solutions: Dealing with NULLs in SSIS at http://toddmcdermid.blogspot.com/2008/12/dealing-with-nulls-in-integration.html Arthur My Blog Conditional split looks like a good idea bcoz it can catch Null, but I could not understand how to change the value to an empty string when the value in the column is NULL. Can you help with that? Thank you.

Then you may use the split component to redirect such a raw and handle it somehow (I am not aware of what you need to do in case you encounter the NULL somewhere). I will point you actually to a blog post by Todd McDermid who discusses this topic in depth and offers several solutions: Dealing with NULLs in SSIS at http://toddmcdermid.blogspot.com/2008/12/dealing-with-nulls-in-integration.html Arthur My Blog Actually, I think Derived Column can also be useful in this case, as I want to change the Nulls to " ". I tried using it with the following expression: ISNULL(ReferenceRange) ? "-" : ReferenceRange my Derived column is Replace 'RefrenceRange' Now I get the following error: Foreach Loop Container: Variable "User::ComponentResultProfessional" does not contain a valid data object I understand this is bcoz ComponentResultProfessional does not get result set from the oledb source. Earlier my execute sql task was putting the result set in this object type variable and I was trying to process each row from this result set using foreach loop. How do I use the foreach loop with the data flow task that contains ole db source running a sql that brings multiple rows in the result set and I need to process each row. Thank You.

Couldn't you replace your "Execute SQL Task + Foreach Loop" with a Data Flow Task? Put your sproc call into an OLE DB Source. Get rid of all the variables entirely. Talk to me now on

Couldn't you replace your "Execute SQL Task + Foreach Loop" with a Data Flow Task? Put your sproc call into an OLE DB Source. Get rid of all the variables entirely. Talk to me now on So here's what I had to do: Removed the "executeSQL task + For each loop" as it could not handle Nulls in columns. Added data flow task->OLE DB Source to run the sproc->Derived Column to handle nulls->Recordset Destination to catch the whole result set in an Object type variable -> For each loop to process each row in the result set. Got this idea from this blog: http://toddmcdermid.blogspot.com/2008/12/dealing-with-nulls-in-integration.html That's a really good blog. If anybody has a better idea to optimize this process, please let me know. This process is working for now. Thank You all for giving me ideas.

I'm still wondering why you're stuffing the data into an Object variable. Why not just use the Data Flow to process the data? Talk to me now on