asked
SQL Server SE 2014 SSIS works on Data Tools but fails on Agent Job
I have a job, in reality it's two jobs but both have the same symptons, that work when run on Data Tools but fail when run from SQL Server 2014 Standard Edition's SQL Agent. I have found similar errors on the web but not quite because mine has to do with an Access database. Has anyone experienced this type of problem and found a solution to it? The package, which resides in the file system, would need to run twice an hour so it has to be scheduled. Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 6:10:02 AM Error: 2016-09-30 06:10:02.83 Code: 0xC0202009 Source: Package Connection manager "D:\Prod_reports_xls\PB\HAR_Reports\HAR_MPS_Database.mdb" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file 'D:\Prod_reports_xls\PB\HAR_Reports\HAR_MPS_Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". End Error Error: 2016-09-30 06:10:02.84 Code: 0xC020801C Source: Reload HAR_MPS Mode Mgmnt OLE DB Source [53] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "D:\Prod_reports_xls\PB\HAR_Reports\HAR_MPS_Database.mdb" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2016-09-30 06:10:02.84 Code: 0xC0047017 Source: Reload HAR_MPS Mode Mgmnt SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2016-09-30 06:10:02.84 Code: 0xC004700C Source: Reload HAR_MPS Mode Mgmnt SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-09-30 06:10:02.84 Code: 0xC0024107 Source: Reload HAR_MPS Mode Mgmnt Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:10:02 AM Finished: 6:10:02 AM Elapsed: 0.672 seconds. The package execution failed. The step failed. Thank you
This is an access issue. When the package runs from the sql agent job it runs through the sql agent service account. Create a proxy that has appropriate access to the directory and run the SSIS Package through proxy in SQL Agent Job.
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Thanks, that did it. I had an error afterwards but it was only because I had forgotten to add the new proxy account to SQL Server.
18 People are following this question.
