Wednesday, February 16, 2011

Date Validation is SSIS

I needed to import a large amount of data from an Access database and the standard SQL Server Management Studio would fail when it ran into a record with a bad date.  I ended up saving the import as an SSIS package (file system) and then editing the DTSX file.  I was able to override bad dates to Null by adding a Derived Column with a date validation that replaced the existing column in the dataset. 

Full SSIS validation expression:

 

((DT_Date)Date_to_validate < (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_to_validate

No comments: