来自MS ACCESS 2007数据库的SSIS 2012数据摘录

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.

我创建了SSIS包来从MS访问db中提取数据。它在我的本地计算机上运行良好但在SQL Server代理程序作业中在服务器上运行时失败。

Please help. Thanks in advance.

请帮忙。提前致谢。

Points to note:

注意事项：

1.) Access database has no username and password

1.）Access数据库没有用户名和密码

2.) I disabled 64 bit on the project level

2.）我在项目级别禁用了64位

3.) Used Microsoft Jet 4.0 OLEDB Provider to connect to Access db

3.）使用Microsoft Jet 4.0 OLEDB Provider连接到Access数据库

4.) I set "Delay Validation" to True for the package and the connection both.

4.）我为包和连接都设置了“延迟验证”为True。

5.)PackageProtectionLevel to DontSaveSensitive

5.）PackageProtectionLevel到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.

ft120是我命名的MS Access连接的名称。

TransferData_FT1_FTM is the name of teh package.

TransferData_FT1_FTM是teh包的名称。

1 个解决方案

#1

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/

我在文章中创建了这样的代理并且它有效。 https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/