sql-server - SSIS Foreach 循环 - Excel 表 - 'xlnm#_FilterDatabase'(幻影/幽灵表)

Question

按照 MSSQLTips 博客文章中的说明，我设置了带有 Foreach 循环的包，以循环遍历工作簿中的所有工作表并将数据导入表中。

循环正在查找正确的工作表 ( '11-18$' )，然后是具有更长名称的同一工作表 ( '11-18$'_xlnm#_FilterDatabase )。额外的工作表导致 data duplication和错误消息，例如 "Derived Column.Outputs[Derived Column Output].Columns[SourceSheet]" specifies failure on truncation .

如何使循环排除“xlnm#_FilterDatabase”对象？

为什么循环复制工作表数据？

MSSQLTips 博客文章-- Read Data From Multiple Excel Worksheets SSIS

Foreach 循环设置:

Variable

• Variable is Name:'Sheet Name', Scope:'Import_AXExtractAgristatsInventory' (package name), Date type:'String', Value:'11-18$'

Foreach Loop Editor

• Collection Enumerator is Foreach ADO.NET Schema Rowset Enumerator

• Connection Provider is .Net Providers for OleDb\Microsoft Office 12.0 Access Database Engine OLE DB Provider

• Connection File is a path to \\filepath\file.XLSX

• Connection Advanced Extended Property is Excel 12.0

• Connection Variable Mapping is Variable:='User:SheetName', Index:='2'

• Connection string is Data Source=C:\filepath\file.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;

更新:Foreach ADO.NET Schema Rowset Enumerator和 Set Restrictions限制

我以为 Set Restrictions选项可以使用 !=*_FilterDatabase* 之类的表达式来解决问题(排除幻像表) ，但根据 flylib 在线的电子书摘录，通配符不是一种选择......

The ADO.NET Schema Rowset Enumerator provides a way to enumerate the schema for a data source. For example, you can enumerate the tables in a database, catalogs on a server, or columns for a table. For each schema, you can filter the results in a number of ways. Figure 13.13 shows the Foreach ADO.NET Schema Rowset Enumerator Editor with the Catalogs schema selected.

There is no way to specify a wildcard for restrictions.

Reference: Microsoft SQL Server 2005 Integration Services: Stock Foreach Enumerators

• Figure 13.13. The Foreach ADO.NET Schema Rowset Enumerator provides a way to enumerate database schema.
• Figure 13.14 shows the Tables Schema Restrictions dialog box for the Tables schema.

更新:在循环中调试值

为了调试循环中的工作表对象，我终于找到了一种停止循环并显示工作表名称的方法。我在 Pragmatic Works Blog Post 上找到了一篇文章，它展示了如何使用 Script Task , Break Point , 和 Watch Window显示该事件循环值-- Looping through resultset with foreach loop .



这些是建议的SO问题...

(1) SSIS Foreach Loop Folder as variable , (2) SSIS with Excel sheets , (3) SSIS Looping through Excel Sheets , (4) Foreach Loop Container gives an error after 5 iterations. SSIS

Answer 1

我已经解决了这个要求，我很震惊它原来是相当简单的。我很高兴在这里与您分享。

MSSQLTips blog post 的所有配置仍在使用中我在原始问题中使用 Foreach ADO.NET Schema Rowset Enumerator 引用配置。我添加了一点逻辑......

步骤:跳过工作表名称的逻辑

1. Variable (boolean) - FINDSTRING
   • Setup a variable called varIsInvalidSheet_DisableTask and set expression to FINDSTRING(@[User::varSheetName] ,"FilterDatabase",1 ) > 0
   • FINDSTRING (SSIS Expression): Microsoft Docs/ SSIS/ Expressions/
2. Data Flow Task (Properties> Expression> Disable)
   • We want to stop the Data Flow Task when it is an invalid sheet. To do so...
   • Left Click the Data Flow task, go to the Properties pane, in the Expressions section choose the elipse icon ('...') and choose 'Disable' property and for the expression simply add the variable created in prior step '@[User::varIsInvalidSheet_DisableTask]'
3. Run debugger to verify the settings take effect
   • All sheets are found but the phantom sheets have a Disable TRUE property, and the task is ignored.

以下是这些步骤的一些屏幕截图