▼

Tuesday, 17 July 2012

Custom SSIS Component: Foreach Excel Worksheet Enumerator

Recently I did a post on how to loop through all sheets in an Excel file. For those who don't like scripting and for those who think the Foreach ADO.NET Schema Rowset solution is ugly, I created a custom enumerator. Same result, but with an easier interface and a possibility to filter with both wildcards and regular expressions.

Foreach Excel Worksheet Enumerator























Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.

Installation
The installer registers the DLL in the GAC and copies it to the ForEachEnumerators folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

SSIS
The enumerator is automatically added to the Foreach Loop when you open a SSIS project.
The new enumerator

8 comments:

  1. Anonymous23 October 2012 at 22:38

    Do you have an example of this project that you can publish?

    ReplyDelete
    Replies
    1. Joost van Rossum23 October 2012 at 23:39

      Hi Anonymous, What exactly are you looking for? On MSDN you can find how you can create your own custom enumerator: http://msdn.microsoft.com/en-us/library/ms136120(v=sql.110).aspx

      Delete
  2. Anonymous6 May 2013 at 20:52

    Can you please explain how this works to read the all data from excel?

    ReplyDelete
    Replies
    1. Joost van Rossum7 May 2013 at 07:58

      I assume you now how the foreach loop works. This enumerator returns the excel sheetname. You can use that in the excel source or to create a select query with an expression on a string variable and then use that variable as a source.

      But you can only use this if the format of all sheets is the same.

      Delete
  3. JP Voogt22 January 2014 at 11:04

    If I us this component inside of another Foreach Loop with a file enumerator on. The first Excel file get process 100% but the second one always fails. It looks like it is trying to read as sheet that does not existx. Example Woorbook has 24 sheets, the error that gets displayed "opening a rowset for Sheet25 failed"

    ReplyDelete
    Replies
    1. Joost van Rossum23 January 2014 at 22:05

      What you could do is add a script task in the loop. Then add the two string variables from the loops as ReadOnlyVariables and then hit the Edit Script Button. In the main method add the messagebox:
      public void Main()
      {
      // TODO: Add your code here
      MessageBox.Show(Dts.Variables["User::myFilePath"].Value.ToString() + Environment.NewLine + Dts.Variables["User::myExcelSheet"].Value.ToString());
      Dts.TaskResult = (int)ScriptResults.Success;
      }
      This will show which sheet is active and then you can check whether it exists and whether the format is the same as the other sheets.

      Delete
    2. JP Voogt24 January 2014 at 17:40

      All my spreadsheets have different number of sheets in it, but the format of the data is the same. I have noticed that it does not reset the enumerator object. example; spreadsheet = 45 sheets & spreadsheet = 24 sheets. when the first file loop go to spreadsheet 2 the enumerator is still trying to import 45 sheets even though it only have 24 sheets.

      Any advice. Thanks for the fast reply

      Delete
    3. Joost van Rossum24 January 2014 at 19:03

      Hi Jean-Pierre, I can not replicate your problem. Could you contact me via the form. Then we can communicate via email... easier/faster.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.