question

eolivera avatar image
eolivera 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
ssissql server 2014microsoft-accesspackage
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
aRookieBIdev avatar image
aRookieBIdev answered
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/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
you are welcome.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.