December 21, 2010 at 1:12 am
Hello,
We got this problem yesterday and have not been able to solve it.
Yesterday our company got the assignment to redo one of our customers DTS packets to a SSIS,
to allow for migration from SQL Server 2005 to a SQL Server 2008 64 bits.
(Altough this is not my project I'm having the same problem during my training)
The problem we have is when we attempt to make a source or destination with the Oracle database we are getting errors on the Test Connection, if we don't test the connection no warnings occur but the packet fails to execute.(ORA-0413, ORA-12154 are some of the errors we've been getting)
From what we've been able to gather the (x86) is the root cause of this,
we tried several solutions we found on the net but none are practical for a deployment server or even work at all.
So please if anyone had this problem and knows how to solve it please tell.
December 21, 2010 at 1:49 am
As gathered from the forums SSIS-designer uses the 32-bit driver, when SQL Server actually executes it it uses the 64-bit driver.
Do you have both oracleclient versions installed?
December 21, 2010 at 2:01 am
How can I confirm this,
and if yes how do I start the oracle database.
Sorry for these maybe newbe questions but I haven't used Oracle in over 3 years and if you're using MS SQL for all that time you get spoiled
December 21, 2010 at 5:28 am
At the server running the SSIS package you already have an Oracle-client installed (otherwise you couldn't make a connection).
The location of the different oracle clients should show up uner "All Programs" like Oracle-OraDB10g_home1,Oracle-OraDB10g_home2.
If you start the Oracle Installer under "Oracle installation products" there is a button to show installed products.
To test the connection in the commandprompt
sqlplus user/mypassword@SIDofDatabase
December 21, 2010 at 5:28 am
Ok I downloaded the clients from Oracle
Oracle Database 11g Release 2
Standard Edition, Standard Edition One, and Enterprise Edition (11.2.0.1.0)
32 bits and 64 bits versions
Unzipped the files into win32_11gR2_database_1of2 and win64_11gR2_database_1of2.
Started the setup and now becomes the first difference with the explanation here.
The wizard looks different and I don't have the option for Custom install, once installed I started up the universal installer (Which gives me the install wizard on, if I start the universal installer from the unzipped maps I'm getting another wizard), I select custom install and select "Oracle Windows Interfaces 11.x.x" to be installed, I click next and after a few minutes I get Error Dialog: OUI-10150:Error: A runtime exception occurred while setting s_dlgCfgNamingLabelSIDnull in component Oracle Database 11g 11.2.0.1.0 .
And both options available to continue result in the same me returning to the start of the Universal Installer.
What am I doing wrong
Atm I'm working on a standalone computer since I'm in training
December 21, 2010 at 5:48 am
Hello,
*try at dev-environment first*
You have downloaded the full database versions instead of the clients.
You can download the 32-bit client (win32_11gR2_client.zip)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
You can download the 64-bit client (win64_11gR2_client.zip)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Usually install the 64-bit client first with "Adminstrator defaults" instead of instantclient
Afterwards the 32-bit client, so that 32-bit becomes the default.
After you installed the clients, you should have a look at (both) the tnsnames.ora in the network/admin subfolder of the oracle_homes.
Tnsnames.ora holds the connection info.
Also check sqlnet.ora and set authentication to (none) if you don't use windows authentication.
Test of the tnsnames is succesfully configured (in the commandprompt)
Tnsping myoracledatabasealias
it also shows you which version is the default (32-bit or 64-bit) and which tnsnames it uses.
Test the connection itself: can be done using odbc or sqlplus (sometimes tricky)
in commandprompt
sqlplus myuser/mavalue@mydatabasealias (with the alias configured in tnsnames.ora).
December 21, 2010 at 7:08 am
Installed the 32-bit database tried to make a database with Database Configuration Manager (Error database was not created)
I can only find a sample tnsnames under the 64 bits client map none such file in the 32bits map.
4 bits connection.
The 32 bits client is not even showing up under installed products in the Universal install so i can't un install it
December 22, 2010 at 5:40 am
a small recap (and in what order?)
you have installed the 32-bit database (was this software only?) at oraclehome1 (not necessary), oracleinstaller not found?
you have installed the 64-bit client at oraclehome2, which has a sample tnsnames.ora, oracle installer is at location Y
? you have installed the 32-bit client at oraclehome3, oracle installer not found?
December 23, 2010 at 2:06 am
First off i'm quite new working with oracle (well I have worked with Oracle before specificlly the 9i express edition 3-4 years ago, so I found myself very rusty)
Atm I'm preparing a total install over (after succeeding yesterday in installing an 11g database software that gave no errors during install, and where the dbca tool didn't give any errors)
I will no install a 32 bits 11g database, so I can create new databases.
I need however help in figuring out how to get onto these databases.
Then a 64 bits 11g Client in administrator followed by a 32 bits 11g client, but in what mode must the 32 bits client be installed.
December 23, 2010 at 4:03 am
OK My boss succeeded in making this work I'll keep trying to make it work as well.
December 23, 2010 at 5:17 am
ALtough I'm still getting errors
December 23, 2010 at 2:01 pm
Almost.
What culprit did your boss fix?
Can you make a connection to the oracledatabase outside SSIS?
January 19, 2011 at 3:29 am
OK
After a month or so I'm confronted again with this,
I installed the 64 bit client option Administrator
I installed the 32 bit client option Administrator
I did tnsping to my db, called training.
It gave a TNS-03505: Failed to resolve name
I checked the ora files, some were missions so I created them, mostly by copying the existing ones.
tnsping training worked showing me 32 bit connection
tried sqlplus user/mypassword@training (note that I used a valid user for my database)
worked
opened an integration services project
created a datasource (both the MS provider or the oracle one could make a connection)
Choose a table, namely data.solarsystem.
Both preview and colums gave me the following message
TITLE: Microsoft Visual Studio
------------------------------
The component reported the following warnings:
Warning at {008E50D2-03F6-45AC-97FC-020AF0FFEC12} [OLE DB Source 1 [125]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.
------------------------------
BUTTONS:
OK
Cancel
------------------------------
The preview did show me the data that I inputted.(Solved this by putting AlwaysUseDefaultCodePage to true)
When I run the project, an error occurs on the datasource.
Error: 0xC0209303 at Package, Connection manager "training.data1": SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider OraOLEDB.Oracle.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, OLE DB Source 1 [125]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "training.data1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "OLE DB Source 1" (125) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
So what am I doing wrong
New problem, after rebooting the machine this is all on LISTENER refuses to restart.
Listener is back up, but the enterprise manager is acting weirdenterprise manager seems normal.
The error in the SSIS persists.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply