Thursday, October 18, 2007

Cannot acquire connection to Excel when executing SSIS package via SQL Agent Job

My current work involves building ETL solution using Microsoft SQL Server SSIS. All has been working well in development until we moved the SSIS packages into production environment (don’t that always happen?).

Anyway, our SSIS packages that has Excel file as data source hits the following problem:

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009

Eventually, it seems that the issue is due to account privilege. Our packages are executed via SQL Agent Job, in which the job step is configured to Run as a one of the designated AD account, that was setup as the proxy for the job. According to this Microsoft kb article , the particular AD account does not have rights to the Temp folder of SQL Server Agent proxy account. It would be logical to assume that this temp folder is used as a working folder by the SQL Agent job if required, such as putting the Excel file there for execution perhaps?

We solve this issue by using the default Run as account, which is SQL Agent Service Account. This Microsoft KB article has the detail:

No comments: