SSIS - How to convert DateTime from ISO format

  • sabarishbabu

    Old Hand

    Points: 349

    Hello,

    In my source file having DateCreated column as ISO format string like '2020-08-05T05:50:49.844Z'. I want to convert from ISO date string to DateTime2 in SSIS. Please advice best way to achieve this.

    Thanks,

    Sabarish

  • Thom A

    SSC Guru

    Points: 98720

    What's wrong with the all the existing conversion functionalities already within SSIS and SQL Server? Why didn't what you've already tried work? Where are you trying to perform this conversion, in a variable, a data flow task, within SQL, in a script task, elsewhere?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • sabarishbabu

    Old Hand

    Points: 349

    Thanks Thom!!

    I am trying to convert in the DFT task, however I could see the below default data conversion, I am not sure how to convert from ISO format.

    DT_DBDATE

    yyyy-mm-dd

    DT_FILETIME

    yyyy-mm-dd hh:mm:ss:fff

    DT_DBTIME

    hh:mm:ss

    DT_DBTIME2

    hh:mm:ss[.fffffff]

    DT_DBTIMESTAMP

    yyyy-mm-dd hh:mm:ss[.fff]

    DT_DBTIMESTAMP2

    yyyy-mm-dd hh:mm:ss[.fffffff]

    DT_DBTIMESTAMPOFFSET

    yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]

    Note : I can convert in SQL query however I am not sure how to achive in SSIS DFT task.

    SELECT CONVERT(DATETIME2, CONVERT(DATETIMEOFFSET,'2020-08-05T05:50:49.844Z'))

    Thanks,

    Sabarish.

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    Since you do not include an offset - you can convert directly to DATETIME2.  For these types of columns - I would just define them as a string in SSIS and pass it through to the table as that string.  The column would be defined as a datetime2 and SQL Server will convert it on insert.

    If the values in the file cannot be converted - the package will fail and you can then identify the bad rows and address the issue with the sender.

    Another option is to pass the data as a string into a staging table - then attempt to perform the conversion in SQL from the staging table to the final table.  For those that fail you can then identify the bad rows and fix them in the staging table as needed.  However, I prefer going back to the sender to make sure they fix the problems so I don't have to fix the bad data every time they send me a new file.

    If the data actually contains an offset value - then in the SSIS connection manager for the file define the field as a string and use a data conversion task.  In the data conversion task - replace the 'T and 'Z' with a ' ' and define it as DT_DBTIMESTAMPOFFSET.  You can then map that new field to a datetime2 column in the destination table and SQL Server will convert it to the appropriate value.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply