Set Date Format on Flat File Source

  • Is there a way to set the format of date fields in a flat file source in SSIS?

    I've got many *.csv files that I'm needing to import and the date fields come in as YYYYMMDD. These are formats are unfortunately actually set in legal agreements and cannot be changed at the source.

    I know that we could define the date fields as character and then change the datatype in a Data Conversion transformation, but there are hundreds of fields in these files and it would be much easier if I could just set a date format in the source files.

    Thanks in advance for any help.

  • If you set a column as a DATETIME datatype, and the data put into that column is in the YYYYMMDD format, no conversion is necessary... it'll just work because of implicit conversons from the character based ISO format.

    Of course, the proof is in the code...

    CREATE TABLE dbo.SomeDateTable

    (

    RowNum INT IDENTITY(1,1),

    SomeDateColumn DATETIME

    )

    INSERT INTO dbo.SomeDateTable (SomeDateColumn)

    SELECT '20081118' UNION ALL

    SELECT '20000101' UNION ALL

    SELECT '19630704'

    SELECT * FROM SomeDateTable

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • That what I would have expected also. However, the date fields are loading with an error (-1071607676 The data value cannot be converted for reasons other than sign mismatch or data overflow. ).

  • After lots of searching, I fanally ran across the term "fast parse" in an article and found the following MSDN article.

    http://msdn.microsoft.com/en-us/library/ms139833(SQL.90).aspx

    This seems to be just the trick I need in order to load date fields formatted as YYMMDD. I've tried it on one of the fields, and it loads without an issue.

  • Ted Zatopek (11/19/2008)


    After lots of searching, I fanally ran across the term "fast parse" in an article and found the following MSDN article.

    http://msdn.microsoft.com/en-us/library/ms139833(SQL.90).aspx

    This seems to be just the trick I need in order to load date fields formatted as YYMMDD. I've tried it on one of the fields, and it loads without an issue.

    Thank you for the link... makes me like ol' school BULK INSERT and BCP even more. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Glad to help. 🙂

  • LOL @ Jeff:w00t:

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

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