Will Thrash's Blog

Business Intelligence

Cannot convert from unicode to non-unicode error for Oracle 10g data source when running in 64-bit mode

Problem:

For packages that I have created to read Oracle 10g tables, that work fine with debugging in 32-bit mode, I get an error message on all string fields when I try to run in 64-bit mode. An example error message is:

[OLE DB Source [1]] Error: Column “ACCT_UNIT” cannot convert between unicode and non-unicode string data types.

Another interesting warning included is:

[OLE DB Source [1]] Warning: The external columns for component “OLE DB Source” (1) are out of synchronization with the data source columns. The external column “ACCT_UNIT” needs to be updated.

I cannot even try to convert this data with a Data Conversion item because the (red) error is on the OLE DB Source item and stops there. It doesn’t matter what the destination is or even if there is a destination in the package yet.

I’m using Oracle Provider for OLE DB, Oracle Client version 10.203 for 32-bit and Oracle Client 10.204 for 64-bit.
Oracle is 10g on a UNIX 64-bit server and the data is not unicode.
I’m using SQL Server Enterprise 2008 (10.0.1600) on Windows Server 2008 Standard SP1 on a 64-bit server.

The packages work fine in 32-bit mode and the data is not unicode data. When I change Run64BitRuntime to True in the Debugging Property Page, I get the error on the OLE DB Source item. I also get the error when I schedule a package to run using the SQL Server Agent.

Solution:

Install Oracle 11g clients. There is a bug in the Oracle 10g 64-bit client that does not exist in 11g. The 11g client is compatible with Oracle 10g.

May 18, 2009 Posted by | Oracle, SQL Server | , , , , , , | 1 Comment

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

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.

May 7, 2009 Posted by | Oracle, SQL Server | , , , , , , | 1 Comment

Problem using an SSAS data source in IBM Cognos Framework manager

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.

May 6, 2009 Posted by | SQL Server, Business Intelligence, IBM Cognos | , , , , , , , | 2 Comments

   

Follow

Get every new post delivered to your Inbox.

Join 527 other followers