Post reply

Date change before insert to database

  • November 8, 2012 at 9:47 am

    #151116

    I have a file with the date 20121025171203. I have created a database table with the field date in

    SQL Server. I have also created a SSIS Package with data flow: Flat File to OLE DB Destination.

    I have set the error outputs to Ignore failures. When the package is run the Source and destination turn green but then my execution results

    show that 0 rows have been wrote.

    I understand that the date above may be preventing this from being written to SQL server. Is there any transformation i can

    use to change the date format before it is written to SQL server?

    Regards

    John

  • November 8, 2012 at 10:34 am

    #1557282

    If you change the format from 20121025171203 to a string in this format 20121025 17:12:03 that should load into a SQL Server datetime column OK.

  • November 9, 2012 at 2:54 am

    #1557512

    Hi,

    The issue is that this file is extracted from another system and the date is in this format. I would like to change the date format to the format you suggested or something different before it hits the SQL server table (date field).

  • November 12, 2012 at 4:49 am

    #1558100

    Hi

    Anyone have any thoughts on how this can be achieved?

    Thanks

  • November 12, 2012 at 5:01 am

    #1558104

    You can use a derived column transformation to change the format of the date field.

    Use the SUBSTRING function and convert the result the the appropriate datatype.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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