November 2, 2012 at 10:12 am
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 ?
February 21, 2014 at 1:33 pm
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
February 21, 2014 at 3:20 pm
see if this is of any help
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 23, 2014 at 4:11 am
Better late than never :w00t:
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);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply