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

Cable Testers and How to Use them in Network Environments

0 724 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Government Edition – Encrypting a USB Flash Drive in Windows 10

0 276 2

In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10

Write a Comment

See what people are saying...

    Share your thoughts...

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