locked
Problem with variable (Excel tab name) in a Foreach Loop RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello, folks.

    I have to import a number of excel spreadsheets. I'm using the classic Foreach Loop inside another Foreach loop approach. The outside loop (Foreach File Enumerator) cycles through the Excel files, while the inside loop (Foreach ADO.NET Schema Rowset Enumerator - ExcelSchema - Tables) to cycle through the individual Excel sheets in each file.

    Nothing special there; however, for some reason these excel files have some "phantom" tabs that should not be imported. I call them phanton because they show up as an importable tab in a SSSIS import wizard but actually are not listed in the excel file structure (no, they are not hidden tabs, I checked).

    My idea is to use a constraint to NOT import those phanton tabs. The name convention should allow me to do that because the normal tabs have the name 'AAAAAAyyyymmdd$' and the phantom tabs show up as 'AAAAAAyyyymmdd$'_xlnm#_FilterDatabase (the line below was captured from the Local Variable window and show one of the phantom tabs name).

    + User::WorksheetName {'AAAAAAyyyymmdd$'_xlnm#_FilterDatabase} String

    I tried using Len (@[User::WorksheetName]) == 17, which corresponds to the length of the normal tabs name ('AAAAAAyyyymmdd$'). However, it does not work. For some reason the portion of the phanton tab name after the ending single quote (_xlnm#_FilterDatabase)  appears to be ignored.

    I tested with a number of different expressions, including reversing the variable, to no avail. It seems that internally just the standard name between quotes is what the constraint sees.

    Ideas?
    Thursday, October 3, 2013 10:05 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Then pre-process the Excel files removing the phantom sheets using some .net code (office interop) Can be done via a separate (outside the SSIS package) process.

    Arthur My Blog

    Friday, October 4, 2013 3:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The solution you're using also returns "named ranges". Easiest solution is probably to use an expression (checking for $ in the name) between a dummy task and your data flow task.

    Other alternatives are:
    Little Scripting http://microsoft-ssis.blogspot.com/2012/07/foreach-excel-worksheet-enumerator.html
    Custom Enum http://microsoft-ssis.blogspot.com/2012/07/custom-ssis-component-foreach-excel.html

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Mike Yin Thursday, October 10, 2013 9:55 AM
    • Marked as answer by Mike Yin Sunday, October 13, 2013 3:32 PM
    Monday, October 7, 2013 2:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I have an Excel file with multiple identical worksheets (one foreach year) and I want to add the data to a single database table.  I used the forloop container. for the year 2013, is working fine for the 2014 and 2015 data is doubling in data base.

    I put the script task in the to check the input it is taking  _2014xlnm#_filterdatabase. I have attached the screen shoot for reference

    I am using Foreach ADO.NET Schema Rowset Enumerator connection, how can I restrict the duplicate values  

    please advice me , thanks

    Raja

    Monday, June 22, 2015 1:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Thought Solution helps someone. :) 

    To avoid this issue, add a EXECUTE SQL task before the dataflow task which checks to see if the sheet name contains the string "xlnm#_filterdatabase". and further have a constraint check to ignore this sheet. Please follow the steps below.

    1)Create a variable ValidityCheck of integer type.
    2)Add a Execute Sql Task before DataFlow task.
    3)Execute Sql Task editor: 
    ConnectionType=oldb; 
    Connection=Any of your database; 
    SqlSoureType=Direct; 
    SqlStatement ="declare @SheetName  nvarchar(max) select  @SheetName=? select case when @SheetName   like '%xlnm#_filterdatabase' then 1 else 0 end as checkvalue" 
    ParameterMapping = Map the variable @SheetName used in above query to the resultset variable of the Foreach loop container that you used  so that the looping of the sheetname is according to the value coming from the Foreach loop container.
    ResultSet = add the variable created in step1 i.e ValidityCheck with resultname=0.
    4)once you join the execute sql task to dataflow task with Constraint, rightclick on the Constraint->click on Edit then under Constraint option set the following :
    evaluate option=Expression and Constraint; 
    value=success; 
    Expression=@[User::VilidityCheck]==0 
    and click on TestConnection. 
    Then click ok.

    Thanks,

     

    Friday, July 15, 2016 10:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SwethaGRavindra

    is it possible you can provide some screen captures , I am trying to import data from multiple excel files with multiple sheets and I keep running to this issue indicated above where I am getting sheet name that end with "xlnm#_filterdatabase" which is causing errors and issues.

    Thursday, November 3, 2016 2:04 AM