CODEWITHSUNDEEP
sql-servervariablesssisnulletl
user3297992
user3297992

Reputation: 11

SSIS and NULL values

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?

Upvotes: 1

Views: 6057

Answers (3)

sam sergiy klok
sam sergiy klok

Reputation: 628

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 )

Upvotes: 0

DrHouseofSQL
DrHouseofSQL

Reputation: 548

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.

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15027

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.

Upvotes: 2

Related Questions