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 6 Jeff 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


 

Videos You May Like

A Simple Introduction to Cisco CML2

0 3624 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Configuring Windows Mobility Center and How to Turn it On and Off

1 1402 1

Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off

How to use AWS CloudFormation templates to automate solutions inside Amazon AWS

0 380 0

  In the AWS Class here at Interface, we actually build fully automated solutions with AWS CloudFormations. Here’s an overview of using AWS templates using CloudFormation. CloudFormation is basically an “infrastructure-as-code tool” where you can go into a declarative document and define all the resources that you want and feed the document into the CloudFormation … Continue reading How to use AWS CloudFormation templates to automate solutions inside Amazon AWS

Write a Comment

See what people are saying...

    Share your thoughts...

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