how to query msdb.dbo.sysssispackages.packagedata

Question

I read this thread Query msdb.dbo.sysssispackages XML for specific values

but I need to know how I can get values "down the xpath" sort of speak here is the snippet of the xml

<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package">
  <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
  <DTS:ConnectionManagers>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Excel Connection Manager 1]" DTS:CreationName="EXCEL" DTS:DTSID="{D64931D0-C6E7-4529-9F44-FC769EC4A125}" DTS:ObjectName="Excel Connection Manager 1">
      <DTS:PropertyExpression DTS:Name="ExcelFilePath">@[User::FilePath]</DTS:PropertyExpression>
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyProjects\test.xlsx;Extended Properties=&quot;EXCEL 12.0 XML;HDR=YES&quot;;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Excel File]" DTS:CreationName="OLEDB" DTS:DTSID="{daf3ba32-d620-41ec-9c5d-5d142ad08bcb}" DTS:ObjectName="Excel File">
      <DTS:PropertyExpression DTS:Name="ServerName">@[User::FilePath]</DTS:PropertyExpression>
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:ConnectionString="Data Source=D:\MyProjects\test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=&quot;Excel 12.0 XML;HDR=YES&quot;;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[TestServerDB]" DTS:CreationName="OLEDB" DTS:DTSID="{3B3B499A-E74F-4C18-B4F5-B99FD2AFE677}" DTS:ObjectName="CustDB">
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:ConnectionString="Data Source=TestServerA;Initial Catalog=Cust Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
      </DTS:ObjectData> 
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[SMTP Connection Manager]" DTS:CreationName="SMTP" DTS:DTSID="{DEC73805-AD2A-4CB9-8ABC-5993F786F967}" DTS:ObjectName="SMTP Connection Manager">
      <DTS:ObjectData>
        <SmtpConnectionManager ConnectionString="SmtpServer=smtpgw.com;UseWindowsAuthentication=False;EnableSsl=False;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
  </DTS:ConnectionManagers>
  <DTS:Variables>....

here is the sql pulling the xml... just snippet

  WITH XMLNAMESPACES (
      'www.microsoft.com/SqlServer/Dts' AS pNS1,
      'www.microsoft.com/SqlServer/Dts' AS DTS
  )
  SELECT
  PackageXML.*,
  PackageUniqifier,
  SSIS_XML.value('@DTS:CreationName', 'varchar(max)') AS TypeString
  ,SSIS_XML.value('@DTS:ObjectName', 'varchar(max)') AS ConnectionString
  --,obj_data.value('@ConnectionString', 'varchar(MAX)') AS ConnectionString
  ,SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="DTS:ConnectionString"][1]', 'varchar(MAX)') AS ConnectionString
  FROM #SSISPackagesList PackageXML
  CROSS APPLY
    PackageXMLContent.nodes ('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS SSIS_XML(SSIS_XML)

I'm trying to return the ConnectionString="SmtpServer=smtpgw.com;UseWindowsAuthentication=False;EnableSsl=False;

I tied by doing

,SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="DTS:ConnectionString"][1]', 'varchar(MAX)') AS ConnectionString

but it doesn't work

if I add a second cross apply like ...

FROM #SSISPackagesList PackageXML
CROSS APPLY 
    PackageXMLContent.nodes ('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS SSIS_XML(SSIS_XML) 
cross apply SSIS_XML.nodes ('DTS:ObjectData/SmtpConnectionManager') AS obj_data(obj_data)

then I can use

obj_data.value('@ConnectionString', 'varchar(MAX)') AS ConnectionString

but I rather not do a second cross apply.... any ideas?

I've added code formatting to your question (which is how you should do it), but I'm leaving the removal of the "
"'s to you. — Dale K, Aug 12 '21 at 01:01
It's not clear why you're defining two namespace aliases for the same namespace URI, `www.microsoft.com/SqlServer/Dts`. — AlwaysLearning, Aug 12 '21 at 02:02
thanks for formatting my post. I will keep that in mind for next time — user2836030, Aug 12 '21 at 11:50

score 0 · Answer 1 · answered Aug 12 '21 at 02:06

0

You can supply alternate subpaths in your .value() XPath, e.g.:

SSIS_XML.value('((DTS:ObjectData/DTS:ConnectionManager)[1]/@DTS:ConnectionString,(DTS:ObjectData/SmtpConnectionManager)[1]/@ConnectionString)[1]', 'varchar(MAX)') AS ConnectionString

answered Aug 12 '21 at 02:06

AlwaysLearning

7,915
5
27
35

thanks for the line of code it works. But is it possible to get a resource or web site that can explain what is going on or how it works. – user2836030 Aug 12 '21 at 11:59
There are plenty of tutorial resources out there for XQuery and XPath. You just need to Bing, DuckDuckGo or Google them. – AlwaysLearning Aug 12 '21 at 21:09

how to query msdb.dbo.sysssispackages.packagedata

1 Answers1