iSeries TimeStamps to SQLServer using SSIS
Here is a simple enough problem, the IBM iSeries (AS400) allows for a date range that starts at a lower value then SQL Server can handle, but one that SSIS can handle. This can cause a slight issue when trying to import data from an iSeries into a SQL Server. This can be handled by a derived column transformation checking the source column for a TimeStamp value less than 1753-01-01, then replacing it with 1753-01-01, or null or whatever other value you desire. Another simple solution is to place this value into a char or varchar on SQL Server buy you may have this problem when you go to query this field later. The data for this example does not contain valid date of a value early then 1753 so I know if I get a date of 0002-01-01 that it is bad source data. I may still want to retain this information in a char/varchar field so I can refer to but it is safe to be replaced in my SQL Server because it is bad data, but the related data may still have value. (Also reporting on bad data from the source system has value in itself)
1st Execute SQL task: Delete the records from my test table on the iSeries
2nd Execute SQL task: Filling the test table on the iSeries with a date time stamp value that SQL Server cannot hold.
Data Flow task: The record set is multicast into two pipelines that end up inserting records into the same table on the same SQL Server. (I wanted to show the complete and failure separately otherwise I would used a UNION and put the results back together) The pipeline on the right hand side goes through a transformation to replace the invalid SQL Server datetime value.
Derived Column: Transformation of the date time SQL server can not handle via a if then statement. (TESTTIMESTAMP < (DT_DBTIMESTAMP)"1753-01-01" ? (DT_DBTIMESTAMP)"1753-01-01" : TESTTIMESTAMP)
The package below runs successfully for the pipeline on the right, while the left fails, notice the values in the TESTTIMESTAMP data viewers, and how it changes.
Pretty straight forward but it took a few minutes for me to think about what I should do when I encountered this error so I figured I would share it with everyone. What is nice is that SSIS can handle a timestamp value outside the range of what SQL Server can.