Passing NULL to SSIS variables

Question

I'm building a package to execute a procedure with parameters from a configuration table.

I've built two Execute SQL Task boxes. One populates SSIS variables from the configuration table. The second executes the procedure with those variables.

I'm facing the following behaviour. Integer variables have a 0 as a default value and strings an empty string. Even after getting NULL from the configuration table those variables keep their values.

How can I change this behavior to apply SQL Server logic of NULL as a default "value", or just to enable NULL that they get from configuration table to override their default value?

Answer 1

SSIS doesn't support nullable types for it's built in variables. So - as you've experienced - a null integer in SQL will become zero when assigned as an SSIS variable. Those are the breaks, but there ARE workarounds.

In order to keep our source table NULLs, we will need to skip over the whole "assigning them to SSIS variables" completely. Have a look at the OLE DB Command. It's similar to the Execute SQL command, except it operates on the dataflow level. It operates one SQL Command per row running through it. The advantage of this is that you never have to map a SQL result set to SSIS variables. Working inside a dataflow is SQL out and SQL in. We can avoid the whole "non nullable" headache altogether by passing a SELECT TOP 1 * FROM MyConfigTable into an OLE DB Command. Inside the OLE DB Command compoenent you can assign the ResultSet columns to SP parameters.

I know it's a change, but I do think that moving away from two Execute SQL Tasks in succession and into a Dataflow containing an OLE DB Command component is what you want. Anything else would involve a script task. Good luck!