SSIS中的Foreach excel文件无法正常工作 [英] Foreach excel file in SSIS is not working
问题描述
我有一个SSIS项目，用于处理多个excel文件并将数据加载到SQL Server DB。我有一个excel的连接管理器，并将表达式ExcelFilePath添加到我的变量@User :: File_Path。其中填充了每个循环容器（在变量映射中User :: File_Path设置为0索引）。
这个软件包运行正常，有多个Excel文件，但问题是当文件名被更改或文件夹中有一个新文件（具有相同的结构）时错误。如果excel文件DebitQR Feb 2018.xlsx（在创建excel源连接时使用）存在于该文件夹中，则没有问题。只有当确切的文件名不存在时才会出现问题。
错误显示： -
Hi,
I have an SSIS project for processing multiple excel file and load data to SQL server DB. I have a connection manager for excel and added the expression ExcelFilePath to my variable @User::File_Path. Which is populated from For each loop container (In variable mapping User::File_Path is set to 0 Index).
The package is running fine with multiple Excel files but the problem is when the file name is changed or a new file comes in the folder (with same structure) it gets error. If the excel file Debit "QR Feb 2018.xlsx" (which is used while creating the excel source connection) is present in that folder there is no problem. Only if the exact file name is not present then the problem occurs.
Error shows :-
TITLE：包验证错误
----------------------- -------
包裹验证错误
-------- ----------------------
附加信息：
错误at Copy Data to TempCard [Excel Source [140]]：尚未提供目的地表名。
将数据复制到TempCard时出错[SSIS.Pipeline]： Excel Source验证失败并返回验证状态VS_ISBROKEN。
将数据复制到TempCard时出错[SSIS.Pipeline]：一个或多个组件验证失败。
将数据复制到TempCard时出错：任务验证期间出错。
（Microsoft.DataTransformationServices。 VsIntegration）
------------------------------
按钮：
OK
----------------- -------------
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Copy Data to TempCard [Excel Source [140]]: A destination table name has not been provided.
Error at Copy Data to TempCard [SSIS.Pipeline]: "Excel Source" failed validation and returned validation status "VS_ISBROKEN".
Error at Copy Data to TempCard [SSIS.Pipeline]: One or more component failed validation.
Error at Copy Data to TempCard: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
我的尝试：
我将DelayValidation更改为True。还尝试通过更改连接。
What I have tried:
I changed the DelayValidation to True. Also tried by changing the connection.
推荐答案
听起来您的连接已设置为仅连接到该电子表格。您可以按照此处的步骤参数化您的连接... 使用SSIS环境变量在运行时参数化连接和值 [ ^ ]
It sounds like your connection has been set up to only connect to that spreadsheet. You can parameterize your connection following the steps here ... Parameterizing connections and values at runtime using SSIS environment variables[^]
这不是解决方案。甚至没有场景。问题在于foreach循环容器的延迟验证。必须为变量excel文件设置为true。我已经更改了我的excel源连接的延迟验证，但没有更改到foreach循环容器。
This was not the solution. Not even the scenario. The problem was with the delay validation of the foreach loop container. Must be set true for variable excel file.I had changed the delay validation of my excel source connection but not to the foreach loop container.
这篇关于SSIS中的Foreach excel文件无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
