Hi,Has anyone received the following error when trying to create a connection to an Oracle database using SSIS installed on a 64-bit (x64) machine?"Test connection failed because of an error in initializing provider. ORA-06413: Connection not open"The reason this is funny to me is because I have the same Oracle/SSIS setup on a 32-bit (x86) machine and I can connect successfully.On both machines I have SSIS RTM, Oracle 9.2 and using the Microsoft Ole Db Provider for Oracle.Thanks,- Joel

SOLUTION 3:Start the application using the short name version of the directory paths. For example for the DTS Wizard in SQL Server 2005, run it using this command prompt:C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe

Can you elaborate on Solution 2? Did you just rename the root 32-bit path and change the PATH in Environment variables? Also, on install there is no way to configure the directory for 32 bit components? Is there anyway to configure the destination 32bit tools directories on install? If not, we can put it on the wish list.

We are also facing the same problem with Oracle. Btw, if you guys are looking at high speed data loading into Oracle using SSIS, we have built a high speed connector. More details - please visit http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm Please contact me if you have questions - sidharth<AT>persistent.co.in

It may not be a problem in SSIS but in the driver. Arr you able to connect to the data source from another app running on the same box? The issue I ran into - if you have 32-bit driver - SSIS does not like it very much runningon 64-bit

it seems the oracle patch should apply on client and server. patch 3807408, unfortunately, thepatch is available only for the currently certified versions (9.2.0.7, 10.2.0.1) and not on our current version (9.2.0.4). On top of the database server patch need to apply another patch for the client machine (where DTS/SSIS is running from): - either 4928723 (Description: ORACLE 9I 9.2.0.7 PATCH 6 ON WINDOWS 32 BIT) or 4928724 (Description:ORACLE 9I 9.2.0.7 PATCH 6 FOR WINDOWS (64 BIT)) --I contacted MS and unfortunately option 2 is not really an option There is no option to change the install directory for the 32-bit SSIS tools to one without the (x86). it seems only option for now is execute the DOS format name from the Run command. any other info is greatly appreciated.

I ran into the same problem, the issue as was previously mentioned is because the client components (management studio) are installed into a folder with (x86) in the folder name. There is no control over this path name at install time because the SQL Server install routine is doing it. The majority of the management studio is compiled as a 32 bit application so that is why the (x86) is being appended to the folder where the studio is being installed. When you attampt to import data from within the management studio, the data import wizard (DTSWizard) is passing a string to the Oracle provider with (x86) in the string, this exposes a networking bug in Oracle and it breaks on the parenthesis. There are a couple of workarounds to this and neither are very pretty or what you would want to do in production, but here is a simple workaround that will at least allow you to connect to Oracle and import some data. When you go through the SQL Server installation you have a choice of where to direct the installation of the database services, the data files, and the client components. You can either accept the defaults which go to the C: drive or you can choose another drive and custom folder name. Let's say for the client tools you chose an install path and folder name as follows: D:\ClientTools What you would end up with after the installation is complete is this: D:\ClientToolsD:\ClientTools (x86) Notice you now have two folders called ClientTools, the first one is where some 64 Bit compiled applications are, and the one with the (x86) are where the majority of the client tools are installed, specifically the management studio and the business intelligence development studio. Workaround: Copy, Paste, and Rename the entire ClientTools (x86) folder as follows: D:\ClientToolsx86 Using explorer or from a command prompt, navgiate to the sub folder under ClientToolsx86 and find the DTSWizard, it should be in a folder similar to D:\ClientToolsx86\90\DTS\Binn Execute the DTSWizard.exe and you should be able to successfully connect to Oracle and import data. One of the many problems with this is although you can connect and import data, you can't set it up as an SSIS package or a job under the SQL Agent because there are a ton of registry keys that point to (x86) as the default location for the management studio I have experimented with hacking the registry and changing all the paths and I did get it working. The last problem I ran into was although I could save and execute an SSIS package, it failed to run as a job under the SQL Agent with no explanation as to why. Oracle does provide an x64 Client and the provider does connect, but it cannot import data because there is a data Precision issue where it cannot convert to Float, Numeric with the proper precision and scale. There is a bug submitted for a patch but it has not been released yet. To connect to Oracle using the x64 Oracle client provider you need to execute the x64 DTSWizard which is not in the management studio, it is located in the D:\ClientTools\90\DTS\Binn folder, this is where the 64 Bit compiled applications are. Crazy huh?

