SSIS - Connecting to Oracle

Hot Topic

Remote Connection problem on MS SQL 2000
Remote SSIS connection problem
proper way to drop publication?
SQL Server 2005 Express
Stored procedure issues...
Suddenly breakpoints in custom component not hit...
Using variables of Connection Manager, Connectionstring
Error 1418 and Good Basic Setup Guide
Numerous (every 10 minutes) Event log entries
Flat File Source Queston

SQL Server

Date parameter format incorrectly interpreted
sp_helplogins
Connect to object explorer but cannot connect to Integration services.
Push Merge snapshot fails with Data is Null message.
The operation has timed out
Passing variables to data flow component
Resources about Load/Run SSIS packages using .NET Remoting.
Error with for..next loop variable
Basic Mirroring Setup Help
Sql Server Encryption

SSIS - Connecting to Oracle

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

Answer this question

SSIS - Connecting to Oracle

  • Nicholas Wagner

    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

  • Yaron Becker

    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

  • RobSmith

    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

  • JorgeRoca

    Larry, that worked fine... Thank you ... I will have a look through the other Oracle posts.

    Cheers

    Pete

  • VinceVega

    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

  • Autechrian

    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



  • SSIS - Connecting to Oracle
Answer this question