InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:45:13 GMT20http://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxThanks a lot Padraig... Worked for my case as well.Thu, 04 Apr 2013 09:28:36 GMTrderisala1http://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxThanks Padraig, that worked for me too.Fri, 22 Feb 2013 02:37:00 GMTsinghsterhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxThanks a lot Padraig, it working for me:)Fri, 07 Dec 2012 07:14:45 GMTsanthosh_k_gadepallyhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspx[quote][b]Padraig FItzgerald (10/27/2010)[/b][hr]Hi, I had the same problem and found a different solution. Posting it here in case it helps someone else out...Dev Environment BIDS 2008 on Win 7 64 Bit. Source: Oracle 10 DB. Dest :SQL2000 DB x86.I was getting the error mentioned here and tried the various solutions posted.None worked but i found that going to Project--&gt;Properties and setting the 'Run64BitRuntime' to 'false' worked.cheersPadraig[/quote]Thanks , It worked for me too.Sat, 21 Jul 2012 11:52:37 GMTsudhakarahttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxAll great info. I changed the encryption to none, but it made no change.What I did find out is the SQL Agent was running under the Local System and it would not run it. I changed the agent to use my username and it worked just fine.I now need to create a new service account with the proper privileges and it will run just fine.Thanks muchWed, 29 Jun 2011 11:13:50 GMT4x4krzyhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxOk, I am going to sound like a useless msdn moderator here, but just bear with me. A couple questions on the execution: are the packages running with a package protection level like DontSaveSensitive or EncryptWithUserKey? Those settings have their own special personalities, and they may work in debug but totally different in SQL Server as a scheduled job. Now I have learned that the AcquireConnection... error is more generic than I once thought: usually SSIS didn't find the password for that user name (wasn't saved in the Connection Manager). EncryptWithUserKey is a setting that will not allow a package to execute UNLESS it was run by that user (security context of Active Directory account) on the machine it was created. See this explanation: http://msdn.microsoft.com/en-us/library/ms141747.aspx Useless for any kind of multi-server environment.Then there is DontSaveSensitive protection setting: it wants to use a configuration file. That is a major project in SSIS, of course, with little documentation behind it. But try the Package Configuration Organizer which will help make the config file for you. Then you need to deploy it properly (no build errors in Visual Studio) if you want it to work as a scheduled job in SQL Server Agent Service. If you don't deploy correctly the job will appear to execute the SSIS package successfully, but if you look carefully in the job history text, you will notice errors like 'Failed to decrypt protected XML node "DTS:Password"' Anyhow, I mention that because it is common to have the execution results in SQL Server not match the results of debugging in Visual Studio using configuration files, with the error text you mention: 'AcquireConnection ... failed...'Finally, check the version of SSIS, not SQL Server Database Engine, but the Integration Services component, installed once per machine, not instanced like other MSSQL Server products. Make sure Integration Services has the latest service pack that matches the version of SSIS project types you are editing in Visual Studio. If it is more recent, no problem, but if it is older, expect some issues trying to run new package versions on old version of SSIS server.Sorry for the huge ramble. Hope it helps.Wed, 29 Jun 2011 11:03:00 GMTMarie Kellyhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxI read all of this thread and it does not pertain to my situation. Any help would be great!I am getting the same error "The AcquireConnection method call to the connection manager failed with error code 0xC0202009.", here is the situation.-I have a SSIS server that stores all the SSIS packages and runs them. -My production box has the databases and flat files that are to be decrypted and imported to a database.- Both SQL servers are 64 bit SQL 2008 R2 running on Windows 2008 R2From the SSIS server the package works just fine running in Visual Studio. I am logged onto the box as the service account so the package is created by the same account that runs it. The production box also has the SSIS service account on it too.However, when I schedule the SSIS package on the SSIS server to run I get the error "The AcquireConnection method call to the connection manager failed with error code 0xC0202009."If I scheduled it on the production box and it runs fine. The package will not run on the SSIS SQL box at all unless in debug mode in Visual Studio.In the package properties window there is a ForcedExecutionValueType set to int32 and ForcedExecutionValue set to False. This is on both the new and old SSIS servers.I do have the same SSIS server set up already and it works. The only difference is the SSIS server is SQL 2008 not R2I am at a loss of why this is not working. Wed, 29 Jun 2011 09:23:51 GMT4x4krzyhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxInstalling both 32 bit and 64 bit drivers/providers on the server has helped resolve this issue in the past at clients. (BIDS uses 32 bit, but Job Agent uses 64 bit). There are a few articles floating around online regarding this issue.Thu, 28 Oct 2010 14:55:10 GMTtmitchelarhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxHi, I had the same problem and found a different solution. Posting it here in case it helps someone else out...Dev Environment BIDS 2008 on Win 7 64 Bit. Source: Oracle 10 DB. Dest :SQL2000 DB x86.I was getting the error mentioned here and tried the various solutions posted.None worked but i found that going to Project--&gt;Properties and setting the 'Run64BitRuntime' to 'false' worked.cheersPadraigWed, 27 Oct 2010 04:13:27 GMTPadraig FItzgeraldhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxI installed microsoft updates and started getting this error. My fix in SQL Server was in SSIS connection manager under "server name" specify the port number e.g. change "AK11\PROD" to "AK11\PROD,52" if your database uses that port.Also told it to use 32bit (since other posts say that) under Project\Properties\Debugging\Run64bitRunTime to FalseTue, 19 Oct 2010 10:32:30 GMTjosphttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxMany thanks for everyone's contribution to this thread. I finally have got an SSIS package to execute, thanks to the responses above regarding the 32 bit execution and its options at runtime. I also agree: there is a working 64 bit Oracle client for Windows:Oracle Data Access Components for Oracle Client 11.1.0.7.10 Betahttp://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.htmlI did install this on my Windows 7 workstation, a 64-bit host, and have finally connected to the Oracle server, also a 64-bit UNIX server, but as others have noted, after much research and error resolution. The first errors were very vague, complaining about name resolution of the Oracle server. May I suggest also adding a system variable TNS_ADMIN to the [client] operating system? After that, I was at least able to connect to Oracle from the Windows host, of course leading to the package execution issues others on this thread kindly helped to resolve.Here is the detail on the system variable needed after installation of the Oracle client:Add an entry to the System Variables of Environment Variables, in Advanced tab of the System Properties in Windows.VARIABLE = "TNS_ADMIN"VALUE = "C:\app\mkelly\product\11.1.0\client_1\Network\Admin"This path has a folder called "Admin" and that subfolder has the critical connection file "TNSNAMES.ORA", with the connection strings to all Oracle environments.I am pretty sure I needed to reboot the machine before the new variable is recognized by the operating system.Here is a reference on the use of system variables that help locate the Oracle client:http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.websphere.ii.foundation.conn.fw.orc.doc/configuring/iiylsorcenvvars.htmlWed, 11 Aug 2010 16:23:07 GMTMarie Kellyhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxSo your development and deployment environments are both x64 or i64. You're using the command line syntax to access a 32bit version of the Oracle driver because there is no 64bit oracle driver. Actually there is a 64bit oracle driver and we're TRYING to get it working. If successful our SQL job could simply call our package as a package, not as command line, imagine that!Anyhow, I was shown this handy link of testing for availability of 32bit drivers on a 64bit machine.http://blogs.msdn.com/farukcelik/archive/2007/12/31/udl-test-on-a-64-bit-machine.aspxHope this helps,KenMon, 07 Jun 2010 08:47:18 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxI have the same problem but I don't have the 32 bit SQL Server installed and the system admins will likely not agree to installing it. Is there any other workaround to this problem?Mon, 07 Jun 2010 08:10:31 GMTdp2010http://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxA year or 2 ago we though we had this very issue licked. The other night, out of the blue, our Oracle --&gt; SQL Server job stops working with this "Cannot acquire connection from connection manager" error. What worked for us was to set DelayValidation = True for all datapump tasks sourced by Oracle and to redeploy the package. Our SQL environment is Win 2003 Server on an i64 using the Oracle 9.2 client tools to pull from a number of Oracle boxes on 10.2.0.3.0 of the DB.KenFri, 12 Feb 2010 13:45:26 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxWhen using Microsoft’s Visual Studio (aka: Business Intelligence Development Studio) to create an SSIS package that has a connection to Oracle and the SSIS package deployment shows: Error : AcquireConnection call to the connection manager <dbserver>.<db> failed with error code 0xC0202009.How can this be fixed?Solution 1: Make sure the provider being used is installed on both the development and deployment machines. This may require Oracle client to be on the SQL Server machine. To see the providers that SQL Server has available, open MS SQL Server Managaement Studio, then connect to the Database Engine. See Server Objects, Linked Servers then Providers. To see the providers on a development PC with XP, right click and create a text file on the desktop. Rename the text file MyProviders.UDL. Double-click, or right-click Open, the text file and see the “Data Link Properties” form with the tab called providers. Test a connection, then close the form, then open the UDL file (using right-click open with) using Notepad. The connection string will be seen.Solution 2: Use an XML configuration file to change the provider when deployed. To create a configuration file in Visual Studio, open the SSIS solution then right-click on the background of the Designer window and pick the Package Configuration Organizer. Make sure your configuration file is in a folder that SQL Server can see it, and that the file contains the connection string and password parameters. Change the connection string so that it has the correct provider. For Oracle it might be one of these:Data Source=ORACLE1;User ID=ORACLE_USER;Provider=MSDAORA.1;Persist Security Info=True;Data Source=ORACLE1;User ID=ORACLE_USER;Provider= OraOLEDB.Oracle.1;Persist Security Info=False;Fri, 21 Aug 2009 12:52:36 GMTJPaulWrighthttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxI have run error while trying to import from Access Database. The weird part is that the error only occurs when the package is called from a SQL Job. I can run it all da long and have no problem on Server Manager but when a job calls it BAM it throws the error? Any ideas are much appreciated.Thu, 06 Aug 2009 16:05:02 GMTrob stronghttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxhiverify if SQL server agent has the autorization to access of oracle database or create an configuration file and use it with SQL server AgentSat, 05 Jan 2008 11:04:48 GMTwissemhabboubhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxActually, there is a 64 bit Oracle provider available from Oracle (OraOLEDB.Oracle.1, see the [url=http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc#_Toc158373716]Connectivity whitepaper[/url]).When implementing ETL packages at a customer however, we found that no performance benefit from using the 64 bit OraOLEDB over 32 bit MSDAORA, so we stuck to MSDAORA, and scheduled our packages to run with the 32 bit DTEXEC instead of the 64 bit.Once we had all data inside SQL 2005, we ran all other packages on 64 bit however, with considerable performancegains over running 32 bit!Thu, 03 Jan 2008 02:53:14 GMTPeter Rijshttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxAs far as I can remember, there's no 64-bit provider for Oracle. Running the package in a 32-bit environment, as you did, solves the problem.PeterMon, 31 Dec 2007 13:00:05 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxThe resolution to the problem is to set-up the SQL Agent job step as a Operating system (CmdExec) and in the command line use a statement similar to the one below:C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /f "f:\bi\64bit\DUMP\DUMP_DATABASE\DUMP Database.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /DECRYPT xyz123The 'xyz123' after the /DECRYPT clause is the password used when saving any sensistive data in the package e.g. database passwords.there doesn't appear to be any explanation on MSDN as to why you cannot use a Job Step type of: SQL Server Integration Services Packageto execute the package. This only appears to be an issue with SQL Server 2005 64bitMon, 31 Dec 2007 07:53:10 GMTAndrew Lamberthhttp://www.sqlservercentral.com/Forums/Topic435591-147-1.aspxI have created an SSIS package to copy the data from an Oracle database to SQL Server 2005. the package runs sucessfully from VS. When I try yo run the packge from the SQL Agent it starts successfully, but then fails when trying to copy the data from the Oracle database into my SQL Server staging table. The full error I receive is:Date 20/12/2007 12:12:18Log Job History (CODA Database)Step ID 1Server SSINT12\SQLS200564Job Name CODA DatabaseStep Name CODA Database UpdateDuration 00:00:14Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: SSIDEV\SqlServer. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:12:18 Error: 2007-12-20 12:12:32.00 Code: 0xC0202009 Source: OAS_BALANCE Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2007-12-20 12:12:32.01 Code: 0xC020801C Source: Data Flow Task Source - Query [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-12-... The package execution fa... The step failed.I am using the Oracle 10.2.0.3 OLEDB driver for the source connection.I have also saved the package in SQL Server using the Encrypt Sensitive with Password option.Any ideas?Fri, 21 Dec 2007 05:42:28 GMTAndrew Lamberth