Tag Archives: Lessons Learned

Oracle 1/1/1700 date causes error in SSIS load to SQL Server 2008

1 Reply

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 [638]] 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:

Derived Column Name

Derived Column

Expression

EFFECT_DTE

Replace ‘EFFECT_DTE’

EFFECT_DTE == (DT_DATE) “1700-01-01” ? NULL(DT_DATE) : EFFECT_DTE

 

This expression checks if the field is 1/1/1700 and if so converts it to a null date for SQL Server otherwise it passes the date through.

Advertisement

Problem using an SSAS data source in IBM Cognos Framework manager

8 Replies

When trying to use a Microsoft SSAS OLAP cube data source in Framework Manager you get this error message:

MO-ERR-0028 The user starting the process doesn’t have the correct privileges

The solution is as follows:

  1. Use a network domain account for the Cognos service
  2. On Cognos server, in Local Security Settings under Local Policies/User Rights Assignment add the cognos service account to “Act as part of the operating system”
  3. ON SQL Server give cognos service security access to cube
  4. For the SSAS Data Source in Cognos Connection use IBM Cognos 8 service credentials for Signon.

Installing PerformancePoint on SQL Server 2008

Leave a reply

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:

http://technet.microsoft.com/en-us/library/dd335966.aspx

Error loading null dates from a text file into a database using SSIS.

2 Replies

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:

LEN(datefieldname)==0?NULL(DT_DBDATE):(DT_DBDATE) datefieldname

This checks for an empty string and returns a DB Date NULL value else it converts the string to a date.