Tag Archives: SSIS

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

1 Reply

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.

Advertisement

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.

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.

Training courses

Leave a reply

Preparing for a four day private training course for a client.  We are delivering Intro to Business Intelligence, SQL Server 2008 Analysis Services (SSAS), SQL Server 2008 Integration Services (SSIS), Sql Server 2008 Reporting Services (SSRS) and PerformancePoint/SharePoint dashboard and scorecard building. 

I recently was made a Microsoft Certified Trainer along with one of my colleague, Carl Sheffield.  We, along with Jeff Wall, MCT in process, will be delivering the courses.  The time is abreviated so our objective is to achieve a learner level of understanding of business intelligence and the Microsoft tools involved.  They will have heavy hands-on labs to make them comfortable with the basic use of the tools.

It’s amazing how much more you learn about a subject when you have to prepare to teach it.