Search code examples
csvdatetimessisetlflat-file

SSIS Convert Blank Datetime column to NULL and Parse Input

I have the following simple SSIS Package to process a comma delimited file:

enter image description here

I'm having problems with date columns - specifically MoveInDate which the input column has data in the format '20090731000000'.

It goes into a database column MoveInDt which has a Datetime data type and data ends up in the format: 

'2009-07-31 00:00:00.000'

In SSIS I have specified the input column to be DT_DBTIMESTAMP. I'm unable to figure out how to do a simple 'if MoveInDate is blank, load null, else change data to 'YYYY-MM-DD 00:00:00.000'

Right now I can't even seem to figure out how to load a hardcoded date

This fails

Derived Column

As does 

"2019-01-01"

Or

(DT_DBTIMESTAMP)"2019-01-01"
(DT_DBTIMESTAMP)(DT_DATE)"2019-01-01"

Error: 0xC02020A1 at Data Flow Task, Flat File Source [11]: Data conversion failed. The data conversion for column "Move In Date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Flat File Source [11]:

Looking up that message I've found posts about the data types not matching on the input to output column, but looking at it ...

Checking the flat file input column, it is defined as DT_DBTIMESTAMP

enter image description here

And the destination column is also defined as DT_DBTIMESTAMP when looking at input output properties.

enter image description here

I've looked at many questions here and elsewhere and have tried reducing the input to one simple row in the input file. I've tried recreating the package several times.

Solution

  • If the data is stored in the following format in the flat file

    20090731000000

    Then leave the source column data type as DT_STR and add a dervied column with the following expression:

    (ISNULL([MoveInDate]) || [MoveInDate] == "") ? NULL(DT_DBTIMESTAMP) : 
    (DT_DBTIMESTAMP)(LEFT([MoveInDate],4) + "-" + 
                     SUBSTRING([MoveInDate],5,2) + "-" + 
                     SUBSTRING([MoveInDate],7,2) + " " + 
                     SUBSTRING([MoveInDate],9,2) + ":" + 
                     SUBSTRING([MoveInDate],11,2) + ":" + 
                     SUBSTRING([MoveInDate],13,2) )

    If data is stored in the following format:

    2009-07-31 00:00:00

    Use the following expression:

    (ISNULL([MoveInDate]) || [MoveInDate] == "") 
         ? NULL(DT_DBTIMESTAMP) 
         : (DT_DBTIMESTAMP)[MoveInDate]

    If data is stored in the following format:

    2009-07-31

    Use the following expression:

    (ISNULL([MoveInDate]) || [MoveInDate] == "") 
         ? NULL(DT_DBTIMESTAMP) 
         : (DT_DBTIMESTAMP)([MoveInDate] + " 00:00:00")