SSIS Package - Derived Column Conditional Expression for Date Fields with Zero

Question

I am relatively new to SSIS and its data types. I have successfully created a Data Flow task that imports data from a comma-delimited .txt flat file to SQL Server. An error occurs when running the task, at the point where a date field in the .txt file has 0.

image description

For a Derived Column expression to convert the date fields with 0 to Null, I have come up with the following so far...

[Latest Bill Due Date]==0 ? NULL(DT_DATE) : (DT_DATE)[Latest Bill Due Date]

...but the logic isn't accepted and the error message appears:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Thanks in advance for any direction.

It would appear that "Latest Bill Due Date" is unicode (DT_WSTR). Add quotes around the 0. i.e. [Latest Bill Due Date]=="0" ? NULL(DT_DATE) : (DT_DATE)[Latest Bill Due Date] — Mark Wojciechowicz
Thank you. Writing this question up made me also remember my Java programming class, where data types on either side of a comparison must be the same type. The source field is being recognized as a string. The final code works (including the SUBSTRING needed to process the YYYYMMDD format in the source data): LTRIM([Latest Bill Due Date]) == "0" ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([Latest Bill Due Date],1,4) + "-" + SUBSTRING([Latest Bill Due Date],5,2) + "-" + SUBSTRING([Latest Bill Due Date],7,2)) — Michigan Lou

Mokz Mokz · Accepted Answer · 2017-02-10T20:41:45

I had a similar problem, when in a textfile there was a 00000000 value, had to convert it to null in a datetime column. What ended up working for me was stablishing the table with null value as default in the column and also adding a Script Component as Transformation. Add as a column output something like 'VerifyNullDateVar' and inside the script do something like

    if (Row.DATEVAR == 0)
    {
         //do whatever you want to do if the input value is an actual date

         Row.VerifyNullDateVar = 2;
    }
    else
         Row.VerifyNullDateVar = 1;

DATEVAR is the input column you get from the textfile. After that, use a derived column to read the value from VerifyNullDateVar

VerifyNullDating == 1
VerifyNullDating == 2

Finally, you need to set up 2 OLEDB Destination, one when you can save a date value in the Table; and the other one when you ont save anything in it, that way it gets the default null value

SSIS Package - Derived Column Conditional Expression for Date Fields with Zero

1 Answers