I have a SSIS package that reads the data from an Excel file using an Excel Connection Manager and imports the data to a table on a SQL Server 2005 DB instance.
When I run this package locally on the server the package being on the file system, the package executes perfectly. Now I upload the package to the msdb database and the run the package from there and the package still executes successfully.
Now I schedule the package to run as a SQL Server Agent job and the package fails and when the logging is enabled I see this in the log file;
OnError,WEB-INTSQL,NT AUTHORITY\SYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
OnError,WEB-INTSQL,NT AUTHORITY\SYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1073450985,0x,component "Allocation Comments" (1) failed validation and returned error code 0xC020801C.
I am wondering why the AcquireConnection method call is failing when the package is scheduled I am running the step as a SQL Agent Service Account and it is the Local System account that starts up the SQL Server Agent and Server and is an Administrator on the box.
Any inputs will be much appreciated.
Thanks,
M.Shah
AcquireConnection method call fails when scheduling a job with Excel Connection Manager?
Mark Spicer
Monisha,
Hi I have had the problem with 64 bit before but this is straight 32bit with SP1 and the 5 Hot Fixes. I cannot understand why I cannot connect to the excel files using the scheduler I understand it runs from the command line but I am running everything under the Administrator password and have full control of everything.
Paul
Gushie
Paul,
I have SQL Server 2005 installed on a 64 bit machine so had to use DTExec32 untility and run the job as a command line job instead of an SSIS package job to get around the problem on my end.
Thanks,
Monisha
jlkirkjr
Please have a look at this kb article: http://support.microsoft.com/default.aspx/kb/918760 which may help.
Donald Farmer
Will Merydith
Changing the CreatorName will not change the credentials with which the package was encrypted. I would suggest using the following steps from the link I posted:
let me know if this helps ...
Donald
Scott Currie MSFT
Yes, I did read the article and have the CreatorComputerName as the name of the box where SQL Server 2005 is running and CreatorName as the user who is starting up the SQL Server Agent which is basically the local system account and is a part of the Local Admin Group on which SQL Server 2005 is running.
I can run the job from the msdb database it is only when it is scheduled I get the AcquireConnection method fails error.
Any inputs on resolving this will be much appreciated.
Thanks,
M.Shah
MadDawg2020
Hi I am getting exactly the same problem as detail above. Everything works in the development studio but the connection manager fails when scheduled. I have tried to read through the screens suggested and have in fact used that screen for other problems but I do not understand what I am supposed to be doing with the above posting could someone sched some light on the command syntax and how it will help allow the connection to excel please.
Paul