wrong Dates values in ssis

Question

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?

Answer 1

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

• USING THE ERROR OUTPUT OF THE DERIVED COLUMN
• How to prevent CAST errors on SSIS?

(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.