SSIS and NULL values

Question

I have package in SSIS. It has a execute sql task that gets some config values from a table on different server. So the SSIS package gets those config values and stores them in the variables in SSIS. These variables are used in further sql tasks as parameters.

The issue that I am encountering is, Some of those config values are null. So whenever SSIS encounters a null value for a integer type, it converts that to 0 and passes it as 0 to further task rather than passing it as null

If the data type of the column is date and its value is null in the config table, SSIS assigns a default value 12/30/1899 12:00:00 AM and passes the default value as the parameter rather than the null value.

How do I make the SSIS package pass the actual null value rather than the value converted by SSIS?

Answer 1

I would change the Data Type of all the variables to String, and change the Execute SQL Task command to return string values e.g. using CAST ... AS NVARCHAR ( 4000 ) )

String variables are usually easier to work with.

Answer 2

How about using Strings as was mentioned and then in the SQL task try 'select isnull([field], 'null')'

This will change all null values to the string 'null'.

It is kind of a hacky way of doing it but it just might work.

Answer 3

There are various options. One of them, you will need to create an expression, which will convert your date to null and not to minimal date value ("1899-12-30")

Expression example of such conversion:

(DT_DATE)( REPORT_DATE ==(DT_DATE)"1899-12-30" ? NULL(DT_DATE) : REPORT_DATE )