Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Query on msdb.dbo.sysssispackages Expand / Collapse
Author
Message
Posted Friday, November 2, 2012 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:11 PM
Points: 5, Visits: 94
Hi,

I am trying to extract some information from SSIS packages store on the server in [msdb].[dbo].[sysssispackages]

For all packages, i need :

PackageName
All Sources information (Server, Databases, list of tables or views that are used in the source)
All Transformations source table or views
All Destination table names with server/databases information

The main goal is to write a program for maintenance in our BI environement. (See witch table or view are no more in use in all our package, cubes, etc...)

Is there a way to do that ?

I have started with this :

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces
SELECT c.name as NomPackage,
SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]','varchar(100)') AS ConnectionManager,
SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionString,
SSIS_XML.value('pNS1:pipeline[1]/pNS1:components[1]/pNS1:component[1]/pNS1:properties[1]/pNS1:property[@name="OpenRowset"][1]','varchar(100)') AS DestOrSourceTable


--SSIS_XML.value('(//property[@name="OpenRowset"])[1]','varchar(100)') AS DestinationTable

FROM
--
( SELECT id ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
FROM [msdb].[dbo].[sysssispackages]
) PackageXML
CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML )
INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id
Where Name not in('QueryActivityUpload','PerfCountersUpload','SqlTraceUpload','Manual Backup','TSQLQueryUpload',
'TSQLQueryCollect','QueryActivityCollect','SqlTraceCollect','PerfCountersCollect')

But I am not very good a understanding XML and I lost haft of my hair in the last 2 days tring to make it work.

Is there another way ?
Post #1380488
Posted Friday, February 21, 2014 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 10:10 AM
Points: 1, Visits: 9
Wanted to see if you ever found a solution for this? I've been tasked with something similar and haven't found anything online yet. Appreciate any insight you can share.

Thanks,
Jon
Post #1544174
Posted Friday, February 21, 2014 3:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 21,297, Visits: 14,988
see if this is of any help

http://www.sqlservercentral.com/blogs/sqlballs/2013/01/21/how-do-you-query-maintenance-plan-package-metadata/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1544202
Posted Sunday, March 23, 2014 4:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 1,320, Visits: 3,775
Better late than never

Here is an example;

DECLARE @DTSXML XML = N'<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="11/7/2013 12:57:04 PM"
DTS:CreationName="SSIS.Package.3"
DTS:CreatorComputerName="USER-COMPUTER"
DTS:CreatorName="MyUserName"
DTS:DTSID="{10A38C50-3656-42E0-A054-87E8D9DFBD8A}"
DTS:ExecutableType="SSIS.Package.3"
DTS:LastModifiedProductVersion="11.0.2100.60"
DTS:LocaleID="2057"
DTS:ObjectName="TEST_RELOAD_PACKAGE_DDL_TEST_RELOAD"
DTS:VersionGUID="{9C72489E-4800-4E59-9666-6B3FA0A664F9}">
<DTS:Property
DTS:Name="PackageFormatVersion">6</DTS:Property>
<DTS:ConnectionManagers>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[DESTINATION_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{1FD5677D-2669-41DB-83DB-CB8FE1C89246}"
DTS:ObjectName="DESTINATION_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager />
</DTS:ObjectData>
</DTS:ConnectionManager>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[EXEC_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"
DTS:ObjectName="EXEC_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[LOG_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{9D9716EF-0E06-4BDF-89DA-B8EA03A0871F}"
DTS:ObjectName="LOG_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
</DTS:ConnectionManagers>
<DTS:LogProviders>
<DTS:LogProvider
DTS:ConfigString="LOG_CONN"
DTS:CreationName="DTS.LogProviderSQLServer"
DTS:DTSID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}"
DTS:ObjectName="LogToSQLServer">
<DTS:ObjectData>
<InnerObject />
</DTS:ObjectData>
</DTS:LogProvider>
</DTS:LogProviders>
<DTS:Variables />
<DTS:LoggingOptions
DTS:FilterKind="0"
DTS:LoggingMode="1">
<DTS:Property
DTS:DataType="8"
DTS:Name="EventFilter">4,12,OnTaskFailed,10,OnProgress,7,OnError,9,OnWarning</DTS:Property>
<DTS:SelectedLogProviders>
<DTS:SelectedLogProvider
DTS:InstanceID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}" />
</DTS:SelectedLogProviders>
</DTS:LoggingOptions>
<DTS:Executables>
<DTS:Executable
DTS:refId="Package\EXEC__SRSTG___USP_HOTELS_"
DTS:CreationName="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DTS:DTSID="{7401EF27-6D72-4E6A-A294-D88CDA1176B6}"
DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DTS:LocaleID="-1"
DTS:ObjectName="EXEC__SRSTG___USP_HOTELS_"
DTS:ThreadHint="0">
<DTS:Variables />
<DTS:ObjectData>
<SQLTask:SqlTaskData
SQLTask:Connection="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"
SQLTask:SqlStatementSource="SELECT * FROM [SRSTG].[TBL_HOTELS];" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
</DTS:ObjectData>
</DTS:Executable>
</DTS:Executables>
</DTS:Executable>'
;WITH XMLNAMESPACES(
'www.microsoft.com/SqlServer/Dts' AS DTS,
DEFAULT 'www.microsoft.com/SqlServer/Dts'
)
SELECT
EXN.R.value('@DTS:refId' ,'NVARCHAR(128)') AS PKG_refId
,EXN.R.value('@DTS:CreationDate' ,'DATETIME2(0)' ) AS PKG_CreationDate
,EXN.R.value('@DTS:CreationName' ,'NVARCHAR(128)') AS PKG_CreationName
,EXN.R.value('@DTS:CreatorComputerName' ,'NVARCHAR(128)') AS PKG_CreatorComputerName
,EXN.R.value('@DTS:CreatorName' ,'NVARCHAR(128)') AS PKG_CreatorName
,EXN.R.value('@DTS:DTSID' ,'NVARCHAR(128)') AS PKG_DTSID
,EXN.R.value('@DTS:ExecutableType' ,'NVARCHAR(128)') AS PKG_ExecutableType
,EXN.R.value('@DTS:LastModifiedProductVersion' ,'NVARCHAR(128)') AS PKG_LastModifiedProductVersion
,EXN.R.value('@DTS:LocaleID' ,'NVARCHAR(128)') AS PKG_LocaleID
,EXN.R.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS PKG_ObjectName
,EXN.R.value('@DTS:VersionGUID' ,'NVARCHAR(128)') AS PKG_VersionGUID
,EXN.R.value('local-name(.)' ,'NVARCHAR(128)') AS LName
,EXN.R.value('.','NVARCHAR(MAX)') AS LValue

,CON.MAN.value('@DTS:refId' ,'NVARCHAR(128)') AS CNM_refId
,CON.MAN.value('@DTS:CreationName' ,'NVARCHAR(128)') AS CNM_CreationName
,CON.MAN.value('@DTS:DTSID' ,'NVARCHAR(128)') AS CNM_DTSID
,CON.MAN.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS CNM_ObjectName

FROM @DTSXML.nodes('/DTS:Executable') AS EXN(R)
OUTER APPLY EXN.R.nodes('DTS:ConnectionManagers/DTS:ConnectionManager') AS CON(MAN);

Post #1553803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse