SSIS 2012 data extract from MS ACCESS 2007 database

1

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.

sql
ssis
ms-access-2007
connection-string
asked on Stack Overflow Jan 22, 2016 by user1529514 • edited Jun 28, 2017 by Ardalan Shahgholi

1 Answer

0

User contributions licensed under CC BY-SA 3.0