March 25, 2011 at 5:16 pm
I am using an SSIS 2008 package to import data from an Oracle database source to a SQL Server 2008 staging table destination. However, Oracle allows dates that are beyond what the SQL Server 2008 datetime data type allows. As you know, the SQL Server datetime range is January 1, 1753, through December 31, 9999. So now I have to deal with a way to handle the dates outside of that range. I am considering using two conditional splits (one for dates before 1753 and one for dates after 9999) and then derived columns to replace those dates with NULL values (the boss wants NULLs). Is this the most effective way of doing this? Is there a more performant or efficient way? I wanted to handle this at the source with a PL/SQL statement, but I am not allowed to do that.
March 27, 2011 at 9:07 pm
imani_technology (3/25/2011)
I am using an SSIS 2008 package to import data from an Oracle database source to a SQL Server 2008 staging table destination. However, Oracle allows dates that are beyond what the SQL Server 2008 datetime data type allows. As you know, the SQL Server datetime range is January 1, 1753, through December 31, 9999. So now I have to deal with a way to handle the dates outside of that range. I am considering using two conditional splits (one for dates before 1753 and one for dates after 9999) and then derived columns to replace those dates with NULL values (the boss wants NULLs). Is this the most effective way of doing this? Is there a more performant or efficient way? I wanted to handle this at the source with a PL/SQL statement, but I am not allowed to do that.
Since you are using SQL 2008, try using the DATE data type instead. This will (at least) increase the range of valid dates to 01/01/0001 to 31/12/9999.
If you are getting dates outside of that range in a commercial application, I have to wonder what meaning to attribute to the value. This becomes a data cleansing issue which, ideally, needs to be addressed in the source system. If you can't get the source system changed, use a derived column transform (not a conditional split) to apply the rules and the resultant column used at the end of your data flow.
March 28, 2011 at 12:01 pm
Thanks for the great advice! Does anyone know what affect the new DATE datatype has on Cognos? Has anyone experienced the new data type hurting Cognos reports?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply