SSIS Foreach Loop - Excel Sheets - 'xlnm#_FilterDatabase' (phantom / ghost sheet)

Question

Following the instructions on a MSSQLTips blog post, I have setup package with a Foreach Loop to loop through all sheets in a workbook and import the data into a table.

The loop is finding the correct sheet ('11-18$'), and then the same sheet with a longer name ('11-18$'_xlnm#_FilterDatabase). The extra sheet is causing data duplication and error messages such as "Derived Column.Outputs[Derived Column Output].Columns[SourceSheet]" specifies failure on truncation.

How to make the loop exclude the 'xlnm#_FilterDatabase' object?
Why is the loop duplicating the sheet data?

MSSQLTips blog post-- Read Data From Multiple Excel Worksheets SSIS

Foreach Loop setup:

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;

Update: Foreach ADO.NET Schema Rowset Enumerator and Set Restrictions limitation

I thought the Set Restrictions option would do the trick (to exclude the phantom sheets) with an expression such as !=*_FilterDatabase*, but according to an ebook excerpt online at flylib, wildcards are not an option ...

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.

Update: Debugging values in loop

In an effort to debug the sheet objects in the loop, I finally found a way to stop the loop and display the sheet name. I found an article on Pragmatic Works Blog Post which shows how to use Script Task, Break Point, and Watch Window to display that active loop value-- Looping through resultset with foreach loop.

These are suggested SO questions...

(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

I found an SSIS solution to this problem on a [Blog by Todd Chitt](https://toddchitt.wordpress.com/2009/08/21/multitab/), but his solution means I would have to re-work my package from `Foreach Loop > DataFlow` to a more complicated process `DataFlow > [Script Task] and [RecordSet destination] > Foreach Loop`. Not sure if that is the change I am looking for. — SherlockSpreadsheets, Oct 10 '18 at 16:48
Same exact question was asked on [SQLServerCenter Forums](https://www.sqlservercentral.com/Forums/1780577/SSIS--ForEach-Loop-Container-Excel-load), but i don't see an answer was given. — SherlockSpreadsheets, Oct 10 '18 at 17:02
Some other options to consider: [Option A -- extra dummy task in your foreach loop with an expression on the precedence constraint to the next task](http://microsoft-ssis.blogspot.com/2012/07/foreach-excel-worksheet-enumerator.html?_sm_au_=iVVq2MjqVNWPTfHF), and [Option B -- Custom SSIS Component: Foreach Excel Worksheet Enumerator](http://microsoft-ssis.blogspot.com/2012/07/custom-ssis-component-foreach-excel.html?_sm_au_=iVVq2MjqVNWPTfHF) — SherlockSpreadsheets, Oct 10 '18 at 22:06
I've done some more testing and found the weirdest thing... When I open the source excel file on my desktop and then run the package, the "FilterDatatbase" phantom sheets are no longer found in the loop. (・_・ヾ ? This is just an interesting note. Still researching the best solution. — SherlockSpreadsheets, Oct 11 '18 at 14:10

score 3 · Accepted Answer · answered Oct 11 '18 at 16:45

I have solved the requirement, and I am shocked that it turns out to be fairly simple. I am happy to share it with you here.

All configurations are still in use from the MSSQLTips blog post that I reference in my original question using the the Foreach ADO.NET Schema Rowset Enumerator configurations. I added one small bit of logic...

STEPS: Logic to Skip Sheet Names

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/

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]'

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.

Here are some screenshots of these steps

SSIS Foreach Loop - Excel Sheets - 'xlnm#_FilterDatabase' (phantom / ghost sheet)

Foreach Loop setup:

1 Answers1