应该使用哪个Excel连接管理器 [英] Which Excel connection manager should be used

问题描述

您好，

我创建了一个变量来连接到一个excel文件，该文件中的数据在每月一次。 用户删除另一个excel文件时，对excel文件的数据更改会更改，其中部分文件名包含名称中的月份。 
例如，invoice_02.xlsx（02表示二月份）。 在SSIS过程中，我有一个任务，检查文件是否存在excel连接管理器和创建的变量，并检查它是否是当前月份文件。 但是当
我正在测试获得另一个月的文件时，连接中断了，我必须手动重新建立连接。   以下是我在变量区域和连接管理器中的内容。   请告知解决方案。

I have created a variable to connect to an excel file which the data in the file changes on a monthly basis.  The data changes to the excel file changes by the user dropping another excel file with part of filename with the month in the name.  For example , invoice_02.xlsx (02 means for February).  In the SSIS process I have a task that checks if the file exists with an excel connection manager and a variable that is created also with a check if it is the current month file.  But when I am testing to get another month file then the connection is broken and I have to manually re-establish the connection.   Below are what I have in the variables area and in the connection manager.   Please advise on a solution.

变量  =    @ [用户:: vfilePath] + QUOT; \\" + @ [User :: vFilename]

variable  =   @[User::vfilePath]+"\\" + @[User::vFilename]

value =        E：\CP\invoice_02.xlsx

value =        E:\CP\invoice_02.xlsx

我的Excel连接：

excel文件路径：    e：\ CP\invoice_02.xlsx

excel file path:   e:\CP\invoice_02.xlsx

excel版本：MS excel 2007

excel version : MS excel 2007

推荐答案

您好CCCOttawa，

Hi CCCOttawa,

您可以通过利用SSIS脚本任务尝试其他方法。

You can try another approach by leveraging SSIS Script task.

它可以检查特定目录中是否存在Excel文件，如果是，则为连接字符串分配其完全限定路径。

It can check a particular directory for an Excel file presence, and if it is there assign the connection string with its fully qualified path.

Excel连接管理器具有  ExcelFilePath
property。通过使用它，您可以从SSIS变量中分配其值。

Excel Connection Manager has ExcelFilePath property. By using it you can assign its value from the SSIS variable.

请看一下：  动态
为excel连接字符串指定文件名

Please take a look here: Dynamically assign filename to excel connection string

这篇关于应该使用哪个Excel连接管理器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！