That is a very good idea. I use the old DOS 8.3 format name when executing DEVENV.exe. This will allow you to validate Oracle connections but not run the pkg. To runuse the DOS 8.3 path and DTEXEC.exe from 32 bit directory. I also store my connection configurtion in XML and use the command file to execute. The only thing you need to secure is the XML file.

Andy_T wrote:I ran into the same problem, the issue as was previously mentioned is because the client components (management studio) are installed into a folder with (x86) in thefolder name. There is no control over this path name at install time because the SQL Server install routine is doing it. The majority of the management studio is compiled as a 32 bit application so that is why the (x86) is being appended to the folder where the studio is being installed. When you attampt to import data from within the management studio, the data import wizard (DTSWizard) is passing a string to the Oracle provider with (x86) in the string, this exposes a networking bug in Oracle and it breaks on the parenthesis. There are a couple of workarounds to this and neither are very pretty or what you would want to do in production, but here is a simple workaround that will at least allow you to connect to Oracle and import some data. When you go through the SQL Server installation you have a choice of where to direct the installation of the database services, the data files, and the client components. You can either accept the defaults which go to the C: drive or you can choose another drive and custom folder name. Let's say for the client tools you chose an install path and folder name as follows: D:\ClientTools What you would end up with after the installation is complete is this: D:\ClientToolsD:\ClientTools (x86) Notice you now have two folders called ClientTools, the first one is where some 64 Bit compiled applications are, and the one with the (x86) are where the majority of the client tools are installed, specifically the management studio and the business intelligence development studio. Workaround: Copy, Paste, and Rename the entire ClientTools (x86) folder as follows: D:\ClientToolsx86 Using explorer or from a command prompt, navgiate to the sub folder under ClientToolsx86 and find the DTSWizard, it should be in a folder similar to D:\ClientToolsx86\90\DTS\Binn Execute the DTSWizard.exe and you should be able to successfully connect to Oracle and import data. One of the many problems with this is although you can connect and import data, you can't set it up as an SSIS package or a job under the SQL Agent because there are a ton of registrykeys that point to (x86) as the default location for the management studio I have experimented with hacking the registry and changing all the paths and I did get it working. The last problem I ran into was although I could save and execute an SSIS package, it failed to run as a job under the SQL Agent with no explanation as to why. Oracle does provide an x64 Client and the provider does connect, but it cannot import databecause there is a data Precision issue where it cannot convert to Float, Numeric with the proper precision and scale. There is a bug submitted for a patch but it has not been released yet. To connect to Oracle using the x64 Oracle client provider you need to execute the x64 DTSWizard which is not in the management studio, it is located in the D:\ClientTools\90\DTS\Binn folder, this is where the 64 Bit compiled applications are. Crazy huh? does anyone know if there's a knowledge base article for this issue?

This is the knowledge based on real world experiences.:-)

Oracle has released a patch that resolves the (x86) issue on x64 Windows servers. I tested the patch on two servers and I can now connect to an Oracle server via the default locations for SQL management studio and the Business intelligence development studio. After creating my data import packages in SSIS I was able to set up a job using the SQL Agent, point to the SSIS packages and run them, and the jobs are now succeeding. Caution, there is a bug somewhere but I'm not sure where, but when you are in the BI studio and you drag an OLE DB connection onto the page, make sure you set the "Always use default code page property" to "true" otherwise your packages will fail due to an authentication problem with the provider. I am unclear as to whether or not this is a SQL problem or an Oracle problem. Also if you are in the SQL management studio and you use the data import wizard (DTS) to import data from Oracle and save your data import as an SSIS package, the package will fail to run as a job due to the default code page property issue. The process I am using that works is: Use the BI studio to create your data import packages (SSIS), make sure you set the "Always use default code page property" to "true" for the Oracle OLE DB connection, and then finish creating the remainder of your package Save your SSIS package to disk or (file system) Use the SQL management studio and connect to Integration Services to import your SSIS package Use the SQL management studio and connect to the database engine to set up a SQL Agent job that points to your SSIS package within Integration Services Run your job and it should work. The two patches you will need from Oracle are on Metalink so you will need an account to download them: Both of these patches are for the Oracle 10.2.0.1 32 Bit Client 5059238 - Patch for (x86) issue http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5059238 4751549 - "opatch" http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=4751549 Make sure you double check these patches to be sure they are the exact ones you need, I have a hard time following Oracle's numbering schemes.

I experieced the same issues and this is how i solved it.(Without patches) On Windows 2003 x64 with, SSIS(x64) Install a x64 version of the oracle client. Copy C:\Program Files (x86)\Microsoft SQL Server andC:\Program Files(x86)\Microsoft Visual Studio 8 to a directory withouth parenthesis in the name i.e "C:\ProgramFilesx86" Start the dev inviroment from the new location in Microsoft Visual Studio 8\Common7\IDE\devenv.exe Change OleDb connections for .Net Oracle Provider connections and use a Datareader as apposed to a DataSource.

