连接到 ms-access 数据库时通过 SQL 作业运行时出现 SSIS 错误(意外终止) [英] SSIS error when run via SQL job while connecting to ms-access database (Unexpected Termination)
问题描述
我有一个 OLEDB 任务，它连接到本地文件系统上的 ms-access 数据库并将数据转储到 sql 数据库中.
I have a OLEDB task that connects to ms-access database on local file system and dumps data into sql database.
访问数据库连接字符串为:Data Source=\\my-share\accessdbfile.accdb;Provider=Microsoft.ACE.OLEDB.16.0;
Access db connection string is: Data Source=\\my-share\accessdbfile.accdb;Provider=Microsoft.ACE.OLEDB.16.0;
SSIS 包在 Visual Studio 中运行正常.此计算机上安装了 Access 2016 32 位运行时.
The SSIS package runs OK in visual studio. Access 2016 32-bit runtime is installed on this computer.
部署到 SSIS 目录后，右键单击并执行工作确定.此计算机上安装了 Access 2016 64 位运行时.
After deployment to SSIS catalog, right click and execute works OK. Access 2016 64-bit runtime is installed on this computer.
当此程序包计划通过 SQL 代理作业运行时，使用代理/凭据 [域用户 - 示例:域名\用户 1](对访问 db 文件具有读取权限)，则作业将失败.SSIS 执行报告将错误显示为:意外终止.没有进一步的信息.
When this package is scheduled to run via SQL Agent job, using proxy/credential [domain user - example: domainname\user1] (having read permission on the access db file), then the job fails. The SSIS execution report shows error as: Unexpected Termination. There is no further information.
假设如果我将代理凭据(域名\用户 1)添加到本地管理员组(在计算机管理中)，那么 SQL 作业运行正常.有什么办法可以解决这个问题，以便我只能提供所需的最低权限?
Suppose if I add the proxy credential (domainname\user1) to the local administrators group (in computer management) then the SQL Job runs OK. What could be the solution to this so I can give only the minimum required permissions?
推荐答案
该错误很难调试，因为它在通过 Visual Studio 运行时不会发生.即使部署到 SQL 服务器并从 SSIS 目录运行，执行也能正常工作.仅当包通过 SQL 服务器代理作业(通过凭据/代理)运行时才会发生错误 - SSIS 执行报告不显示任何有用的消息；令人惊讶的是，当将凭据添加为计算机上的本地管理员时，错误得到了解决 - 这是一种不好的做法.
The error is very difficult to debug as it doesn't occur when running via visual studio. Even when deployed to the SQL server and run from the SSIS catalog, the execution works OK. The error occurs only when the package is run via SQL server agent job (via credential/proxy) - the SSIS execution report doesn't show any helpful messages; and surprisingly the error gets resolved when the credential is added as local administrator on the computer - which is a bad practice.
解决方案是分析您使用的是哪种 ms 访问文件，并适当地使用正确的访问运行时版本(2013 年与 2016 年)和连接字符串中正确的提供程序值.
The solution is analyse what kind of ms access file you are using and appropriately use the correct access runtime version (2013 vs 2016) and the correct provider value in the connection string.
访问运行时:
就我而言，我卸载了 Access 2016 64 位运行时并安装了 Access 2013 64 位运行时.
In my case, I uninstalled Access 2016 64-bit runtime and installed Access 2013 64-bit runtime.
提供程序版本:
我把provider版本从16.0改成了15.0，如下图-
I changed the provider version from 16.0 to 15.0, as shown below-
之前:Data Source=\\my-share\accessdbfile.accdb;Provider=Microsoft.ACE.OLEDB.16.0;
之后:Data Source=\\my-share\accessdbfile.accdb;Provider=Microsoft.ACE.OLEDB.15.0;
注意:
无需将用户添加到本地管理员组.
无需使用该帐户登录计算机.
There is no need to login to the computer using that account.
假设 SSIS 包仅读取 ms access 数据库，则仅读取权限(在 Windows 文件夹安全性下)就足够了.
Only read permission (under windows folder security) is sufficient assuming the SSIS packge will only read the ms access database.
这篇关于连接到 ms-access 数据库时通过 SQL 作业运行时出现 SSIS 错误(意外终止)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
