我有一个运行脚本任务的SSIS包(主要是等等).该脚本任务使用OleDB连接连接到Access数据库.这是Microsoft Jet 4.0连接.我安装了驱动程序但它不会通过代理帐户在sql代理中运行.它将直接从Visual Studio和包存储运行良好.事实上,当我作为代理绑定的特殊帐户登录时,这两个地方都可以正常运行.但是当我通过sql Server代理运行时,我得到了可怕的“未指定的错误”OleDbException.

脚本任务的相关代码：

// class field
private string accessConnectionStringTemplate = "Data Source=\"{0}\";Provider=Microsoft.Jet.OLEDB.4.0;";

// in method that connects to database
Print(file,"Connection string: " + string.Format(accessConnectionStringTemplate,file.FileName));
// outputs: Data Source = "\Path\To\File";Provider=Microsoft.Jet.OLEDB.4.0"
using(access = new OleDbConnection(string.Format(accessConnectionStringTemplate,file.FileName))) {
     access.Open();
     // other code
}

通过sql代理作业历史记录的错误消息：

Started:  12:35:10 PM
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description: Exception: Unspecified error
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description:    at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr,OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options,DbConnectionPoolKey poolKey,Object poolGroupProviderInfo,DbConnectionPool pool,DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options,DbConnection owningConnection,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection,DbConnectionPoolGroup poolGroup,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,TaskCompletionSource`1 retry,DbConnectionOptions userOptions,DbConnectionInternal oldConnection,DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,DbConnectionFactory connectionFactory,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.ImportFile(AccessFile file,DateTime startRecordDate,DateTime endRecordDate,List`1 accessTables,Boolean includeTransactionTables,List`1 specifiedTableList)
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.Main()
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000006
   Source: Import Files 
   Description: The script returned a failure result.
End Error

我确定的一些事情：

>安装Access驱动程序,并在sql Agent所在的服务器上工作.我通过在VS中作为我的帐户和代理帐户运行包来验证这一点,没有问题.
>代理帐户可以访问该文件.再次通过以代理帐户的身份登录到服务器进行验证.文件位于网络共享上,但路径被指定为UNC路径.
>代理帐户可以访问作为此操作的一部分的其他数据库,以排除任何其他潜在的错误来源.
>通过我的帐户和代理帐户的工作,从包存储(通过SSMS)运行包.我在数据库服务器上做到了这一点.

在互联网上看到的其他问题,驱动程序通常是一个问题.在这种情况下,我不知道如何.

我很乐意提供其他信息来帮助其他诊断.我自己完全不确定为什么这不工作.

var tempPath = Path.GetTempPath().Replace("\\sqlSERVERAGENT\\","\\" + Environment.UserName + "\\");
Environment.SetEnvironmentVariable("TEMP",tempPath);
Environment.SetEnvironmentVariable("TMP",tempPath);