CODEWITHSUNDEEP
sqlssisexpression
user1709091
user1709091

Reputation:

How to assign a default value to NULL dates using SSIS derived column transformation?

In my SQL 2008 SSIS environment, I have data coming from a CSV file and I need to fix up null dates.

Data from CSV looks like this:

01011990
01011990
01011990
01011990
01011990

In my staging table, I have a column defined as:

[SHIPPED DATE] date NOT NULL

First I fix the date values by pushing the column through a derived transformation that has:

(DT_DBDATE)(SUBSTRING([SHIPPED DATE],1,2) + "/" + SUBSTRING([SHIPPED DATE],3,2) + "/" + SUBSTRING([SHIPPED DATE],5,4))

The above transformations makes my string dates coming form CSV import into a date column in my SQL db.

The output of this is:

1990-01-01
1990-01-01
1990-01-01
1990-01-01
1990-01-01

Next, I'd like to deal with NULL dates so that I can stay true to the "NOT NULL" definition in my db. What I want to do is assign the NULL dates a default value but I get an error message as per below:

Error at CSV to SQL [Derived Column [24817]]: The data types "DT_WSTR" and "DT_DBDATE" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

My CSV column is WSTR, my destination in the SQL DB is a "date" column.

My transformation I am trying to use is: ISNULL([SHIPPED_DATE]) ? "1900-01-01" : [SHIPPED_DATE]

It does not want to work due to the above message. Can anyone point me to the right direction?

Upvotes: 4

Views: 35012

Answers (1)

user756519
user756519

Reputation:

Change your expression as shown below. You have two parts to the ternary operator.

Part 2 you have it as [SHIPPED_DATE], which is of type DT_DBDATE because you are already casting this value in the derived column transformation.

However, Part 1 is simply assigned as text "1900-01-01". You need to add (DT_DBDATE) before that string value to type cast it to date format so that both the values specified on the ternary operator are of same data type.

From

 ISNULL([SHIPPED_DATE]) ? "1900-01-01" : [SHIPPED_DATE]

To

 ISNULL([SHIPPED_DATE]) ? (DT_DBDATE)"1900-01-01" : [SHIPPED_DATE]

Upvotes: 11

Related Questions