Asked by:
How to get PackageName of a called package from a SSIS project package with XMLNAMESPACES?

Question
-
Hello,
I am trying to document an SSIS project which calls-off a few other packages. I am able to get some details out of the packages called from the project package ( ProjectName.dtsx ). To make the relation from the project to the individual called packages, I am trying to parse the projectName xml file.
I am able to list content of the executables but not able to get the called package ( package1.dtsx )
This is the XML
<DTS:Executable
DTS:refId="Package\Export SimpleFS_TLS"
DTS:CreationName="Microsoft.ExecutePackageTask"
DTS:Description="Execute Package Task"
DTS:DTSID="{ABDD8A9B-4B22-4A67-A85C-C146938B7640}"
DTS:ExecutableType="Microsoft.ExecutePackageTask"
DTS:LocaleID="-1"
DTS:ObjectName="Export SimpleFS_TLS"
DTS:TaskContact="Microsoft Corporation; Microsoft SQL Server; Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1">
<DTS:Variables />
<DTS:ObjectData>
<ExecutePackageTask>
<UseProjectReference>True</UseProjectReference>
<PackageName>Export_SimpleFS_TLS.dtsx</PackageName>......
</ExecutePackageTask>
</DTS:ObjectData>
</DTS:Executable>This is a piece of the code but the last column stays NULL
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS,
'www.microsoft.com/ExecutePackageTask' AS PKG )
SELECT
Proj_Name.jaap.value('./@DTS:refId','nvarchar(max)')
,Proj_Name.jaap.value('./@DTS:refId','nvarchar(max)') as [Executable]
,Proj_Name.jaap.value('./@DTS:ObjectName','nvarchar(max)') as [Project_Name]
,Proj_Name.jaap.value('./@DTS:LastModifiedProductVersion','nvarchar(max)') as [execType]
,Proj_Name.jaap.value('./@DTS:refid','nvarchar(max)') as [jaap]
,Task_names.task_name.value('./@DTS:ObjectName','nvarchar(max)') as [Project_TaskName]
,Task_names.task_name.value('./DTS:ObjectData[1]/PKG:ExecutePackageTask[1]/@PKG:PackageName','varchar(50)') as [Project_RefID]
FROM
( SELECT id ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
FROM [msdb].[dbo].[sysssispackages]
where name = 'Finance'
) PackageXML
CROSS APPLY PackageXML.nodes('DTS:Executable') Proj_Name ( jaap )
CROSS APPLY Proj_Name.jaap.nodes('DTS:Executables/DTS:Executable') Task_Names ( Task_Name )
INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.idI hope there is someone who able to tell me what I am doing wrong here. I also noticed that the names of the attributes are case sentive. Thanks in advance for your time and effort
Kind Regards
Jaap de Reus
Thursday, May 14, 2020 1:09 PM
All replies
-
I don't know why you have a PKG namespace. The ExecutPackageTash doesn't have a Namespace.
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS ) SELECT Proj_Name.jaap.value('./@DTS:refId','nvarchar(max)') ,Proj_Name.jaap.value('./@DTS:refId','nvarchar(max)') as [Executable] ,Proj_Name.jaap.value('./@DTS:ObjectName','nvarchar(max)') as [Project_Name] ,Proj_Name.jaap.value('./@DTS:LastModifiedProductVersion','nvarchar(max)') as [execType] ,Proj_Name.jaap.value('./@DTS:refid','nvarchar(max)') as [jaap] ,Task_names.task_name.value('./@DTS:ObjectName','nvarchar(max)') as [Project_TaskName] ,Task_names.task_name.value('./DTS:ObjectData[1]/ExecutePackageTask[1]/@PackageName','varchar(50)') as [Project_RefID] FROM ( SELECT id , CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML FROM [msdb].[dbo].[sysssispackages] where name = 'Finance' ) PackageXML CROSS APPLY PackageXML.nodes('DTS:Executable') Proj_Name ( jaap ) CROSS APPLY Proj_Name.jaap.nodes('DTS:Executables/DTS:Executable') Task_Names ( Task_Name ) INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id
Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com
Friday, June 19, 2020 12:50 AM -
Hi Jaap de Reus,
Your XML sample (I added a missing namespace):
<?xml version="1.0"?> <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package\Export SimpleFS_TLS" DTS:CreationName="Microsoft.ExecutePackageTask" DTS:Description="Execute Package Task" DTS:DTSID="{ABDD8A9B-4B22-4A67-A85C-C146938B7640}" DTS:ExecutableType="Microsoft.ExecutePackageTask" DTS:LocaleID="-1" DTS:ObjectName="Export SimpleFS_TLS" DTS:TaskContact="Microsoft Corporation; Microsoft SQL Server; Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"> <DTS:Variables/> <DTS:ObjectData> <ExecutePackageTask> <UseProjectReference>True</UseProjectReference> <PackageName>Export_SimpleFS_TLS.dtsx</PackageName> </ExecutePackageTask> </DTS:ObjectData> </DTS:Executable>
Your 2nd CROSS APPLY is questionable.
Please try instead of this line
,Task_names.task_name.value('./DTS:ObjectData[1]/PKG:ExecutePackageTask[1]/@PKG:PackageName','varchar(50)') as [Project_RefID]
This line:
, jaap.value('(DTS:ObjectData/ExecutePackageTask/PackageName/text())[1]','VARCHAR(100)') AS as [Project_RefID]
Friday, June 19, 2020 1:42 AM