Hey Marcell, I was looking at this article and found that this can work in development. But in production if you would like to schedule it as a sql server job, how can you control it to use this path?

Beware.... I've tried to create a desktop shortcut and windows changes the path everytime I close the edition. When you see the properties, there is the "(x86)" again.I've also tried changing the PATH, which does actually work.

Sam, You just need to schedulethe jobas Operatve Syestem(CmdExec); that way you have control over what path/executables are invoked. Rafael Salas

Hi Rafael, I did exactly the way it is mentioned in this post. For some reason, if I open the devenv.exe from the D:\ProgramFilesx86\Microsoft Visual Studio 8\Common7\IDE and create a connection to the oracle DB using a tnsname, I am able to connect to the Oracle Db and preview the tables. But if I try to run the application and try to import the data, I get an error.."Class not registered" error. Package fails at the first step --OLE DB Source DataFlowComponent. This is what I am trying to do...I would like to run the application from the devenv and if everything runs fine, I would like to schedule an sql job (cmdExec) ---giving the path---to execute the package. But I fail to accomplish the first task itself. I have downloaded ODAC10202.exe and installed it on the machine for oracle connection. Can somebody please help me? Thanks.

Hello all, It seems I have this very same problem. I downloaded and installed the latest versions of the oracle client (10.2) and oracle developer tools (ODT 10.2.0.2.20) hoping that *this time* I was going to be able to connect to the oracle db from the dev environment, or at the very least, from my application. No luck. I've already tried the parentheses solution, but nothing seems to work for me. Truth is that I've been trying this since the first x64 build of the 9i client. The client works fine (hallellujah!!), meaning that I can successfully test the connection from the config tools, and the enterprise manager of oracle can open the db and manage everything, etc. However VS2005 just won't use this data source. I get the ORA-12154 error, same as many others. I also installed the InstantClient, which allowed me to hook up the oracle db to ODBC, where I thought (silly me) the dev environment of VS2005 would be able to use it as a regular data source. No luck either: VS2005 uses its own *ODBC* and this one doesn't work with oracle. For the time being, I think I'll dig up my trusty vmware system and try to run everything in 32 bits, which I know it'll work, but I still wonder if anything else can be done to get it to work under x64. I really want to make it work this way. Could anyone give me a few suggestions/instructions on what could I try to sort this out under x64? Maybe there is something else I could try, like uninstalling the sql server and vs2005 and reinstalling everything under a different path? It's a lot of work for me, but I'm willing to try if there is someone else that thinks this is a good idea (and has already made it to work of course :) ). Thanks in advance

Hi Jaffer, Thanks for that info, I'm very interested in the method you used to make the client to work even with the parenthesis, how can I contact you?

This is how I've solved this problem:1) Install the latest Oracle 9i 32-bit driver version: 9.2.0.8 with the patch released by Oracle, avaliable at http://metalink.oracle.com, applied to your 9i existing version.This patch has the fix for the parenthesis problem at Oracle network layer.2) Run 32 bit aplications: You cannot run 32 bit drivers from 64 bit runtime environment. a) If you're using Visual Studio, then you've no problem, cause it is a 32-bit software. b) In the case you're developing SSIS packages and are planning to program them with SQL Server agent jobs, you need to use the procedure explained at SQL Server 2005 Books Online, wich is: b.1) Use dtexecui.exe to obtain the command parameters for running the package b.2) The complete command to run is: C:\Program Files (x86)\Microsoft SQL Server\90\DTS\binn\dtexec .... plus the previously obtained command parameters. b.2) Create a cmdexec task to run the command.If you cannot install 9.2.0.8 version, then use the command line with the ~c:\Progra~2\.... etc.That works. I've spent a week to get it ready, but now I can connect my old ORACLE 8.0.5 databases from my new 64bit SQL Server 2005. This is only valid for access through SSIS. If you need to link Oracle servers, you need Oracle 10 64-bit drivers. Oracle version 10 is the only one that has released x64 drivers... Oracle 9i is only for IA64, which is not my case.

I have the same problem. Afterfinding first explanation about it I startIntegartion servicefrom DOS with command: C:\PROGRA~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe So,I made connectionto Oracle and SQL server 2005(64 bit)with succes, but DataFlow for import from Oracl toSQL server 2005(64 bit) didn't work. ReallyI got this messages : [OLE DB Source [2408]] Error: The AcquireConnection method call to the connection manager'name of my OLE DB connection manager' failed with error code 0xC0202009. [DTS.Pipeline] Error: component "OLE DB Source" (2408) failed validation and returned error code 0xC020801C. etc. Could you give me more explanation about your solution. Thank

hi thereim curious as to know how you have managed to resolve this too.how do i contact you?thanks

Hi! I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases. - I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange!- I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio! - Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection.- We have even tried an 10G version of Oracle with the same result.Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?Regards

Assuming your 64Bit servers is actually a X64 running either Intel EMT64 or some type of AMD64 processor. Start by ensuring that the asumed 64 bit Oracle client you attemted installing is a X64 version and not a 64Bit version64 Bit implies "Itanium" 64 Bit sometimes refered to as 64Bit or Pure 64.x64 is different and not compatible. Also, once you managed to install either the 32Bit or x64 versions, don't forget to do the other fixes outlined in the beginning of this thread.things like copying some executables to locations without parenthesis in the folder names...etc..etc...etc. I ended-up installing both the x64 version and 32bit version of Oracle Client.Both work after applying the fixes. Enjoy.

Thanks Marcell for your quick answer. My 64 bit server is running onAMD64 processor. I will contact our Oracle specialist again regarding the Orcale versions. I would be back with the resulats later today

Hi! I managed fixing thanks to tips from Ron (http://www.dbforums.com/showthread.php?p=6242791#post6242791) by downloading and installning "Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64)" from Oracle download page. I could set up odbc links and linked servers in my x64 box. Thanks, everyone who replied to my questions.

Hello, There is a solution to connect to your oracle database without using the odbc/oledb connection in ssis. By using linked servers... If you install the correct x64 driver from oracle, you will find into your server objects/linked servers /provider you have to find the OraOLEDB.Oracle provider. Open it and check the allin process. Now you can create a linked server mapped to your oracle database. We use the openquery method to get info from oracle or update/insert/delete. it works perfectly. ... Select * from openquery(LinkedServer,'oracle query') where... To use parameters in the query, you can use the sp_executesql with the query. In your ssis package you simply have to use your sqlserver connection and use a query to get to oracle. This method is working and give a way to bypass the problem (altough without resolve it)... Success to all NB: Dear microsoft members, update your information in sqlserver 2005. Add that connection to oracle is not so easy that you said.... Sqlserver 2005 is a really great product and i love it. But i hear again that's always the same with microsoft products... (bugs,....), how can i promote a great product and have such big problem than a simple connection to oracle !?

Hello again, With the linked server we found a problem with the 64 bit driver (this is not the case with the msdaora driver - 32 bit...) If you work with table that contains NUMBER field in oracle, the query could crash. We found that when the value of the field is like : xxx0.00 the driver seems to have problem and crash. This is an oracle bug. We are not up to date with the driver so i don't know if there is a fix.I will post if it's the case... You can still use linked server, but when using a NUMBER field you have to work on it (trunc,round,...). With this, sqlserver has no problem. Greetings

I was having a similar issue. I was running an EM64T box with Windows 2003 X86-64. I had Sql Server 2000 w/ SP4, and was using Oracle client 10g. When a DTS local package was scheduled through SQL Server agent to do a transaction against an Oracle database, I would get the "Test connection failed because of an error in initializing provider. ORA-06413: Connection not open" error.After troubleshooting it for a few hours, I got I tried several things and got it to work.1. Copied the contents of C:\Program Files (x86)\Microsoft SQL Server to C:\Program Files x86\Microsoft SQL Server .2. Downloaded and installed the latest Oracle 10g client.3. Added "C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn" to my path.I am not sure which of these fixed it, but it is now working.

Our problem is very similar to Maara's. We've installed the Oracle 10.2.0.1 client (32 bit) on our 64 bit machine and applied patch 9 (32 bit patch number is 5695784). I created a connection using .Net Providers for OleDb -> Oracle Provider for OLE DB. I can succesfully test the connection from my connection manager dialog (which I could not do before applying the patch). I'm attempting to pull data from Oracle (well actually Oracle Rdb) on a remote VMS server via a DataReader source and output the data to a Flat File. When I try to executethe taskin debug, my Progress tab shows the following messages: Progress: Validating - 0 percent complete [DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Unspecified error: E_FAIL(0x80004005) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) [DTS.Pipeline] Error: component "DataReader Source" (1) failed validation and returned error code 0x80004005. Progress: Validating - 50 percent complete [DTS.Pipeline] Error: One or more component failed validation. Error: There were errors during task validation. Validation is completed Any ideas what's going wrong for me? I can do this same thing on a 32 bit server without a problem. Thanks in advance, Pat

My co-worker helped me to solve this problem. We were trying to run the package in debug when we were getting these error messages. We view the properties for the solution (right click on the solution in Solution Explorer and select properties), go to the Debugging property, and change Run64BitRuntime to False. After that the package run just fine in debug. Have yet to try to deploy the package so no idea about non-debug. Dang, SSIS can be a pain in the rear! Pat

Steps 1 and 2 above worked fine for me. We had it up and running in about an hour after downloading the 9.2.0.8 patch. Thanks Clon!

Andy - Can you tell me how to install these patches? do i have to install both oracle 10 g 32bit client and 64 bit client? can you shwo me the step by step to install these patches?

We ran into a similar issue with a .Net program we created to talk with oracle. We kept getting eorror: 'ORA-12154: TNS:could not resolve the connect identifier specified'. After many hours of digging we reinstalled the application on the secondary drive which did not have a Program Files (x86) folder but only a Program Files folder. Once we did this all was well -- having the executable in the folder with a parentheses was the problem.

I've written up suggested instructions to develop packages on an x64 box that hit Oracle: http://www.artisconsulting.com/Blogs/tabid/94/EntryID/5/Default.aspx Hope that helps.

Hi Joel, I have same issue as you mentioned in this alert. I saw many ppl have given answer but i do not know which solution work best for you. Could You please tell me how you fix this error. Thanks

I just went through this and it wasn't fun.We setup a Linked Server to the Oracle database but I stillhad to deal with some numeric data type issues. Friggin sucked

Hi there , I have the same problem in X64 8 cpu Win 2003 with SQL server2005 and Oracle 9.2.0.7 server& client Tried various option to avoid installing oracle patch or changing directory name and was successfull .. uphhhhhhhh 1. Installation on the default directory c:\ProgramFiles (x86)\ was okay to develop something when contents were copied to another directory without brackets. However wasn't good to run something. 2. Uninstalled SQL server and reinstalled on differnt drive , 3. Still x86 was created on the new drive, intrestingly the folder containingDTSRUN exewas created in C drive, Looks like Microsoft loves to write here. 4. Changed the path environmental variable as C:\Progra~2\......... and D:\Micros~1\... and thats pretty much it.. now i'm able to execute any damn thing :-) Let god help me and microsoft.

Hi I am facing a problem with SQL Server 64 Bit SSIS Package & Oracle 32 Bit fo Connection.Connecting on 64 bit sq lServer 2005 using Visual Studio( SSIS packages)to Oracle 32 bit other server.While connecting giving an error,even though i know Visual Studio 32 bit.(Does not support GUI based for running). Environment: S1-----> DBServer : 64 bit Windows Server 2003 Enterprise Edition, 64 Bit SQl Server, 64 Bit Oracle 10g Client S2----->.AppServer :32 bit Windows Server 2003 Enterprise Edition, IIS, 32 Bit Oracle 10g Client & Server DB i am unable to connect S1---->S2 using SSIS packages?I am running through command line only. How can i solve this problem? pl give steps for going right direction. Help will be appreciated. Thanks johnny dbajohnny@gmail.com

dbajohnny wrote: Hi I am facing a problem with SQL Server 64 Bit SSIS Package & Oracle 32 Bit fo Connection.Connecting on 64 bit sq lServer 2005 using Visual Studio( SSIS packages)to Oracle 32 bit other server.While connecting giving an error,even though i know Visual Studio 32 bit.(Does not support GUI based for running). Environment: S1-----> DBServer : 64 bit Windows Server 2003 Enterprise Edition, 64 Bit SQl Server, 64 Bit Oracle 10g Client S2----->.AppServer :32 bit Windows Server 2003 Enterprise Edition, IIS, 32 Bit Oracle 10g Client & Server DB i am unable to connect S1---->S2 using SSIS packages?I am running through command line only. How can i solve this problem? pl give steps for going right direction. Help will be appreciated. Thanks johnny dbajohnny@gmail.com are you using the 32 bit version of DTExec? if not, then try it.hth

Hi, Duane, I am using 64 bit version of DTExec. Can you give right solution to solve the problem. Thanks John

dbajohnny wrote: Hi, Duane, I am using 64 bit version of DTExec. Can you give right solution to solve the problem. Thanks Johndo you know the exact nature of you problem? if so, did you try the solutions mentioned in this thread?hth

Have you tried passing it down as a parameter on the command line in SQL Agent?

Hi All We are having the same problem, The Server is an Itanium Windows 2003 Server and SQL Server 2005 IA64 bit, Oracle Client 10g 64 Bit I am able to connect to the Oracle database using Oracle SQL+plus, but when I use the DTSWizard in Management Studio, I get the error saying the Oracle Clients are not installed and need to install them, whereas it is installed. From this post I changed the path to Progra~2 but still not luck, In the Providers dropdown I could not see OraOLEDB Oracle client where as this is installed in the bin directory of Oracle Home. Please any solutions. Regards laks

Ok, I have spent 200 to get a Technical Support from Microsoft as it was not going forward. The mistake was I was using the 32 BIT DTSWizard where the Oracle Clients are not shown simply becuase Oracle 64 Bi client was installed. Everyone the solution do Oracle Client Install 10g and apply the ODAC (Oracle Data Access Components) as well. Do not use the DTSWIzard from the SSMS (SQL Server Management Studio) as it is 32 bit. In the installation folder there will be similar folders with Program Files and Program Files (x86). Use the Program Files folder which is the 64 bit tools and go to the directory \Microsoft SQL Server\90\DTS\Binn where you will find the file DTSWizard.exe which is the 64 bit one. If you run this wizard you can see the Oracle Drivers and it works beautifully in a 64 bit environment. Happy with the support given by Microsoft as well Lakshmi

Hi Jaffer Li, Can you send me the solution how to solve this issue. my email id is dbajohnny@gmail.com I am trying it to solve,but not.i am expecting from you right solution. Thanks John T

Hi, I tried to install the recommended oracle patch'es for windows 64 bit, but that didn't helped. The only way I cound make this work was by installing the application that loads the oracle driver (which in my case is a 32 bit application) under a path without brackets. I installed it under C:\Program Files\... instead of C:\Program Files (x86). Then the application could load the oracle driver correctly!!!!!

I didn't think this was going to work but it did. I uninstalled apache and php and reinstalled them to be C:\Apache2.2\ and C:\PHP\ and they worked perfectly

I spent several hours with this problem. I'm developing an ASP.NET application with Oracle XE 10g on Vista x64. This error occurs when I try to debug the application and even when I run it on IIS7 locally. Unfortunately, there is no patch for Oracle XE available - I'll have to throw XE away now! PS: I tried to reinstall Visual Studio 2008 to another location without paranthesis - did NOT help.

shaper wrote: I'm developing an ASP.NET application with Oracle XE 10g on Vista x64. This error occurs when I try to debug the application and even when I run it on IIS7 locally. Can you post us the error? Are there related errors in Event Viewer?

Sam_res03 wrote: Hi Rafael, I did exactly the way it is mentioned in this post. For some reason, if I open the devenv.exe from the D:\ProgramFilesx86\Microsoft Visual Studio 8\Common7\IDE and create a connection to the oracle DB using a tnsname, I am able to connect to the Oracle Db and preview the tables. But if I try to run the application and try to import the data, I get an error.."Class not registered" error. Package fails at the first step --OLE DB Source DataFlowComponent. This is what I am trying to do...I would like to run the application from the devenv and if everything runs fine, I would like to schedule an sql job (cmdExec) ---giving the path---to execute the package. But I fail to accomplish the first task itself. I have downloaded ODAC10202.exe and installed it on the machine for oracle connection. Can somebody please help me? Thanks. I was cheching that this thread is very oldsomeone founds a solution yet? Regads, -Marcos

Hi Joel, Take a look at my blogs post: http://blogs.microsoft.co.il/blosg/BeI The main Issue is that Oracle Identify your OS version as 64bit, hence installed the assemblies of the 64bit. Which is good since, there are no automatic togles between the both. The best workaround I've stumbled upon, : 1.Manually copy the x86 assemblies into theGac. 2. Copy the Provider section from the machine.config of the 64bit framework into the x86 version (altering the public key to match the x86 version) Please follow the correct Oracle procedure, which is described in details in my blog. Regards, Eran

