Ssis Hidden Sheets as Excel Destination

SSIS hidden sheets as Excel Destination

The trick when the GUI doesn't let you do something in SSIS is to tell it you know what you are doing. ;)

In this case, the drop down does not allow you to select values from a hidden tab. The connection manager points to a Excel file with 2 tabs, Sheet1 and Hidden which is hidden.

It does however allow you to select "Table name or view name from variable" or "SQL Command." This solution will use the variable approach but command should work just as well.

As you can see, I have defined a variables called SheetName and assigned it a value of Hidden$

Sample data flow

Results

Progress: 2011-11-07 07:49:32.80
   Source: Excel Destination
   Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  7:49:30 AM
Finished: 7:49:32 AM
Elapsed:  2.121 seconds

SSIS Excel Destination Editor closes unexpectedly

I think the issue lies with the Excel connection instead. Once I changed the output file to .xls instead of .xlsx and changed the connection to Excel 97-2003, I was able to create a new Excel Sheet for the file.

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

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

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.

Here are some screenshots of these steps

---

Related Topics