When loading DATE type data from an Oracle database into a datetime column in a SQL Server database using SSIS an error such as this can occur:
[OLE DB Destination ] Error: There was an error with input column “EFFECT_DTE” (998) on input “OLE DB Destination Input” (651). The column status returned was: “Conversion failed because the data value overflowed the specified type.”
This can be caused when the data value in the Oracle database is 1/1/1700, Oracle’s implementation of a null date. To fix this, create a Derived Column transformation after the OLE DB Source item. In the Derived Column, using EFFECT_DTE as the example field name, enter the following:
If you are installing PerformancePoint Monitoring Server on SQL Server 2008 there are some prerequisite steps. PerformancePoint Monitoring Server requires SQL Server 2005 client component software. After SQL Server 2008 is installed and properly updated, then Cumulative update package 9 for SQL Server 2005 Service Pack 2 must be installed. This CU9 is a hotfix and has to be requested at http://support.microsoft.com/kb/953752.
After applying the SQL Server 2005 update, PerformancePoint 2007 can be installed but not configured until after PPS SP2 is applied.
Microsoft’s Technet has step by step instructions for these installations and updates at:
I was creating an SSIS package to load data from a flat text file into a SQL Server 2008 database table and kept getting a data conversion error on a date field. It turned out that the date was empty in the flat file and that’s ok, it should just update the table with a value of NULL. The OLE DB Destination task was not having it. For some reason it did not recognize the empty field from a text file as a null value.
The specific error was:
There was an error with input column “datefieldname” on input “OLD DB Destination Input”. The column status returned was: “The value could not be converted because of a potential loss of data.”.
The solution was to change the data type in the Flat File Connection Manager to string for dates and add a Derived Column task. The formula to convert the string to a date was: