Fixing DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER When Working with Excel Files

So I set up a SQL Agent Job to run my SSIS package following these steps.

Here’s the crazy thing. The package would run perfectly when I ran it directly or via a batch file, but when I ran it through the SQL Agent Job it would die and I would see a message like this in the log file I set up:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I tried all of the work-arounds mentioned on this page. However none of them helped.

The answer it turns out is that 64 bit SQL Server Integration Services can’t handle Excel (xls) files! The trick is to use the 32 bit runtime of dtexec. To make the SQL Agent Job do this, go to the execution options tab of the job step and select “use 32 bit runtime”.

Posted by Greg Pinero (Primary Searcher) on Aug 16th, 2010 and is filed under Excel, SQL, win32.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

Comments are closed.