Extracting SSIS Package information using T-SQL

Home > Blogs > SQL Server > Extracting SSIS Package information using T-SQL

Extracting SSIS Package information using T-SQL

Like This Blog 5Jeff Jones
Added by May 10, 2010

SQL Server Integration Services can store it packages in either the file system or the MSDB system database.  One of the advantages of storing a package in MSDB is that you can use T-SQL to read the package definition and generate reports on your SSIS package library.

Here are a couple of example queries to extract the package GUID for each package and to display the connection string for each OLEDB connection manager in a package.

Example 1: Package GUID display

 


USE MSDB
GO
WITH xmlPackages
-- Read sysssispackage and convert packagedata image column to XML datatype
AS
(SELECT [name]
, [Description]
, verbuild
, CAST(CAST(packagedata as VARBINARY(MAX)) AS XML) PackageXML
FROM dbo.sysssispackages)
,
Packages
-- Cross join each package row with itself to extract out the GUID then convert it to VARCHAR

AS
(SELECT [name]
, [Description]
, [verbuild]
, x.guid.value('text()[1]', 'varchar(4000)') PackageGUID
FROM xmlPackages
CROSS APPLY
PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:Executable/DTS:Property[@DTS:Name="VersionGUID"]') AS x(guid)
)

SELECT *
FROM Packages

 

Example 2: Display Connections strings for each OLEDB Connection Manager
 

USE MSDB
GO
WITH xmlPackages
-- Read sysssispackages and convert packagedata image column to XML datatype
AS
(SELECT [name]
, [Description]
, verbuild
, CAST(CAST(packagedata as VARBINARY(MAX)) AS XML) PackageXML
FROM dbo.sysssispackages)
,
Packages
-- Cross join each package row with OLEDB connections found in the package and convert it to VARCHAR
AS
(SELECT [name]
, [Description]
, [verbuild]
, x.cn.value('text()[1]', 'varchar(4000)') Conn
FROM xmlPackages
CROSS APPLY
PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:ConnectionManager[DTS:Property="OLEDB"]/DTS:ObjectData/DTS:ConnectionManager/DTS:Property [@DTS:Name="ConnectionString"]') AS x(cn)
)
SELECT [Name] PackageName
, [Description]
, [verbuild]
, Conn ConnectionString
FROM Packages


 

You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

Videos You May Like

Creating Users and Managing Passwords in Microsoft Office 365

0 98 1

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.

JavaScript for C# Developers – September 24, 2014

0 196 1

In this video Dan Wahlin demonstrate the similarities and differences between C# and JavaScript and the future of JavaScript (ES6).

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 192 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

  1. Thomas

    Thank you. This was very helpful. I am trying to find the Child Packages a package calls. I cannot figure out the syntax to get what is in Arguments

    Any help is appreciated. Thank you

  2. Jacob

    Found it! When copying the code make sure it uses correct Invalid character for quote ‘
    Also comments have to be removed or re-keyed.
    Thank you very much for your help!

  3. Jacob

    It looks like I is giving an error message: “Msg 2333, Level 16, State 1, Line 8
    XQuery [#xmlPackages.PackageXML.nodes()]: Invalid source character 0x201d”
    Can you help please?

  4. Interface Technical Training

    Glad you found it useful. Both examples are the SQL code. The line you referenced is a comment. These SQL statements will would with SQL Server 2005 or later.

  5. Jacob

    Thank you for your article. Could you please also include the code for ” Read sysssispackage and convert packagedata image column to XML datatype” .
    Is this code SQL version specific?

Share your thoughts...

Please fill out the comment form below to post a reply.