locked
How to get PackageName of a called package from a SSIS project package with XMLNAMESPACES? RRS feed

  • 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

    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