How do I convert invalid entries from a file for date field to NULL when populating to a table column?

Question

I am new to SSIS and I am working on an assignment where I am reading some columns from a csv file and populating them into a SQL server 2008 table using SSIS. There is a column in the file for date field. If the column has any value other than a valid date in yyyy-MM-dd format, I need to change that to NULL and populate it in the SQL server table. Can someone help? Currently, the file has "-" for some records and blank for some records. I see that I can use the ternary operator with ? and : just like java, but I am not sure how to use it if the file has any invalid value than the actual date.

Can someone help?

Answer 1

You can achieve this 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

You can get a more detailed answer with more approaches in the link below:

• wrong Dates values in ssis