Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting metadata from SSIS packages Expand / Collapse
Author
Message
Posted Wednesday, August 12, 2009 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 29, 2010 8:50 AM
Points: 15, Visits: 45
So I was just assigned an interesting task by my senior DBA to look into the SSIS packages installed to our prod SSIS box and pull relevant metadata in order to document some older packages. To wit; I need to find which stored procedures are used in the packages. I would prefer not to open the packages individually and would optimally like to look into some sort of SSIS summary tables to compile queries to get this information. Is this possible? I know the packages are stored in MSDB, via XML, but I'm not finding the tables where I might be able to use XQuery to get the data.
Post #769402
Posted Wednesday, August 12, 2009 5:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 5:56 PM
Points: 185, Visits: 637
I know there is a packagedata column or something in the dbo.sysdtspackages90 system table in the msdb database. It's binary I believe.

Not sure how to do what you want other than by loading the package into memory using the SSIS API using .NET code, saving it out to file somewhere, and examining the dtsx file since it's XML afterall.
Post #769785
Posted Thursday, August 13, 2009 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 AM
Points: 5,314, Visits: 12,341
You might find this link interesting:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/77ebba39-2dd7-4c5c-bd97-6c6d9e5ca2f5



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #769963
Posted Wednesday, November 5, 2014 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:33 AM
Points: 8, Visits: 31
this kind of impact analysis of SPROCS used by SSIS Packages is something we have a product for.
You are welcome to try it for yourself.

Another option is to pull this metadata via C# and the API's for SSIS, not a trivial task, but doable.

here is our link if you are interested to see how we do it.

http://www.infolibcorp.com
Post #1631119
Posted Wednesday, November 5, 2014 10:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 4,653, Visits: 11,110
bbrewer 62909 (11/5/2014)
this kind of impact analysis of SPROCS used by SSIS Packages is something we have a product for.
You are welcome to try it for yourself.

Another option is to pull this metadata via C# and the API's for SSIS, not a trivial task, but doable.

here is our link if you are interested to see how we do it.

http://www.infolibcorp.com


Do you understand that this post is 5 years old?


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1631127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse