Hi, As part of our development we have created around 70-75 SSIS packages so far, where we have used different table from SQL Server. Now is there any easy way to get the packages name where any particular is in used (either as Source or Destination). Actually as part of our development we are changing few tables' script and now every time we do this change we have to find out manually what are packages are impacted for this change. So we are looking for any easy way for getting this metadata. Thanks in advance for any serious help. Regards - Koushik

Its not easy unfortunately. You can use the SSIS API to iterate over the contents of a package (see Building Packages Programatically) or you can parse the XML inside the .dtsx file yourself in order to get the info you're interested in. I show examples of parsing the XML here: Collecting information about your SSIS packages SSIS Package stats collector version 2 They won't explicitly solve your problem but you should be able to adapt the code to suit. Failing that you could just search the XML for any instance of the table name you're looking for (Notepad++ has a very useful "Find in files" feature that should help). That is *very* easy. Hope that helps @Jamiet http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi, As part of our development we have created around 70-75 SSIS packages so far, where we have used different table from SQL Server. Now is there any easy way to get the packages name where any particular is in used (either as Source or Destination). Actually as part of our development we are changing few tables' script and now every time we do this change we have to find out manually what are packages are impacted for this change. So we are looking for any easy way for getting this metadata. Thanks in advance for any serious help. Regards - Koushik HI Koushik, I dont think there is an easy way or table that store that minute level information about packages .. there could be work arounds though like u have the infos logged in some table .. u can as well open the dtsx file in text editor and search for "YOUR_TABLE_NAME" in the file if the name is present u know that some or other way ur package is referring to that table ... one more teadious way would to open the package via c# code iterate through Executables [in dataflow ] ... and check out the source or destination names for search table name/ names ... all that is gonna be a teadious job .. i am sure u must have been provided some business document / use case for developing the packages .... if not i'd strongly suggest u should have practice of having such mapping document ... worst case if u want to find out then better have dummy modified databases change ur packages connection strings to point to new[modified ] schema and run ur job / third party scheduler .. wherever ur package cribs for metadata change u can pin point the package to work on .. Phwe... i'd really like to know if there is really short cut way to find out the info required by u without parsing the dtsx files in any way :) Hope that helps ... Kunal

Thanks a lot Jamie. Can you please let me know that is there any way to collect multiple package codes "using OPENROWSET as you have shown" in a single go or do I have to do it one by one always. Regards - Koushik

Thanks a lot Jamie. Can you please let me know that is there any way to collect multiple package codes "using OPENROWSET as you have shown" in a single go or do I have to do it one by one always. Regards - Koushik Hi, See section "Putting it all together" in Collecting information about your SSIS packages, that shows an example of running it for multiple packages and has a link to the script that achieves it. Hope it helps! @Jamiethttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me