Some XML Query Magic Required
-
Hi all. As many of you will know, SSIS packages are defined in XML. I am working on a little project to analyse the variables and parameters in an SSIS project and need some help accessing variables of multiple scopes.
I have a sample package with the following variables:
'pkg_2012' is the package (top-level) scope.
But notice how the variable 'pathname' appears twice, with different scopes.
SSIS variables are defined in a hierarchy that looks something like
.../DTS:Executable/DTS:variables/DTS:variable
Unfortunately, this hierarchy can be several levels deep (imagine a variable scoped to a dataflow within a foreach container within a sequence container, for example).
My XQuery knowledge is rudimentary. Can anyone provide a query which pulls DTS:objectname from any occurrence of DTS:variable (which is the variable name), along with the value of DTS:objectname in the corresponding DTS:Executable level (which is the variable scope)?
Here is some sample code which sets up the package XML corresponding with the variables in the screenshot above.
DROP TABLE IF EXISTS #SSISTest;
CREATE TABLE #SSISTest
(
PackageXML XML NOT NULL
);
INSERT #SSISTest
(
PackageXML
)
SELECT CAST('<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="5/12/2020 8:08:56 AM" DTS:CreationName="SSIS.Package.3" DTS:CreatorComputerName="ETSID005505" DTS:CreatorName="EUROPA\w136" DTS:DTSID="{52E6D8B8-5E23-4F3C-BF87-78CF3BAE4B50}" DTS:ExecutableType="SSIS.Package.3" DTS:LastModifiedProductVersion="13.0.5026.0" DTS:LocaleID="6153" DTS:ObjectName="pkg_2012" DTS:PackageType="5" DTS:VersionBuild="1" DTS:VersionGUID="{023FA7EE-3A6A-4366-AAC5-9E94E9FDE2E1}"><DTS:Property DTS:Name="PackageFormatVersion">6</DTS:Property><DTS:ConnectionManagers><DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[SampleFile]" DTS:CreationName="FLATFILE" DTS:DTSID="{CBBEFBDB-1791-4691-89F8-2A137D1360E3}" DTS:ObjectName="SampleFile"><DTS:PropertyExpression DTS:Name="ConnectionString">@[User::pathname2] + @[User::pathname1]</DTS:PropertyExpression><DTS:ObjectData><DTS:ConnectionManager DTS:Format="Delimited" DTS:LocaleID="6153" DTS:HeaderRowDelimiter="_x000D__x000A_" DTS:ColumnNamesInFirstDataRow="True" DTS:RowDelimiter="" DTS:TextQualifier="_x003C_none_x003E_" DTS:CodePage="1252" DTS:ConnectionString="C:\fred\phil\demo_variables\samplefile.txt"><DTS:FlatFileColumns><DTS:FlatFileColumn DTS:ColumnType="Delimited" DTS:ColumnDelimiter="_x000D__x000A_" DTS:MaximumWidth="50" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="Name" DTS:DTSID="{ACB1C8E8-3FAF-4223-A622-78C079B3C265}" DTS:CreationName=""/></DTS:FlatFileColumns></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager></DTS:ConnectionManagers><DTS:Variables><DTS:Variable DTS:CreationName="" DTS:DTSID="{49DFC240-C8C6-441E-AB1A-D542FA6FD91D}" DTS:IncludeInDebugDump="2345" DTS:Namespace="User" DTS:ObjectName="pathname"><DTS:VariableValue DTS:DataType="8" xml:space="preserve"/></DTS:Variable><DTS:Variable DTS:CreationName="" DTS:DTSID="{B2235D27-917C-46DB-843B-4DCFBAA12E43}" DTS:IncludeInDebugDump="2345" DTS:Namespace="User" DTS:ObjectName="pathname1"><DTS:VariableValue DTS:DataType="8">samplefile.txt</DTS:VariableValue></DTS:Variable><DTS:Variable DTS:CreationName="" DTS:DTSID="{B0CB2E4F-BF8A-47F8-865D-EB14920558FA}" DTS:IncludeInDebugDump="2345" DTS:Namespace="User" DTS:ObjectName="pathname2"><DTS:VariableValue DTS:DataType="8">C:\fred\phil\demo_variables\</DTS:VariableValue></DTS:Variable><DTS:Variable DTS:CreationName="" DTS:DTSID="{9C0C10D0-C0FE-48B5-984F-F779A836CAAC}" DTS:IncludeInDebugDump="6789" DTS:Namespace="User" DTS:ObjectName="rowcount"><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue></DTS:Variable></DTS:Variables><DTS:Executables><DTS:Executable DTS:refId="Package\Sequence Container" DTS:CreationName="STOCK:SEQUENCE" DTS:Description="Sequence Container" DTS:DTSID="{6A0E28C4-9481-4A92-B26A-C0A2F4AB4E8B}" DTS:ExecutableType="STOCK:SEQUENCE" DTS:LocaleID="-1" DTS:ObjectName="Sequence Container"><DTS:Variables/><DTS:Executables><DTS:Executable DTS:refId="Package\Sequence Container\DFT-SampleCount" DTS:CreationName="SSIS.Pipeline.3" DTS:Description="Data Flow Task" DTS:DTSID="{9EAE9AA8-0A00-4CE4-833D-C3612A876D23}" DTS:ExecutableType="SSIS.Pipeline.3" DTS:LocaleID="-1" DTS:ObjectName="DFT-SampleCount" DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"><DTS:Variables><DTS:Variable DTS:CreationName="" DTS:DTSID="{FD82CDE8-2D59-48A1-91A0-89D56B11B6AD}" DTS:IncludeInDebugDump="2345" DTS:Namespace="User" DTS:ObjectName="pathname"><DTS:VariableValue DTS:DataType="8" xml:space="preserve"/></DTS:Variable></DTS:Variables><DTS:ObjectData><pipeline version="1"><components><component refId="Package\Sequence Container\DFT-SampleCount\Flat File Source" componentClassID="DTSAdapter.FlatFileSource.3" contactInfo="Flat File Source;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1" description="Flat File Source" localeId="6153" name="Flat File Source" usesDispositions="true" version="1"><properties><property dataType="System.Boolean" description="Specifies whether zero-length columns are treated as null." name="RetainNulls">false</property><property dataType="System.String" description="Specifies the name of an output column containing the file name. If no name is specified, no output column containing the file name will be generated." name="FileNameColumnName"/></properties><connections><connection refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Connections[FlatFileConnection]" connectionManagerID="Package.ConnectionManagers[SampleFile]" connectionManagerRefId="Package.ConnectionManagers[SampleFile]" name="FlatFileConnection"/></connections><outputs><output refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output]" name="Flat File Source Output"><outputColumns><outputColumn refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output].Columns[Name]" codePage="1252" dataType="str" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" externalMetadataColumnId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output].ExternalColumns[Name]" length="50" lineageId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output].Columns[Name]" name="Name" truncationRowDisposition="FailComponent"><properties><property dataType="System.Boolean" description="Indicates whether the column uses the faster, locale-neutral parsing routines." name="FastParse">false</property><property dataType="System.Boolean" description="Indicates whether the data is in binary format." name="UseBinaryFormat">false</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="True"><externalMetadataColumn refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output].ExternalColumns[Name]" codePage="1252" dataType="str" length="50" name="Name"/></externalMetadataColumns></output><output refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output]" isErrorOut="true" name="Flat File Source Error Output"><outputColumns><outputColumn refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[Flat File Source Error Output Column]" codePage="1252" dataType="text" description="Flat File Source Error Output Column" lineageId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[Flat File Source Error Output Column]" name="Flat File Source Error Output Column"/><outputColumn refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[ErrorCode]" dataType="i4" lineageId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[ErrorCode]" name="ErrorCode" specialFlags="1"/><outputColumn refId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[ErrorColumn]" dataType="i4" lineageId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Error Output].Columns[ErrorColumn]" name="ErrorColumn" specialFlags="2"/></outputColumns><externalMetadataColumns/></output></outputs></component><component refId="Package\Sequence Container\DFT-SampleCount\Row Count" componentClassID="DTSTransform.RowCount.3" contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0" description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status." name="Row Count"><properties><property dataType="System.String" description="Specifies the variable to hold the row count." name="VariableName">User::rowcount</property></properties><inputs><input refId="Package\Sequence Container\DFT-SampleCount\Row Count.Inputs[Row Count Input 1]" hasSideEffects="true" name="Row Count Input 1"><externalMetadataColumns/></input></inputs><outputs><output refId="Package\Sequence Container\DFT-SampleCount\Row Count.Outputs[Row Count Output 1]" name="Row Count Output 1" synchronousInputId="Package\Sequence Container\DFT-SampleCount\Row Count.Inputs[Row Count Input 1]"><externalMetadataColumns/></output></outputs></component></components><paths><path refId="Package\Sequence Container\DFT-SampleCount.Paths[Flat File Source Output]" endId="Package\Sequence Container\DFT-SampleCount\Row Count.Inputs[Row Count Input 1]" name="Flat File Source Output" startId="Package\Sequence Container\DFT-SampleCount\Flat File Source.Outputs[Flat File Source Output]"/></paths></pipeline></DTS:ObjectData></DTS:Executable></DTS:Executables></DTS:Executable></DTS:Executables><DTS:DesignTimeProperties><?xml version="1.0"?> <!--This CDATA section contains the layout information of the package. The section includes information such as (x,y) coordinates, width, and height.--> <!--If you manually edit this section and make a mistake, you can delete it. --> <!--The package will still be able to load normally but the previous layout information will be lost and the designer will automatically re-arrange the elements on the design surface.--> <Objects Version="sql11"> <!--Each node below will contain properties that do not affect runtime behavior.--> <Package design-time-name="Package"> <LayoutInfo> <GraphLayout Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph"> <NodeLayout Size="151,42" Id="Package\Sequence Container\DFT-SampleCount" TopLeft="74,41" /> <ContainerLayout HeaderHeight="43" IsExpanded="True" PanelSize="409,224" Size="409,267" Id="Package\Sequence Container" TopLeft="222,99" /> </GraphLayout> </LayoutInfo> </Package> <TaskHost design-time-name="Package\Sequence Container\DFT-SampleCount"> <LayoutInfo> <GraphLayout Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph" xmlns:mssgle="clr-namespace:Microsoft.SqlServer.Graph.LayoutEngine;assembly=Microsoft.SqlServer.Graph" xmlns:assembly="http://schemas.microsoft.com/winfx/2006/xaml"> <NodeLayout Size="151,42" Id="Package\Sequence Container\DFT-SampleCount\Flat File Source" TopLeft="207,121" /> <NodeLayout Size="130,42" Id="Package\Sequence Container\DFT-SampleCount\Row Count" TopLeft="279,268" /> <EdgeLayout Id="Package\Sequence Container\DFT-SampleCount.Paths[Flat File Source Output]" TopLeft="282.5,163"> <EdgeLayout.Curve> <mssgle:Curve StartConnector="{assembly:Null}" EndConnector="61.5,105" Start="0,0" End="61.5,97.5"> <mssgle:Curve.Segments> <mssgle:SegmentCollection Capacity="5"> <mssgle:LineSegment End="0,48.5" /> <mssgle:CubicBezierSegment Point1="0,48.5" Point2="0,52.5" Point3="4,52.5" /> <mssgle:LineSegment End="57.5,52.5" /> <mssgle:CubicBezierSegment Point1="57.5,52.5" Point2="61.5,52.5" Point3="61.5,56.5" /> <mssgle:LineSegment End="61.5,97.5" /> </mssgle:SegmentCollection> </mssgle:Curve.Segments> </mssgle:Curve> </EdgeLayout.Curve> <EdgeLayout.Labels> <EdgeLabelCollection /> </EdgeLayout.Labels> </EdgeLayout> </GraphLayout> </LayoutInfo> </TaskHost> </Objects></DTS:DesignTimeProperties></DTS:Executable>' AS XML);
SELECT *
FROM #SSISTest st;- This topic was modified 4 years, 6 months ago by Phil Parkin. Reason: Fix typo
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Try this
SELECT v.n2.value('(@*:ObjectName)[1]','varchar(30)') AS Name,
p.n1.value('(@*:ObjectName)[1]','varchar(30)') AS Scope
FROM #SSISTest st
CROSS APPLY st.PackageXML.nodes('//*:Executable') AS p(n1)
CROSS APPLY p.n1.nodes('*:Variables/*:Variable') as v(n2);____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
- Mark Cowne wrote:
Try this
SELECT v.n2.value('(@*:ObjectName)[1]','varchar(30)') AS Name,
p.n1.value('(@*:ObjectName)[1]','varchar(30)') AS Scope
FROM #SSISTest st
CROSS APPLY st.PackageXML.nodes('//*:Executable') AS p(n1)
CROSS APPLY p.n1.nodes('*:Variables/*:Variable') as v(n2);Straightforward and works perfectly, thanks very much for your help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Mark Cowne wrote:
Try this
SELECT v.n2.value('(@*:ObjectName)[1]','varchar(30)') AS Name,
p.n1.value('(@*:ObjectName)[1]','varchar(30)') AS Scope
FROM #SSISTest st
CROSS APPLY st.PackageXML.nodes('//*:Executable') AS p(n1)
CROSS APPLY p.n1.nodes('*:Variables/*:Variable') as v(n2);Man... I remember you helping me with parsing items in procedure cache for which statistics were being used. I have to ask again, how did you learn XML so well, especially as to how it pertains to SQL Server? Since XML in SQL Server doesn't seem to be going away, I should probably learn much more about it that my currently stumbling knowledge.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply