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

A teammate of mine might have found the problem and "solution" (it really is a messy workaround right now). Stupid parantheis :-)Here are some excerpts from a support document Subject: ORA-12154 or ORA-6413 Running 32-bit Oracle Software on 64-bit Windows OS Applies to: Oracle Net Services - Version: 8.1.7.0 to 10.2.0.1Oracle Data Provider for .NET - Version: 8.1.7.0 to 10.2.0.1Oracle Objects for OLE - Version: 8.1.7.0 to 10.2.0.1Oracle Provider for OLE DB - Version: 8.1.7.0 to 10.2.0.1Oracle ODBC Driver - Version: 8.1.7.0 to 10.2.0.1Microsoft Windows XP (64-bit Itanium)Microsoft Windows Server 2003 (64-bit Itanium)Microsoft Windows XP (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T) Symptoms You are attempting to connect to the Oracle database from a Windows platform using one of the following programmatic interfaces ODBC OLEDB OO4O ODP.NET after installing 32-bit Oracle client software on a 64-bit Windows operating system (OS) you receive one of the following errors: ORA-12154: TNS:could not resolve the connect identifier specified or ORA-6413: Connection not open. Cause 64-bit Microsoft OS's install 32-bit applications into the following location "C:\Program Files (x86)\..." rather than the typical location of "C:\Program Files\..." This causes an existing networking bug to occur where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle. The following bug has been filed to correct this behavior: Bug 3807408 CANNOT EXTERNALLY AUTHENTICATE USER WITH QUOTE IN USERNAME Additional Information-----------------------The reason you receive an ORA-12154 vs. an ORA-6413 is generally due to which programmatic interface you have chosen to use to connect to Oracle. The ORA-12154 is the typical error seen when connecting with up-to-date interfaces using the latest version of the Oracle Call Interface (OCI): Oracle ODBC Driver Oracle Provider for OLE DB Oracle Objects for OLE Oracle Data Provider for .NET (ODP.NET) Microsoft's .NET Managed Provider for Oracle The ORA-6413 is typical of using older interfaces which make legacy API calls such as Oracle's OCI Version 7 API: Microsoft ODBC Driver for Oracle Microsoft OLE DB Provider for Oracle Solution To resolve this problem try either of the following solutions: SOLUTION 1: Use a version of the Oracle client AND database software that contains the fix for Bug 3807408. This fix requires that both the client and database software be patched. NOTE: Currently this bug has not been resolved. See SOLUTION 2 for now. SOLUTION 2: Find the location of the application that is generating the error. Check the path to this location and see if it contains any parenthesis. If so, you must relocate the application to a directory without any parenthesis in the path.

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

I tried this on a machine to start Visual Studio and it still gave the same error I actually created a shortcut on the desktop and then used it. Temporarily, I execute the DOS format name from the Run command and it connects to Oracle. Do you guys know if the Oracle bug has been fixed? I can get my client to patch it in. I assume the bug is in the Oracle 9.2 client. Thanks

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 All, For using Oracle in 64 bits environment with Microsoft SQL Server 2005, you have to solve two technical issues. 1. Oracle driver can't work with program installed path with "parenthesis directory". This is typical (x86) directory in 64 bits environment. And any program stored in the (x86) can't connect to Oracle database, even you update the oracle 10g client. As from my experience, some software you installed (x86) can be move to another directory simply, but some can't. For this issue, Microsoft SQL Server 2005 64 bits are combine 32/64 bits application, and some files will be installed in (x86) directory. In conclusion, the parenthesis is Oracle reserved word, any application installed path with that word will lead oracle driver failed to work. 2. 32 bits dll can't be load into 64 bits process. So far, there isn't 64 bits MDAC, that what I mean you have limited ODBC drivers available in 64 bits environment. Those 32 bits ODBC driver can't be shared in 64 bits. The default ODBC administrator in Adminstrative tools (Control Panel) is 64 bits, you can open it and see how limited driver 64 bits can supported. And after that, you can launch Windows\SysWow64\odbcad.exe to see how many ODBC driver supported in 32 bits. You will find the gap between 64 bits and 32 bits, it seems Microsoft didn't complete the driver migration at the time of Windows 64 bits released. From my experience of Microsoft SQL Server 2005 64 bits. Here is the finding and possible solution for you. 1. I've successful fix the Oracle problem and any program stored in (x86) can access the oracle driver well. DTS and SSIS can both work very well without changing any directory naming (x86). This method is not certified by Oracle and I already deploy this to my production environment. I'm wondering if I can put this method on the web someday, if you need this solution you can contact with me. I think you need Oracle driver might be SSIS or DTS package, simply copy DTSRun.exeto C:\SQL\DTS\ and DTExec.exe to C:\SQL\SSIS, mapping all CmdExec to C:\SQL\DTS\DTSRun.exe would be better. (You have to copy entired directory which DTSRun.exe stored to C:\SQL\DTS not just one exe file; this is same for DTExec.exe) 2. Install both 64 bits and 32 bits Oracle 10g client and create same System DSN name in 32/64 bits ODBC administrator. All SSIS and DTS job refer to OLE DB for ODBC and specify the System DSN name. This is because SSIS editor (VS.Net 2005) is 32 bits program, only 32 bits driver listed in the SSIS editor. But the SSIS runner might be 32 or 64 bits, it is better to create both same nameSystem DSN that mapping to 32/64 bits Oracle driver, so that no matter 32 or 64 bits SSIS runner start to execute, it can mapping to correct Oracle driver. (64/32 bits oracle ODBC driver name is different)

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?

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?

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..Marcellthks for your help But have you ever try to run ssis package with a sql agent job???I think when ssis package run at sql agent , it could be at 64bit modeAnd is it still work???? Tim

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 think I found the way to do it. I've written a blog post about it here: http://sqlblog.com/blogs/jorg_klein/archive/2009/10/15/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

Hi!!I had the same problem. Unfortunately I just saw your suggestion to install 11g 32-bit and 64-bit clients, instead of 10g, Jorg.I will try it. Nevertheless, I have installed the 10g 32-bit and 10g 64-bit clients and made it work. So I thought of sharing my solution with you.The problem is that with the Program Files (x86) problem.I wanted to create a package using the "Import and Export Wizard" of the Sql Server Management Studio, that copies a table from Oracle to Sql Server. When I opened the wizard from the Sql server management studio I couldn't connect to Oracle: Errors: Test connection failed because of an error initializing the provider. ORA-12154: TNS could not resolve service nameor DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009.Instead I opened the wizard manually (using the Progra~2 path):C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn\dtswizard.exethe connection then worked and the package was successfully created. (The package could also be correctly created by the Visual Studio when opening it using the path: "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" )In order to schedule the created package, I couldn't use the Sql Server Management studio (because it was giving me the Oracle error), so I created a bat file to execute the package, using the DTExec.exe which is in the Program Files (and not in the Program Files (x86).----- bat file ----------cd C:\Program Files\Microsoft SQL Server\90\DTS\Binn\start DTExec.exe /FILE "C:\mypackage.dtsx" /DECRYPT password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E---------------------------Then I used the windows scheduler to run the batch file.I will now try installing the 11g clients, to see if that works.Good luck to everyone!

So I had this problem, and I have reviewed all posts in this section in order to try to fix it. None of them worked. The solution became obvious to me however when I noticed the Run64BitRunTime Option in the configuration settings in the property page. Since many hours were spent by me finding this, I figured I might share the solution with all you fine gentlemen. Also since MSDN does not allow picture uploads( I dont know why?) I had to upload the picture elsewhere and link it over here. Error Resolution: 1) Change Run64BitRunTime To False in the Property Page of your Solution as here: http://1.bp.blogspot.com/_b8eeoM4oXw0/S4gy_Y-dHkI/AAAAAAAAAVM/xcwo3RzNNxk/s1600-h/64Bit_debug.JPG 2) If you are scheduling the SSIS package, then also change Execution Run Time Environment to 32 bit as here: http://1.bp.blogspot.com/_b8eeoM4oXw0/S4gy_G-Ku8I/AAAAAAAAAVE/QOYgeSFkHIQ/s1600-h/64bit_schedule.JPG Good luck Kanhar Munshi http://www.kanharmunshi.com/ kanhar<at>gmail.com

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!!!!!!!!!!!

Blog post about it help me: http://sqlblog.com/blogs/jorg_klein/archive/2009/10/15/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

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... :|

Hi All, I have the same problem ( Windows 2003 enterprise sp2 x64 , oracle client 10.2, 11.2 , sql server 2008 ). THE SOLUTION FOR ME WAS : - add aditional entries in environment variable(variable: path) for every application you want to us with oracle client: for example : For Microsoft visual studio : C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\ I hope this works for you too.Vt - MCTS

oops - clicked propose answer above by mistake I resolved the problem of connecting to Oracle 10g XE on Windows 7 x64 by creating a symlink to Program Files (x86) and then manually changing the path of the visual studio shortcut to the symlink C:\Windows\system32>mklink /D "C:\Program Files x86" "C:\Program Files (x86)"

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

Another solution is to navigate to the DTEXEC.exe file and place it in the same folder your SSIS package is running For example your bat file located in D:\Jobs has the command: "c:\program files (x86)\...." /File "D:\Jobs\test.dtsx" /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING EWCDI Move the DTEXEC file into the D:\Jobs directory change your bat file to: DTEXEC /File "D:\Jobs\test.dtsx" /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING EWCDI The SSIS package will run. Just remember to go into SYSWOW64 and configure your 32-bit Oracle drivers and you tnsnames.ora file is in the proper location. Hope this helps

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