Post reply

SSIS Flat File Mapping - DATETIME Conversion Error

  • May 12, 2017 at 6:14 pm

    #337835

    Hi
    I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00).  I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection.  For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL.  Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data?  The destination table data type for this field is DATETIME.

    Thanks

  • May 15, 2017 at 9:03 am

    #1942580

    ShuaibV - Friday, May 12, 2017 6:14 PM

    Hi
    I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00).  I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection.  For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL.  Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data?  The destination table data type for this field is DATETIME.

    Thanks

    You could use a Derived Column to send a NULL or something else instead of that value using a conditional expression.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 15, 2017 at 10:17 am

    #1942593

    ShuaibV - Friday, May 12, 2017 6:14 PM

    Hi
    I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00).  I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection.  For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL.  Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data?  The destination table data type for this field is DATETIME.

    Thanks

    That is a valid DATETIMEOFFSET value in SQL.  Try using DT_DBTIMESTAMPOFFSET instead.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • May 15, 2017 at 10:58 am

    #1942596

    i have set source file column data type as DT_WSTR (50) and destination table (SQL) data type as DATETIMEOFFSET.  i have tried using Derived column to convert to datetimeoffset and getting the error.
    i have the value in source file as "2017-04-04T21:46:17.931+00:00", the value could be empty for some rows in the input file.  i want to store the same value in SQL server.  
    Any input is greatly appreciated...

  • May 16, 2017 at 8:18 am

    #1942701

    I find this piece of documentation extremely helpful when dealing with SSIS data types.
    https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types

    Check out the sections on converting.  I think you need to define it as DT_DBTIMESTAMPOFFSET.

  • May 17, 2017 at 6:42 pm

    #1943030

    Thanks Everyone.  I have set the Datatype to VARCHAR(37) at destination and able to resolve this issue.

Viewing 6 posts - 1 through 5 (of 5 total)

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