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 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
A Simple Introduction to Cisco CML2
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
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
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...