locked
List of tables (Sources/Targets) in a SSIS Package RRS feed

  • Question

  • Hi Experts-

     

    Is there any way to list the tables (Source and Target) involved in a SSIS package without digging into the code...?

    Since my package is huge and took almost 4-6 hrs to do the required analysis to the list of tables requesting to provide your valuable suggestions...!

    Thanks In advance.


    -balas

    Friday, November 2, 2012 1:09 PM

Answers

All replies

  • This is pretty hard, as tables can be listed at various places. Take the source for example:

    * you can select it through the dropdownlist. This will be fairly straightforward to find in the XML.
    * you can write a query. You'd have to parse the TSQL to find all the tables referenced.
    * the table name can be in a variable. You'd need to track down the value of that variable.
    * the TSQL can be in a variable. You'd need to track down the value of that variable and parse it to find the tables referenced, taking into account not only the SQL dialect, but also the SSIS expression language.


    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Marked as answer by Balas777 Saturday, November 3, 2012 9:19 PM
    • Unmarked as answer by Balas777 Saturday, November 3, 2012 9:19 PM
    Friday, November 2, 2012 1:56 PM
  • There is no simple way of doing it.

    There is a workaround to open the package and examine it using Notepad. Since SSIS packages are basically XML Files.

    Update:

    This may help you http://social.msdn.microsoft.com/forums/en-us/sqlintegrationservices/thread/A011317E-D9C6-455A-9758-348951D8FB6F

    • Edited by irusul Friday, November 2, 2012 1:57 PM
    • Marked as answer by Eileen Zhao Monday, November 19, 2012 2:18 AM
    Friday, November 2, 2012 1:57 PM
  • Could you please provide little more information regarding the workaround using Notepad...?

    Open the package with notepad and search for the key words like "From", "Insert"  etc. Is this the workaround you are suggesting..?

    -Please suggest me your views.


    -balas

    Saturday, November 3, 2012 9:20 PM
  • Koen is right. There is no easy way to do this. You don't need Notepad to examine the XML of a package, just right-click on the package in BIDS and click "View Code."
    Sunday, November 4, 2012 6:36 PM