Why can’t my package acquire a connection from its connection manager?
I work as a Support Escalation Engineer in CTS at Microsoft, specializing in the SQL Server Developer area. Within this area, I do a lot of work with SQL Server Integration Services (SSIS). This post is part one in a series pertaining to connection manager related problems in SSIS. One of the topics we get calls on the most for SSIS involves the error message:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
The AcquireConnection method call to the connection manager "__" failed with error code __. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
One of the most common reasons for this error is the lack of a 64 bit driver for the provider used by the connection manager mentioned in the error. The developer of SSIS packages/solutions should consider the environment to which their solution will ultimately be deployed long before the deployment stage. Whether or not the production environment is 64 bit or 32 bit is extremely important for several reasons – one being the availability of drivers in both environments.
There is common confusion around the drivers used by the Business Intelligence Development Studio (BIDS) environment verses the runtime environment.
BIDS
BIDS uses 32 bit drivers at design time when testing connectivity, previewing data, etc. When running/debugging a package in BIDS, the drivers used are determined by the Run64BitRuntime project property and whether or not the machine on which BIDS is running is 64 bit or 32 bit. If the development machine is 32 bit, 32 bit drivers will be used at design time and while running/debugging in BIDS. If the development machine is 64 bit, 32 bit drivers are used at design time and 64 bit drivers are used while running/debugging in BIDS by default, since the Run64BitRuntime property defaults to True. That is why it is possible to test a connection in BIDS and see it succeed, only to see the AcquireConnection method call failed error when running the package in BIDS. In that scenario, the 32 bit drivers are installed and working; but the 64 bit drivers are not.
Whether to change the default Run64BitRuntime setting depends on how you would like debugging to simulate the production environment. If ultimately the package will be deployed to a 64 bit environment and 64 bit drivers are available for the chosen providers, leave the default setting of True on 64 bit development machines and ensure that both 64 bit and 32 bit drivers are installed. Otherwise, change it to False. Keep in mind, however, that this setting, Run64BitRuntime, does not stay with the package once it is deployed. It is a BIDS project property which only impacts how the package is executed in BIDS.
Here is a quick demo of the Run64BitRuntime setting and its impact in a 64 bit development environment when using an Ole Db connection manager connecting to an Excel file:
SQL Server Agent
By default, the SQL Server Integration Services Package job step type in SQL Server Agent will run a package using 64 bit Dtexec.exe on a 64 bit server. Therefore, the same AcquireConnection method call failed error will occur when the job step runs if no 64 bit driver is correctly installed for the providers used by the connection managers in the package.
In SQL Server 2008 and later, a check-box is available to specify “Use 32 bit runtime” when the job step runs. This check-box is on the Execution Options tab in the New Job Step dialog:
Another option to use, especially in SQL Server 2005, is the Operating System (CmdExec) SQL Server Agent job step type, specifying the path to 32 bit Dtexec.exe in the command line:
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe /FILE "C:\SSIS\Package.dtsx" /CHECKPOINTING OFF /REPORTING E
The 64 bit/32 bit provider mismatch is one of the most common reasons for receiving the AcquireConnection method call failed error. I will be explaining and, hopefully, clarifying other scenarios for that error in future posts.