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
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
How to clone a Windows Server 2012 or 2012 R2 Domain Controller
One of the coolest new features in Window Server 2012 and Windows Server 2012 R2 is the ability to clone a Domain Controller. In the past, if we had virtualized Domain Controllers and we actually took a snapshot of it and then rolled back to that snapshot, it would break the logon service on that … Continue reading How to clone a Windows Server 2012 or 2012 R2 Domain Controller
Detailed Forensic Investigation of Malware Infections – April 21, 2015
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015
Write a Comment
See what people are saying...