Post reply

Convert mm/dd/yyyy, m/d/yyyy and mm/d/yyyy Date format To Date/datetime (yyyy/mm/dd) in SSIS

  • February 19, 2015 at 8:43 am

    #301411

    Hi guys,

    I need help: I am loading a FLAT FILE into SQL table, the file have date column with date format that is not consistence (mm/dd/yyyy, m/d/yyyy and mm/d/yyyy). Does anybody know SSIS line code that can convert these format to DATE/DATEIME (YYYY/MM/DD) and assign NULL for other value like "N/A" in the column.

    Thanks

    Chris

  • February 19, 2015 at 8:45 am

    #1778664

    What is the data type of the column coming from the test file?

    James Phillips
    Sr. Consultant
    Pragmatic Works

  • February 19, 2015 at 8:48 am

    #1778666

    The Data type is VARCHAR(50)

    Thanks

  • February 19, 2015 at 9:04 am

    #1778669

    You can use a derived column task and use an expression like this where [date] is the column name

    (DT_WSTR,4)YEAR((DT_DATE)[date]) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH((DT_DATE)[date]),2) + "/" + RIGHT("0" + (DT_WSTR,2)DAY((DT_DATE)[date]),2)

    James Phillips
    Sr. Consultant
    Pragmatic Works

  • February 19, 2015 at 9:18 am

    #1778677

    Why don't you define your column as a date data type in your flat file connection and save yourself some problems? Your destination column should be a date data type as well, using strings to store dates is just a bad idea.

    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

  • February 19, 2015 at 9:27 am

    #1778681

    I am still getting error. the column name from flat file is "[cln enc date]" FYI.

    Please could you include that in the code? if possible also include "NULL" handling code, where any other value like "N/A" will be NULL at the target table.

    Thanks

    Chris

  • February 19, 2015 at 9:39 am

    #1778685

    @luis,

    Your Question: ''Why don't you define your column as a date data type in your flat file connection and save yourself some problems? Your destination column should be a date data type as well, using strings to store dates is just a bad idea."

    You right, but the problem with that solution is that Flat file connection lacks the option to handle other value like "N/A" or "NULL" in the source file. The package will fail that happens. To handle this I have to do it Using Derived column then define the logic. if you know the code line to handle this I will appreciate it.

    Thanks

    Chris

  • February 19, 2015 at 10:18 am

    #1778708

    I'm sorry, I didn't read that last part. I'll blame the lack of coffee.

    Leaving your flat file column as a string, you could use a conditional to generate null values in the derived column with an expression similar to this:

    (FINDSTRING([cln enc date],"N",1) > 0) ? NULL(DT_DBDATE) : (DT_DATE)[cln enc date]

    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

  • February 19, 2015 at 10:32 am

    #1778710

    ilokris (2/19/2015)

    @Luis,

    Your Question: ''Why don't you define your column as a date data type in your flat file connection and save yourself some problems? Your destination column should be a date data type as well, using strings to store dates is just a bad idea."

    You right, but the problem with that solution is that Flat file connection lacks the option to handle other value like "N/A" or "NULL" in the source file. The package will fail that happens. To handle this I have to do it Using Derived column then define the logic. if you know the code line to handle this I will appreciate it.

    Thanks

    Chris

    I would not change the data type for the input. Instead I would try to use a Derived Column to try and cast the data to a date format as a new column. You may have to add logic to deal with missing values.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 19, 2015 at 11:38 am

    #1778734

    I use the EXp and I got this Error msg.

    “[Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[#clc enc date]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.”

    Maybe I did not explain it well... I want "clc enc date" column with Date format ( 02/06/2015, 2/06/2015 and 2/6/2015 ) to be converted to SQL DATE/DATETIME Format (2015/02/06). any other value like "empty" or "NA" should be converted to "NULL".

    Thanks

    Chris

  • February 19, 2015 at 12:14 pm

    #1778743

    Different approach:

    Just cast the column as a date and use the error output to ignore the column when it's not a valid date.

    This was my test file:

    ID,Date

    1, 12/19/2014

    2, 1/5/2015

    3, 11/6/2014

    4, NULL

    5, "N/A"

    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

  • February 21, 2015 at 12:22 am

    #1779099

    To handle the non date values, incorporate the use of a conditional split transformation. You are the best judge on what the non date values are like. You can state anything that is not a number or a "/", or anything like N* could be your qualification. Upon splitting this branch , derive a column to replace the date with with null and then join it back into the other pipeline lower in the dataflow.

    ----------------------------------------------------

Viewing 12 posts - 1 through 11 (of 11 total)

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