Extracting SSIS Package information using T-SQL

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 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=”http://search.microsoft.com/Results.aspx?mkt=en-US&qsc0=0&q=Sql+Server+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