Extracting metadata from SSIS packages

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Extracting metadata from SSIS packages

Extracting metadata from SSIS packages

Rate Topic

Display Mode

Topic Options

Author

Message

Idris_Arslanian

Posted Wednesday, August 12, 2009 9:21 AM

Grasshopper

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

Paul_J

Posted Wednesday, August 12, 2009 5:58 PM

SSC-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

Phil Parkin

Posted Thursday, August 13, 2009 2:23 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 4,242, Visits: 9,492

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #769963

« Prev Topic | Next Topic »

Permissions