locked
SSIS - Connecting to Oracle RRS feed

  • Question

  • Hi,

    I am new to using SSIS (after using DTS on SQL Server 2000 for many years).

    I am trying for the first time to connect to an Oracle 9.2 database and export data from it into SQL Server 2005.

    I used the Microsoft OLE DB Oracle Provider to connect to the Oracle instance. The test connection works fine, but when I try and preview a table or copy data from Oracle I get the error....

    The component reported the following errors

    Cannot retrieve column code page

    Has anyone got any ideas how I fix this?

    Many thanks

    Pete

    Thursday, January 19, 2006 2:56 PM

Answers

All replies

  • Assuming your using western alphabet (code page 1252 Latin I), just right click -> properties on the OLE Src and set the AlwaysUseDefaultCodePage to TRUE. 

    If you aren't using code page 1252 in Oracle, you'll need to set the DefaultCodePage in the same properties window.

    You may want to review some of the other posts regarding SSIS and Oracle as they contain many useful hints and workarounds.

    Larry Pope
    Thursday, January 19, 2006 3:25 PM
  • Scott Barrett has done alot of work of using SSIS with Oracle and has written about it on his blog: http://microsoftdw.blogspot.com/

    Donald Farmer has a gret post on the subject as well: http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

     

    -Jamie

     

    • Proposed as answer by denduluri Thursday, September 29, 2011 4:06 PM
    Thursday, January 19, 2006 3:31 PM
  • Larry, that worked fine... Thank you ... I will have a look through the other Oracle posts.

     

    Cheers

     

    Pete

    Thursday, January 19, 2006 3:36 PM
  • Here is one of the original threads regarding Code Pages.
    http://forums.microsoft.com/msdn/showpost.aspx?postid=117014&siteid=1#117014

    It also discusses some potential Number/Decimal conversion issues that may occur with your version of Oracle.  I haven't seen this issue with 10g r1/r2.

    I also have one other suggestion if you are using BLOB/CLOB columns in Oracle.  The best way of importing these columns is to use the DataReader and use the .GetClobVal or .GetBlobVal function in your SQL statement.

    Larry Pope
    Thursday, January 19, 2006 3:46 PM
  • Hi Larry, I was attempting to extract from SQL 2005 and import into Oracle 10g into existing tables. I have the logic worked out on this, but my problem is that when I select OLE DB source and attempt to open a table from the Oracle 10g database, it throws an error. The issue is mainly dealing with the DB Types of CLOB. I notice you listed using a DataREader and use the .GetClobVal() but i have been unsucessful in my attempts at this. Could you further clarify on this issue? Thank you very much
    Thursday, April 27, 2006 10:16 PM
  • Hi there,

    I have just tried this proposal solution, but in my case it unfortunatelly didn't work fines.

    The charset used by my Oracle is WE8ISO8859P1. Do you have idead what could still causing this issue about data precision from Oracle?

    Another thing that I noticed is that all columns which have this issue are columns defined as NUMERIC data type at Oracle, but these columns don't have any precision defined and the SSIS warning says the precison MUST be between 1 and 38...

    Any help will be apreciated so much.


    Thank you,
    Luis Antonio - Brazil
    Thursday, December 13, 2007 6:56 PM
  • Free High Speed connector for Oracle SSIS package destination:
     microsoft download

    From the Microsoft SSIS team blog:
    Microsoft Fast Oracle SSIS connector available
    Wednesday, April 1, 2009 2:40 AM
  • Monday, December 14, 2009 7:55 AM
  • We just find out the solution:

    At Solution Property Pages --> Configuration Properties --> Debugging

    set Run64BitRuntime as False

    At package protection level set as EncryptSensitiveWithUserKey or WithPassword

    The package will remember oracle password and the package will work!

    Monday, July 7, 2014 7:21 PM