How to handle Inavlid Date Columns in Source while Loading Data into tables in SSIS?

Ramana Kopparapu 226 Reputation points
2024-02-26T03:47:07.4066667+00:00

Hi All, I received data from Data Lake. I need to load the data from DL to Stage tables using SSIS. Recently, I received data in DL having some invalid date columns. Actual Data in Date column looks like '1900-01-01:00:00:000" But for records the data appeared in Source like '-6084792' and there are 8 rows in similar way.. How can I handle the data in this case in SSIS? Could anyone please suggest? Thanks in advance..

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,459 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dronec 166 Reputation points
    2024-02-26T09:23:35.6933333+00:00

    When reading the columns, cast them to String and then use Conditinal Split to send normal dates to one flow and corrupted ones to another. Use a Derived Column transformation to replace corrupted values.

    0 comments
  2. ZoeHui-MSFT 33,286 Reputation points
    2024-02-28T02:58:15.9733333+00:00

    Hi @Ramana Kopparapu,

    Add Data Conversion in the package and also need to check the Source column data type to match them.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments