I created and SSIS package to extract data from MS access db. It runs fine on my local machine but fails when I run it on the server in SQL server agent job. Please help. Thanks in advance. Points to note: 1.) Access database has no username and password 2.) I disabled 64 bit on the project level 3.) Used Microsoft Jet 4.0 OLEDB Provider to connect to Access db 4.) I set "Delay Validation" to True for the package and the connection both. 5.)PackageProtectionLevel to DontSaveSensitive It fails with error below: Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 6:28:42 PM Error: 2016-01-21 18:28:43.34 Code: 0xC0202009 Source: TransferData_FT1_FTM Connection manager "ft120" 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: "Disk or network error.". End Error Error: 2016-01-21 18:28:43.34 Code: 0xC020801C Source: Data Flow Direct OLE DB Source [33] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ft120" 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-01-21 18:28:43.34 Code: 0xC0047017 Source: Data Flow Direct SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2016-01-21 18:28:43.34 Code: 0xC004700C Source: Data Flow Direct SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-01-21 18:28:43.34 Code: 0xC0024107 Source: Data Flow Direct Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:28:42 PM Finished: 6:28:43 PM Elapsed: 0.844 seconds. The package execution failed. The step failed. ft120 is the name of the MS Access connection I named. TransferData_FT1_FTM is the name of teh package.
I created a proxy like this in the article and it worked. https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)