Seems SSIS is having issues with NULL values passed to variables. I'm loading results from a stored procedure into a variable of type object. I then need to insert the data from the object in a table. This is done with a foreach loop. But when the sql task hits the NULL value it fails. The datatype for the NULL value is double. I've tried to catch the NULL value through the EvaluateAsExpression (ISNULL(@[User::intUptime]) ? (DT_R8) 9999 : (DT_R8) @[User::intUptime]) but with no luck. Is this a known issue in SSIS? Version used SQL Server 2008 R2
Thanks in advance,
asked Nov 03, 2011 at 07:21 AM in Default
For tables, SSIS takes the underlying data type. For stored procs, SSIS must read the first SELECT statement to retrieve metadata for what it expects the stored proc to return. In your stored proc, use a no-op select statement to declare the meta-data for the given columns, i.e.
answered Nov 03, 2011 at 11:17 AM
You cannot use variable of type [System.Object] is not convertible to the SSIS data types.
In case you have a NULL value in it and want to handle it, you will have to use the Script Task to handle the NULL.
answered Nov 06, 2011 at 12:17 PM