My package is using .xlsx file as input source through excel connection manager.

The package is running fine in BIDS 2008 R2 and in SSIS Server instance (where the package is deployed, the package can be run successfully from the option run package).

But the same package is not running using the SQL Agent job.

Error:

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

I found for files that have the Excel 2007 or later format, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. https://msdn.microsoft.com/en-us/library/cc280527(v=sql.105)

Is the installation of the provider is mandatory for this scenario?

Why it is failing only through agent job only?

Thanks

• Edited by Pinwar15 Monday, June 29, 2015 8:41 PM

I have the proxy account configured with all the necessary access and some other jobs are running with same proxyID with accessing .xls file from the same location.

However, I checked the SQL agent services and it is running through NETWORK SERVICE id. Added this Network Service to DCOM config (in msdtsserver100).

The job ran for now, I am not sure it will run through the schedule.

Why the job ran after doing the above change?

"However, I checked the SQL agent services and it is running through NETWORK SERVICE id. Added this Network Service to DCOM config (in msdtsserver100)." I do not see that being necessary.

Hi Pinwar15,

If each time the SQL job tries to execute, the following similar error occurs in the Windows System Event log:
The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID {46063B1E-BE4A-4014-8755-5B377CD462FC} and APPID {FAAFC69C-F4ED-4CCA-8849-7B882279EDBE} to the user "SQL AGENT Service account "SID (S-1-5-21-) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

Then the issue should be caused by the SQL Server Agent service lacks the Local Activation permission for the MsDtsServer100 DCOM Application. So you can fix this issue by adding SQL Server Agent service account (Network Service) to DCOM config. For more details, please see:
http://www2.schneider-electric.com/sites/corporate/en/support/faq/faq_main.page?page=content&country=APS_GLOBAL&lang=en&locale=en_US&id=FA240854&redirect=true

Otherwise, as ArthurZ said, the process may not necessary.

Thanks,
Katherine Xiong