Oracle & Data Conversions

  • We have our warehouse in Oracle. So, we have a number of columns coming out. Some of them were stored in Oracle as varchar, but SQL Server has them as nvarchar. Simply doing a straigt connect between source & destination in the data flow causes an error: "cannot convert between unicode & non-unicode string data types."

    Obviously, we can put a Data Conversion transformation between the source & destination in order to clean this up. However, there are a lot of these. Does anyone have another option?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • check fastreader from wisdomforce : http://www.wisdomforce.com

    We are using it for high-speed data transfer from our Oracle production into SQL Server

  • Thanks for the pointer. We're pretty committed to sticking with Integration Services, replacing DTS, and from what I saw on the web site this is a seperate tool, not an add-on to Integration Services.

    Anyone else have some advice?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • We have had the same problem trying to import our data from a JD Edwards database on an as\400 to sql server 2005.  In our case we use the ibm Client access odbc driver.

    We were told that we should use the ole db provider to connect to the 400. (Naturally, we have to install ole db on the 400 first before we can try that).  We were told that the MS odbc provider delivers the data to sql server 2005 as unicode and that there is nothing that can be done about that.  As a work around, it was suggested to create a temp table with the unicode fields then use the ole db provider with the wizard to automatically convert the fields to non-unicode.  We have not had much success with this either as the wizard fails attempting decimal to smalldatetime conversion.   We too would be intrested in anyones suggestions to this troubling problem.

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply