Post reply

Accessing excel sheet name dynamically in ssis package

  • October 30, 2008 at 3:19 am

    #214647

    Currently my package contains an Excel Source File and am trying to load the data in to a table. The Excel Connection Manager currently has an expression attached to it which allows it to read the variable value and this in turn loads the correct file. This all works fine.

    The problem I am having is that if the Worksheet name is different to what was previously set a error is thrown.

    Is there anyway in SSIS I can retrieve the name of the excel worksheet and pass it into a variable to be used.

  • October 30, 2008 at 9:20 am

    #891893

    Yes, it is possible. However you have to write a script to retrieve the list of worksheets and based on your requirements store in a package variable. What you have to do is open ADO connection to the Excel workbook and then use the OpenSchema method to retrieve the list of sheets in it. You can check the following article with overview and sample VB code. You have to find out how you can accomplish similar functionality with VB.NET or use standard ADO (don't confuse it with ADO.NET) in VB.NET.

    Good luck!

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply