动态将文件名分配给excel连接字符串 [英] Dynamically assign filename to excel connection string

问题描述

这是我第一次在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.

推荐答案

选项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 Connection Manager的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 Connection Manager中进行设置.如果仍然抛出Package Validation Error，请尝试将Data Flow的延迟验证也设置为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连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！