Search code examples
sqlexcelssisssis-2012ssis-2008

$xlnm excel sheets

I have bunch of sheets in excel file. when I am looping the sheets its picking $xlnm #_filterdatabase as excel source.

How can I ignore loading $xlnm #_filterdatabase sheets?.

Solution

  • You cannot ignore these sheets, since they exists but they are hidden. Temporary sheets names always endswith an underscore, you can use a similar logic to filter them (using Expression Task or Script task) :

    if (!strSheetname.EndsWith("_") & & !strSheetname.EndsWith("_filterdatabase") && strSheetname.EndsWith("$")){}

    Note that a valid sheet name must ends with a dollar sign $

    If you decide to use an Expression task you must use Findstring() function.

    You can check the following answer, to learn how to implement a filter inside a foreach loop container: