I have a SQL Server Agent job with a step to execute an SSIS package. The package has as one of it's connection managers the Connection Manager For Excel Files. When I run the job it fails every time. I have implemented logging and in the OnError event it states "The AcquireConnection method call to the connection manager "LOBMappingExcel" failed with error code 0xC0202009".
Note that if I execute this package via dtexecui or dtexec it runs successfully. Only when I have the SQL server Agent Job run the package does this occur. What am I missing?
- Moved by Sethu SrinivasanMicrosoft employee Thursday, January 24, 2013 9:48 PM SSIS
The account that SQL Agent is running under is a domain account and a member of the sysadmin role. I made sure this account has access to the spreadsheet in question by Terminal Servicing to the box - using these credentials - where the spreadsheet resides and opening it. This was successful.
I also ran dtexec from the command line using the "run as" feature using these credentials and the package executed fine.
It only fails with the "AcquireConnection method" error when I schedule the SSIS package to run via a SQL Agent job.