CODEWITHSUNDEEP
sqlxmlvariablesssis
Tr4nce
Tr4nce

Reputation: 87

Extracting Variables from SSIS package stored in the msdb

I need to audit over 100 SSIS packages inside the msdb and I need to pull all the variables and associated values from each package. I have come up with the following script that is kind of there (only reading one package for now) so I can list everything within the DTS:Variable node but I'm not sure how just to extract each variable and value.

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as dts) 
SELECT Con.Str.value('.', 'varchar(400)')
FROM (
        SELECT CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS pkgXML
    FROM msdb.dbo.sysdtspackages90
    WHERE name = 'SSISPackageName'

        ) pkgblob
CROSS APPLY pkgXML.nodes('//dts:Variable') as Con(Str)

Sample XML

<DTS:Variable>
    <DTS:Property DTS:Name="Expression" />
    <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
    <DTS:Property DTS:Name="Namespace">User</DTS:Property>
    <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
    <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
    <DTS:VariableValue DTS:DataType="3">50000</DTS:VariableValue>
    <DTS:Property DTS:Name="ObjectName">DeleteBatchQty</DTS:Property>
    <DTS:Property DTS:Name="DTSID">{2A967BFC-BE6C-41C8-B574-6CB94D09C96E}    </DTS:Property>
    <DTS:Property DTS:Name="Description" />
    <DTS:Property DTS:Name="CreationName" />
  </DTS:Variable>

So I need ObjectName of "DeleteBatchQty" and it's value "50000" in the above example and ultimately a list of all the variables.

If anyone has the answer it would extremely appreciated.

Thank you.

Upvotes: 1

Views: 426

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

I first fill the XML into a dummy table to keep my test scenario close to your actual issue.

DECLARE @Dummy TABLE(packagedata XML);
INSERT INTO @Dummy VALUES
('<DTS:Variable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
    <DTS:Property DTS:Name="Expression" />
    <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
    <DTS:Property DTS:Name="Namespace">User</DTS:Property>
    <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
    <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
    <DTS:VariableValue DTS:DataType="3">50000</DTS:VariableValue>
    <DTS:Property DTS:Name="ObjectName">DeleteBatchQty</DTS:Property>
    <DTS:Property DTS:Name="DTSID">{2A967BFC-BE6C-41C8-B574-6CB94D09C96E}    </DTS:Property>
    <DTS:Property DTS:Name="Description" />
    <DTS:Property DTS:Name="CreationName" />
  </DTS:Variable>');

  ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) 
   SELECT Con.Str.value('(DTS:Property[@DTS:Name="Expression"])[1]','varchar(max)') AS Expression
         ,Con.Str.value('(DTS:Property[@DTS:Name="EvaluateAsExpression"])[1]','varchar(max)') AS EvaluateAsExpression
         ,Con.Str.value('(DTS:Property[@DTS:Name="Namespace"])[1]','varchar(max)') AS Namespace
         ,Con.Str.value('(DTS:Property[@DTS:Name="ReadOnly"])[1]','varchar(max)') AS ReadOnly
         ,Con.Str.value('(DTS:Property[@DTS:Name="RaiseChangedEvent"])[1]','varchar(max)') AS RaiseChangedEvent
         ,Con.Str.value('(DTS:VariableValue)[1]','varchar(max)') AS VariableValue
         ,Con.Str.value('(DTS:Property[@DTS:Name="ObjectName"])[1]','varchar(max)') AS ObjectName
         ,Con.Str.value('(DTS:Property[@DTS:Name="DTSID"])[1]','varchar(max)') AS DTSID
         ,Con.Str.value('(DTS:Property[@DTS:Name="Description"])[1]','varchar(max)') AS Description
         ,Con.Str.value('(DTS:Property[@DTS:Name="CreationName"])[1]','varchar(max)') AS CreationName
   FROM (
        SELECT CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS pkgXML
        FROM @Dummy
        ) pkgblob
CROSS APPLY pkgXML.nodes('/DTS:Variable') as Con(Str)

You may chose your fitting datatypes and not a varchar(max) in all places...

Upvotes: 1

Related Questions