locked
List of SSIS Packages that tables are used in,. RRS feed

  • Question

  • I am looking for a way to find out which tables I am using in SSIS packages. If I make a change to a table I need to know which packages I may need to examine. I have looked at the stored procedures but do not see anything that jumps out at me. Is there a procedure that will list the tables used in a package?
    Wednesday, September 9, 2009 12:19 PM

Answers

  • Phil

    I am confused then by your statement "If you deploy to MSDB, this information is already available as you described. "?


    I should be able to read throught the xml as described above and will see what I come up with.

    I was referring to the post by Jagapathi where it was recommended to create a table to store the package XML information.  I wanted to point out that if you deploy to MSDB, there is already a table that contains the package XML - you don't have to create anything.

    The trouble for you will be understanding how to interpret the XML and then to be able to make decisions based on what you find so that you can generate a list of tables used in your package(s).  I don't think this is an easy task.
    • Marked as answer by MPCJoe1 Wednesday, September 9, 2009 4:21 PM
    Wednesday, September 9, 2009 3:49 PM

All replies

  • Simply NO. :) .... There is no short cut.
    you need to open your package and look for the tables.


    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, September 9, 2009 12:21 PM
  • NO..

    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, September 9, 2009 12:23 PM
  • Hi,

    Yes there is no straight forward SP or query to get it. But one possible way of getting it is:

    If you have 10 packages, create a table with a columns package name and packagemetadata(xml data type). and laod this table with the package names and XML file(SSIS package is nothing but an xml file).

    Then query xml data avaialable in the table and search for the table name based on the oledb source and oledb destination transformation names.

    Well, I haven't tried it out practically, but just giving an insight of how we can acheive it.

    Regards,
    Raju
    • Proposed as answer by Sudeep Raj Wednesday, September 9, 2009 12:38 PM
    Wednesday, September 9, 2009 12:36 PM
  • I am looking for a way to find out which tables I am using in SSIS packages. If I make a change to a table I need to know which packages I may need to examine. I have looked at the stored procedures but do not see anything that jumps out at me. Is there a procedure that will list the tables used in a package?
    Nope, unfortunately, there isn't.  The only way to programatically get at the metadata of an SSIS package is to use the SSIS API and interogate each package in question - and even then you may have limited success.



    Wednesday, September 9, 2009 12:38 PM
  • Yes !!!
    the developer of the packages must have maintained the list as well the use case / business rules ;)
    Wednesday, September 9, 2009 12:39 PM
  • Hi,

    Yes there is no straight forward SP or query to get it. But one possible way of getting it is:

    If you have 10 packages, create a table with a columns package name and packagemetadata(xml data type). and laod this table with the package names and XML file(SSIS package is nothing but an xml file).

    Then query xml data avaialable in the table and search for the table name based on the oledb source and oledb destination transformation names.

    Well, I haven't tried it out practically, but just giving an insight of how we can acheive it.

    Regards,
    Raju
    If you deploy to MSDB, this information is already available as you described.  I think you'll have limited success parsing the XML - especially where datasources are based off of variable values.
    Wednesday, September 9, 2009 12:43 PM
  • Hi Phil,

    I am planning to implement the same tomorrow, as we have around 700 tables which needs to warehoused, and any change to any of the table we are not sure which package metadata needs to refreshed.

    Well, I thought of reading the XML data from the table against each and every packagename where metadata like 'Table%'. And I am not sure how accurate I will be getting the results. Also we have few packages where the data sources are of variable value where variable values are hard coded inside the package.

    You might be right if the variable value is fetched from a configuration file. I will try it out and post the result.

    Regards,
    Raju

    Wednesday, September 9, 2009 1:02 PM
  • Hi,

    Yes there is no straight forward SP or query to get it. But one possible way of getting it is:

    If you have 10 packages, create a table with a columns package name and packagemetadata(xml data type). and laod this table with the package names and XML file(SSIS package is nothing but an xml file).

    Then query xml data avaialable in the table and search for the table name based on the oledb source and oledb destination transformation names.

    Well, I haven't tried it out practically, but just giving an insight of how we can acheive it.

    Regards,
    Raju
    If you deploy to MSDB, this information is already available as you described.  I think you'll have limited success parsing the XML - especially where datasources are based off of variable values.

    What do you mean deploy to MSDB. I am using SSIS to build tables then use SSAS to build cubes for Data mart applications.
    Wednesday, September 9, 2009 3:06 PM
  • Hi, It means storing SSIS packages in MSDB.Phil correct me If I am wrong here. Regards, Raju
    Wednesday, September 9, 2009 3:16 PM
  • Correct.
    Wednesday, September 9, 2009 3:18 PM
  • So how does one store ssis packages in MSDB. What is MSDB?
    Wednesday, September 9, 2009 3:22 PM
  • Hi, Please go through the below link , which will proivde you complete information about managing and deplying SSIS pacakes. http://technet.microsoft.com/en-us/library/cc966389.aspx Regards, Raju
    Wednesday, September 9, 2009 3:27 PM
  • What I am seeing is that I need to deploy my packages and not use them as files. When they are deployed then something occurs which will allow me to see what tables are used in which deployed package.
    Wednesday, September 9, 2009 3:36 PM
  • What I am seeing is that I need to deploy my packages and not use them as files. When they are deployed then something occurs which will allow me to see what tables are used in which deployed package.
    No, you still won't be able to see what packages touch what tables.  For that you'll still have to try to parse XML data, or use the SSIS API as I mentioned earlier.  Either way, you will have challenges with tables that are sourced from variables.
    Wednesday, September 9, 2009 3:38 PM
  • Phil

    I am confused then by your statement "If you deploy to MSDB, this information is already available as you described. "?


    I should be able to read throught the xml as described above and will see what I come up with.

    Wednesday, September 9, 2009 3:42 PM
  • Phil

    I am confused then by your statement "If you deploy to MSDB, this information is already available as you described. "?


    I should be able to read throught the xml as described above and will see what I come up with.

    I was referring to the post by Jagapathi where it was recommended to create a table to store the package XML information.  I wanted to point out that if you deploy to MSDB, there is already a table that contains the package XML - you don't have to create anything.

    The trouble for you will be understanding how to interpret the XML and then to be able to make decisions based on what you find so that you can generate a list of tables used in your package(s).  I don't think this is an easy task.
    • Marked as answer by MPCJoe1 Wednesday, September 9, 2009 4:21 PM
    Wednesday, September 9, 2009 3:49 PM
  • Looks like it is the only choice I have.

    I already have 900 + tables I am trying to track in an excel spreadsheet, trying to maintain this manual list is unworkable. I am adding additional developers and this is huge failure point.

    Wednesday, September 9, 2009 4:20 PM
  • How would I load the xml data?

    Wednesday, September 9, 2009 4:25 PM
  • How would I load the xml data?


    Which are you talking about? Loading it to a table, loading it from the table and querying it, loading it to the .net SSIS engine and investigating it there or what?  The question is somewhat vague when you look at the full thread...



    Please mark answered posts. Thanks for your time.
    Wednesday, September 9, 2009 7:55 PM
  • Guys,

    I  have a written a script task which will search for the table names used in packages and will display the list of packages effected given the table name as an input. I am using file system storage for the SSIS packages and its working perfectly. This might be perfect to my situation where I dont have variable value which can change at run time when used as a source variable connection . And it may not work perfectly for others where Phil already described.

    Note: I am using file system storage not MSDB,

    Regards,
    Raju
    Monday, September 14, 2009 10:02 AM
  • Sounds like what I am looking for too. Could I get a look at the code? I am using file system storage and have been manually going through my packages so this would be a good check.
    Friday, September 18, 2009 11:30 AM
  • If you are using good naming conventions, you might get what you want just by being a bit creative with the findstr function in windows commandline...

    Thats how I do it...

    Friday, June 14, 2013 4:21 PM