SSIS: NULL value in variable with double datatype

Question

Hi,

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,

Olivier

Answer 1

0

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.

SELECT 
CAST(NULL AS <datatype>) AS ColumnName,
CAST(NULL AS <datatype>) AS ColumnName,
CAST(NULL AS <datatype>) AS ColumnName
...

answered Nov 03 '11 at 11:17 AM

the_SQL
92 ● 1 ● 1 ● 3

Unfortunately changing the SP is not an option. It is not managed by our team.

Olivier

Nov 04 '11 at 12:26 AM Olivier Lammens

(comments are locked)

Answer 2

0

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 '11 at 12:17 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

SSIS: NULL value in variable with double datatype

Follow this question

Related Questions