将文件名动态分配给 Excel 连接字符串

Dynamically assign filename to excel connection string(将文件名动态分配给 Excel 连接字符串)

问题描述

这是我第一次在 SQL Server 2012 中使用 SSIS.我可以成功读取 excel 文件并将其内容加载到 SQL Server 2012 中的表中.任务是一个简单的直接读取 excel 文件然后复制到 sql server目前没有验证或转换.任务成功.但是当我试图让包从一个变量而不是原始的硬编码变量中读取文件名时，它产生了一个错误DTS_E_OLEDBERROR.发生了一个 OLE DB 错误.错误代码:0x80040E4D"

This is my very first time playing with SSIS in SQL Server 2012. I can successfully read an excel file and load its content to a table in SQL server 2012. The task is a simple direct read excel file then copy to sql server with no validation or transformation for now. The task was successful. But when I tried to make the package read the file name from a variable instead of the original hard coded one, it was generating an error "DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D"

我所做的只是将 excel 连接管理器中的硬编码连接字符串替换为一个表达式，该表达式采用表达式分配的变量的值

What I did was just replacing the hard coded connection string in the excel connection manager with an expression which took the value of a variable assigned by an expression

该变量在数据流任务开始之前被赋值.变量被检查并且确实有正确的值.

The variable was assigned the value before the data flow task started. The variable was checked and did have the correct value.

但是下面的错误是在数据流任务开始时产生的.

But the error below was generated when data flow task started.

如果有人能指出我做错了什么并建议我如何解决问题，我们将不胜感激.

It would be highly appreciated if someone could point out what I did incorrectly and advise me how to solve the issue.

推荐答案

Option A

Excel 连接管理器的 ConnectionString 属性不是我去操作当前文件的地方，这与普通的平面文件连接管理器形成对比.

Option A

The ConnectionString property for an Excel Connection Manager is not where I go to manipulate the current file, which is contrast to an ordinary Flat File Connection Manager.

相反，在 Excel 连接管理器的 ExcelFilePath 属性上放置一个表达式.

Instead, put an expression on the Excel Connection Manager's ExcelFilePath property.

理论上，ConnectionString 和 ExcelFilePath 之间应该没有区别，只是您需要构建更多东西"以使连接字符串恰到好处.

In theory, there should be no difference between ConnectionString and ExcelFilePath except that you will have more "stuff" to build out to get the connection string just right.

另外，请确保您在 32 位模式下执行包.

Also, be sure you're executing the package in 32 bit mode.

您可能遇到的另一种情况是，连接字符串的设计时值一旦运行就无效.当包开始时，它会验证所有预期的资源是否可用，如果不可用，它会快速失败而不是在负载中途死亡.您可以延迟此验证，直到 SSIS 必须实际访问资源为止，您可以通过将 DelayValidation 属性设置为 True 来实现此目的.此属性存在于 SSIS 中的所有内容中，但我会首先在 Excel 连接管理器上设置它.如果仍然引发包验证错误，请尝试将数据流的延迟验证也设置为 true.

An alternative that you might be running into is that the design-time value for the Connection String isn't valid once it's running. When the package begins, it verifies that all of the expected resources are available and if they aren't, it fails fast rather than dieing mid load. You can delay this validation until such time as SSIS has to actually access the resource and you do this by setting the DelayValidation property to True. This property exists on everything in SSIS but I would start with setting it on the Excel Connection Manager first. If that still throws the Package Validation Error, try setting the Data Flow's delay validation to true as well.

这篇关于将文件名动态分配给 Excel 连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！