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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
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.
Post #769963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse