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.id

I 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

Answer 1

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

Answer 2

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]