Click here to Skip to main content
15,881,204 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#
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.
Posted

looks like a file system permission issue
 
Share this answer
 
C#
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/
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900