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 characters needed characters left characters exceeded

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

1 Answer

· Write an 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 characters needed characters left characters exceeded

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.