Technical Article

List Expressions and Values of Variables in SSIS package

,

1 - Locate the target packages (this works as scripted on the raw dtsx files)

2 - copy the script to an SSMS query window

3 - Place the full path to the first such package in the script (it's in the SourcePackage CTE)

4 - Execute.

5 - Save results in whatever form you wish

6 - repeat steps 3-5 for each package you wish to document.

-- big hat tip to http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx for ther original script.
-- I've limited it to the Variables collection but the world's your oyster with XML - just adjust the Cross Apply nodes to get at other stuff.

with SourcePackage as
(
        SELECT    CAST(pkgblob.BulkColumn AS XML) pkgXML
        FROM    OPENROWSET(bulk '<insert your target package here>',single_blob) AS pkgblob
)
SELECT    'Expression'                                                        [Variable Type]
,
        Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:ObjectName)','nvarchar(max)')                    as VariableName
,        Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:Expression)','nvarchar(max)')                    as TextOrExpression
FROM    SourcePackage t

CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                        /DTS:Executable/DTS:Variables/DTS:Variable') Props(Prop)
where 
Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:EvaluateAsExpression)','nvarchar(50)') = 'True'
union all
SELECT 'Direct Text'                                                       
,   
        Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:ObjectName)','nvarchar(max)')                  
,        Props.Prop.value('.', 'nvarchar(max)')                                       
FROM    SourcePackage t

CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                        /DTS:Executable/DTS:Variables/DTS:Variable') Props(Prop)
where 
Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:EvaluateAsExpression)','nvarchar(50)') != 'True'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating