Issue with date format check in SSIS

  • Hi i have csv file with policystartdate before loading data i need to check if its in YYYY-MM-DD format or not. i wrote expression as

    "LEFT((DT_STR,19,1252)PolicyStartDate,8) == (DT_STR,4,1252)DATEPART("yyyy",PolicyStartDate) + RIGHT("0" + (DT_STR,4,1252)DATEPART("mm",PolicyStartDate),2) + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",PolicyStartDate),2) ? PolicyStartDate : (DT_DATE)0" it always return date "1899-12-30 00:00:00.000". How can i check the format and load it in sql server in same format.

    Thanks in advance

  • Slightly puzzled here - you are using date functions on PolicyStartDate in your expression. Are you importing the column as a DT_DATE already?

    Also are you planning on writing the date to SQL server as a text value or date value? If a date then the format you load to SQL server is irrelevant - it's a date type.

  • yes i want to write as date in sql server. but before that i need to check format yyyy-mm-dd other format then filter that data

  • Probably the easiest way to do what you are trying is to just pass the string to a derived column transform and try to cast it e.g. (DT_DATE)PolicyStartDate. If it is a valid date format the cast will work otherwise it will error. Use Configure Error Output to redirect errors to error output so the package will not fail and you can do whatever you need with the row.

    If you absolutely need to validate the format then SSIS expressions are rubbish and the best way is to use a Regex's in a script task. Good article on this here.

  • Thanks for your help

  • latitiacasta (9/27/2016)


    yes i want to write as date in sql server. but before that i need to check format yyyy-mm-dd other format then filter that data

    Redirecting any errors on INSERT (ie, on the destination component) is also fairly easy to do. This avoids the need for any sort of validation in the package itself.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Spiff (9/27/2016)


    --

    If you absolutely need to validate the format then SSIS expressions are rubbish and the best way is to use a Regex's in a script task. Good article on this here.

    As this would be something which would occur in a data flow, it would be a script component rather than a script task.

    And rather than using a regular expression, it would perhaps be more intuitive to use something like DateTime.ParseExact.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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