来自Variable的动态ConnectionString的SSIS 2012 Excel源代码

I have a simple SSIS 2012 package with:

我有一个简单的SSIS 2012包：

A Script Task obtains the latest .xlsx filename from a folder and stores the full path in a package-scoped variable: SourceFileExcel
脚本任务从文件夹中获取最新的.xlsx文件名，并将完整路径存储在包范围的变量中：SourceFileExcel
An Excel Connection Manager called FUNB to connect to the file
一个名为FUNB的Excel连接管理器，用于连接到该文件
An Excel Source using the FUNB Connection Manager
使用FUNB连接管理器的Excel源

When trying to assign the ConnectionString dynamically, I get error:

尝试动态分配ConnectionString时，我收到错误：

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "FUNB" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Excel源[2]]错误：SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。 AcquireConnection方法调用连接管理器“FUNB”失败，错误代码为0xC0209303。在此之前可能会发布错误消息，其中包含有关AcquireConnection方法调用失败原因的更多信息。

I have tried the following:

我尝试过以下方法：

Add the Connection Manager by browsing to the actual file. Connect the Excel Source to it. Preview Data.
通过浏览到实际文件来添加Connection Manager。将Excel源连接到它。预览数据。
Once file loaded manually, update connection manager properties. Set ConnectionString Expression:

手动加载文件后，更新连接管理器属性。设置ConnectionString表达式：
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@[User::SourceFileExcel]+";Extended Properties=\"EXCEL 8.0;HDR=Yes; IMEX=1\";"
```
Above produces the error. Change the expression to ExcelFilePath set to:

以上产生错误。将表达式更改为ExcelFilePath设置为：
```
@[User::SourceFileExcel]
```

Both the ExcelFilePath and the ConnectionString expressions produce the same error. I have tried both with DelayValidation=True on the Excel Connection Manager and the Data Flow object.

ExcelFilePath和ConnectionString表达式都会产生相同的错误。我在Excel连接管理器和数据流对象上尝试使用DelayValidation = True。

I have another package on the same server that works in much the same way but is using a CSV connection. This package runs normally. The Windows Security Permissions on both source file folders is the same.

我在同一台服务器上有另一个包，它的工作方式大致相同，但使用的是CSV连接。这个包正常运行。两个源文件文件夹上的Windows安全权限是相同的。

Any ideas on what I am doing wrong?

关于我做错的任何想法？

1 个解决方案

#1

I think that you should use Microsoft.ACE.OLEDB.12.0 as Provider when connecting to excel 2007 or higher, Also make sure you are not running the package in 64-bit mode

我认为在连接到excel 2007或更高版本时应该使用Microsoft.ACE.OLEDB.12.0作为提供程序，还要确保没有以64位模式运行程序包

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::SourceFileExcel] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

Or try the following format (without writing Extended Properties)

或者尝试以下格式（不编写扩展属性）

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::SourceFileExcel] + ";Excel 12.0;HDR=YES;IMEX=1"

If none of these suggestions works, then i think that this is not an expression issue, and you are facing the same issue in this link.

如果这些建议都不起作用，那么我认为这不是表达式问题，并且您在此链接中遇到同样的问题。

The problem maybe that a 64-bit office installation was found on the machine and it needed to install 32-bit AccessDatabaseEngine

问题可能是在计算机上找到了64位办公室安装，它需要安装32位AccessDatabaseEngine

#1