I just ran into this same problem with my Windows XP 64 machine. I found a solution that is working for me. Here is what I did:1) Fresh install of Windows XP 642) copy "Program Files (x86)" to "Program Files x86"3) use a registry editor to do a mass change of "Program Files (x86)" to "Program Files x86"4) use regedit and verify that these three keys all point to the new "Program Files x86"a) HKEY\local_machine\software\microsoft\windows\currentversion\ProgramFilesDirb) HKEY\local_machine\software\wow6432node\microsoft\windows\currentversion\ProgramFilesDirc)HKEY\local_machine\software\wow6432node\microsoft\windows\currentversion\ProgramFilesDir (x86)5) reboot machine6) delete "Programs Files (x86)"7) install drivers, SP2, updates,and software.8) after each install verify that "Program Files (x86)" has not come back.Oracle XE is installed and fully functional.I can open the admin web page, make local DB connections, and have other machine connect to the Oracle DBIt was a lot of work figuring this out, but worth it.I should note that I have no idea if this would work on a machine that has "stuff" already installed on it.I suspect that there would be many scripts on the system that would require editing.This is why I elected to make my registry changes right after the initial install.So any software installed later would use the "good" path names when creating any scripts or batch files.Good luck people!

Hi Tim_hsu Following the suggestion by Brain Murphy i performed the following and i was able to both debug the solution on a 64 bit server and also execute the package via sql agent Environment Windows 2003 SP2 X64 SSIS 2008 (also applies to SSIS 2005) The steps I carried out to resolve the problem are shown below 1) Installed the appriopriate Oracle client (32 bit is ok here) and ODAC component(32 bits and 64 bits) 2) Changed all environment variables for Program File (x86) and Microsoft SQL Server (x86) directories used by Visual Studio/2005/2008 and SQL 2005/2008 binaries to Progra~2 and Micros~2 respectively. 3) Created \Program Filesx86 folder and copied the following files for ssis solution development \P \Program Files\Microsoft SQL Server (x86) to \Program Filesx86 \Microsoft SQL Server b) \Program Files (x86)\Microsoft Visual Studio 8 to \Program Filesx86 \Microsoft Visual Studio 8 c) \Program Files (x86)\Microsoft Visual Studio 9.0 to \Program Filesx86 \ Microsoft Visual Studio 9.0 4) Start the development environment from \Program Filesx86 \Microsoft Visual Studio 9.0 \ Common7\IDE\devenv.exe to create or edit SSIS solution. a) Note: this is the only way the 32 bit providers will be available however the package cannot be run in this mode. To run the packages create a batch file. 5) Call DTExec.exe in a batch file using x 86 path with ~ .For example a) \Program Files\Micros~2\100\DTS\Binn\DTExec.exe" /FILE for SQL 2008 OR\Program Files\Micros~2\90\DTS\Binn\DTExec.exe" /FILE for SQL 2005 N Note: You must supply the path to the 32 bits (Microsoft SQL Server (x86) ) path here not the renamed path in step 3. 6 6) Create SQL agent using Operation system type (CmdExec) to call batch file i.e"<<file path>>\batch.bat" include quotes v Snr BI Architect/Developer

I have the same problem. I tried various things from this post, but none really worked. I have Windows Server 2003 R2 x64 with SQL Server 2005 Standard x64 (latest patch level). I installed 32 and 64 bit version of Oracle 10.2 client. I used RegSAR to replace all references to "C:\Program Files (x86)\Microsoft SQL Server" with "C:\Progra~2\Microsoft SQL Server". Since then, I can use Import and Export wizard to import Oracle data and save the packages. However, I'm still unable to schedule them. Using SQL Server Agent simply tells me "failed" but without any specific reason. The 32-bit version of DTEXEC gives an errorDTS_E_PRODUCTLEVELTOLOW at every step. The 64-bit version of DTEXEC complains about some component registered and not working, and also about "BadImageFormatException". It seems that it uses 32-bit version of Oracle library though 64 bit is also installed. I guess that it works better in SQL 2008 (with native x64 DTSWizard), but I have only Express Edition which cannot save the packages. Does anyone have an idea?

Hello Everyone,PLease refer to the following Blog post which has a good summary of 64-BIT issues with Oracle and its possible solutions:http://blogs.msdn.com/debarchan/archive/2009/02/04/good-old-connectivity-issue.aspxThanks,Deb

Following worked for me to get rid of ORA-12154: TNS:could not resolve the connect identifier specified and I was able to connect to oracle from VS 2008 Web Express. -- Steps: 1) Replace all Progra.. (x86) with Progra~2 in Path environment variable 2) Copy Program Files (x86)\Microsoft Visual Studio 9.0 to a new folder ProgramFilesx86 folder and run vwdexpress.exe from the new folder btw, am trying to get a web application to connect to oracle DB using System.Data.OracleClient provider and above approach works just fine. Thanks to all of you who helped me in getting rid of this annoying error.!Please mark solutions as answers (so that everyone knows the thread has been resolved)

HiWe have FULLY SUCCEEDED to connect SSIS with Oracle on 64 bit on both sides with a good transfer rate ( about 10000 records a second )The definition process was not trivial at all.You can email me to yuval@normative.co.il and I will try to assist.best Regards,Yuval

