5

I am working with ssis. I have a column that contains date in format integer YYYYMMDD: For example to day I get 20190214.I am using derived column to convert it in format date YYYY-MM-DD in my case for example 2019-02-14

But sometimes I receive wrong values for example 20188101. How could I test that I have good values to be converted to date?

Hadi
  • 36,233
  • 13
  • 65
  • 124
test test
  • 83
  • 11
  • Your using SSIS but what DB are you using? MSSQL? Are you saying your wrong values the ones you are trying to convert (your source data)? If your source data is bad you have to fix it at the source, or put in a check to find it is an invalid date and do something with those records. – Brad Feb 14 '19 at 17:58
  • What DB are you using?? – Sebin Thomas Feb 15 '19 at 06:35

1 Answers1

3

There are 3 methods to achieve that

(1) Using Another Derived Column

Beside of the first Derived COlumn, you can add another derived column with the following expression:

(DT_DATE)(LEFT([DateColumn],4) + "-" +  SUBSTRING([DateColumn],5,2)  + "-" + RIGHT([DateColumn],2))

If the date value is incorrect redirect the bad from Error Output configuration , and you can handle these bad values from the Error Output.

Helpful Links

(2) Using a Script Component

Add a script component to check if the value is correct, and add a Output Column of type i.e. OutDateColumn, use a similar code (VB.NET)

IF Not Row.DateColumn_IsNULL Then

dim dtDate as DateTime

    If DateTime.TryParseExact(Row.DateColumn,"yyyyMMdd",System.Globalization.InvariantCulture,System.Globalization.DateTimeStyles.None  , dtDate ) Then

        Row.outDateColumn = dtDate.ToString("yyyy-MM-dd")

    Else

        Row.outDateColumn_IsNull = True

    End If


Else

    Row.outDateColumn_IsNull = True

End If

(3) Add a Data Conversion Transformation

After the derived column Transformation, add a Data COnversion Transformation and try to convert the Date Derived Column you add to DT_DATE, if conversion failed, than handle the bad values from Error Output as explained in the first method.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 2
    Great answer. I was going to update my post with a similar Script Component conversion but looks like you beat me to it. Gets my upvote. – userfl89 Feb 14 '19 at 18:33