We installed an additional server with SQL Server 2008 in the meantime. On that, everything works just fine without doing any tweaks. Installed SQL server, installed x64 Oracle client. We use the x64 DTSWizard to create packages and the .NET Provider for Oracle. I think the tricky thing in SQL 2005 is that there is only a 32-bit DTSWizard, but SQL Server Agent executes packages in 64-bit mode. That's why we gave up on that and use the SQL 2008 machine.

We just ran into the same problem. Oracle client 10.2.0.3 for Vista and W2k8 (32 bit) seems to have fixed this problem. We're now deploying it rather than 10.2.0.1

I had seen that solution, but I think that the Option to change Execution Run Time Environment to 32 bit exists only in SQL Server 2008.I have SQL Server 2005. In msdn it says:http://msdn.microsoft.com/en-us/library/ms141701(SQL.90).aspxOn a 64-bit computer, the SQL Server Integration Services job step type means that the job step will run the package in 64-bit mode. To have the job run the package in 32-bit mode instead, change the job step type to Operating system (CmdExec), and then have the job step invoke the 32-bit version of the dtexec utility (dtexec.exe).But I didn't make it to do this.

i am trying to import data from excel to oracle. i have server 2003 64 bit, oracle 10G and excel 2007. i got the error when i tried to connect to oracle through excel ORA-12154 while i am able to connect in sql plus and oracle form developer.

"SOLUTION 3: Start the application using the short name version of the directory paths. For example for the DTS Wizard in SQL Server 2005, run it using this command prompt: C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn\dtswizard.exeé this very simple solution that Roger Hernandez gave before in this thread is working !!!! of course the string going to oracle provider is different and without those parenthesis !! simple, working, fast ! thank you so much Roger!!!!!!!!!!!

Hello folks. I just ran into this problem and this thread. I have a Windows 2008 virtual server with MS SQL Server 2005 and the need to create linked servers to other SQL boxes, Oracle farms (of interest to this thread) and MySQL boxes as well. For the Oracle connections, I realized that I was being bit by the Oracle parenthesis bug. The solution that I came up with was, of all things, uninstalling Oracle client 10g and installing Oracle Client 11. No tweaking of any kind was necessary. Once that was done and the .ora files were properly configured, I was able to create linked server objects in my humble 32-bit version of SQL Server in management studio. I hope this works for you as well and that this post saves you the three hours I just spent dealing with this otherwise 10-minute configuration task... :|

I've had similar problem where SSRS 2208 R2 on 64 bit can't see an Oracle 10g database using the 64 bit Oracle client. See post at: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0a01d7e2-e980-40b9-92f2-3e66821071c3 Error showing unable to resolve TNS name, resolved using fully qualified Data Source = <database>.DNS Posted link here just in case anyone following this very long thread is having similar problemsEric

SSIS u rock mate!! U where more than helpful!

This is just a nightmare about SSIS, in Design time every thing works fine and in Runtime it fails. I am using SQL Server 2008 R2 and this stupid error appeared in 2005 (6 years ago)... Holly shit, I am f.... disapointed about this product !BizTalk Consultant in France

moignardr, your solution worked like a charm! Thanks!Vincius Ferreira da Rosa

For Windows 7 , 64 bit edition; the solution is simple; copy this (Toad for Oracle 10.6) from here: C:\Program Files (x86)\Quest Software\Toad for Oracle 10.6 and move it to here or your preferable directory: C:\Program Files\Quest Software\Toad for Oracle 10.6 And then run toad.exe; And it will work.

I have a bit of a heretic solution to this problem... IF, and only IF, the need to connect to an oracle server and query some data from it is very rare and you do not need to create any kind of job to do it repeatedly, it could be easier to simply find any server box on your network that has a working SQL 2000 installation with a working oracle client, and run the DTS Import/Export Wizard from there to get your data out of that Oracle database. I'm suggesting this because as most of these issues are caused by old versions of Oracle databases/clients and confusion between 32/64 bit software, there is a great chance that if you have that Oracle running for some years then you probably have some old SQL2000 machine too. And the DTS from sql2000 is simpler and works better with older clients. It's been a nightmare to install everything client-related to make my windows7+sql2008 workstation talk to Oracle, and I have not yet walked into these 32bit/64bit SSIS issues. Because I have some old servers on my work network and after seeing this topic, I searched for any server with SQL2000+OracleClient to give it a try, and in less than 5 minutes found one and got my data out of that Oracle box.