Querying ssis package

Question

I need to query the ssis package and get this data out , all pivot tasks and it's name and the data flow name that it's belongs to, the output column name that has a PivotKeyValue and it's PivotKeyValue. I have been struggling with this for the last 2 hours, didn't get too far. I already have ssis package contents loaded in a table with a primary xml key on it. I am pretty newbie on this :-) I was trying stuff like this below.

Thank you

WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' AS DTS
)
,step1 AS (SELECT

x.c.value ('(DTS:Property[@DTS:Name="name"])[1]','int') AS ColumnName,
ldi.c.value ('(DTS:Property[@DTS:Name="PivotKeyValue"])[1]','int') AS PivotKeyValue

FROM dbo.SSISPackages p
CROSS APPLY pkgXML.nodes ('/DTS:Executable/DTS:ObjectData') AS x(c)
CROSS APPLY x.c.nodes('property') ldi(c)
WHERE p.name = 'loadproperty'
)

SELECT TOP 10 * FROM step1

---

Gokhan Varol

Answer 1

Can you please post a valid snippet of the xml data you're trying to query? (some of us don't have a SSIS xml file available ...)

Answer 2

I cannot paste the full dtsx here, I do not know how to break it into pieces either. I uploaded the files to my skydrive, zipped and ful version, you can use them if you'd like.

zip file (600KB or so)

Zip File

dsx file(close to 10 MB)

dtsx file 10 MB

---

Gokhan Varol

Answer 3

I will try to help.  What is it that you want from this?  I have this so far.  It gets the PivotKeyValue.  But I'm not totally sure what the ColumnName is and whether you want the name of the component.

WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' AS DTS
)
,step1 AS (SELECT
x.c.query ('.') AS XMLData
 ,x.c.value ('(//DTS:Property[@DTS:Name="name"])[1]','int') AS ColumnName,
ldi.c.value ('(.)[1]','int') AS PivotKeyValue
FROM dbo.SSISPackages p
CROSS APPLY pkgXML.nodes ('/DTS:Executable//DTS:ObjectData') AS x(c)
OUTER APPLY x.c.nodes('//property[@name="PivotKeyValue"]') ldi(c)
 )
SELECT TOP 10 * FROM step1

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 4

I'd like to get the data flow name, pivot task name, pivot variable name and pivotkeyvalue

this thing is a lot harder than I thought on top of that I am not sure if I am retaining anything from this xquery

Thank you

---

Gokhan Varol

Answer 5

The following works for SQL Server SSIS 2008 R2.  This affects my reading of the structure and the componentClassID that I use to identify the Pivot Component.  I am assuming that the pkgXML column is an xml data type.

WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' AS DTS
)
,step1 AS (SELECT
x.c.query ('.') AS XMLDATA
 , x.c.value ('(DTS:Property [@DTS:Name="ObjectName"])[1]', 'varchar(200)') AS DataFlowName
 , ldi.c.value ('@name','varchar(200)') AS ComponentName
  ,col.c.value  ('@name','varchar(200)') AS ColumnName
  ,col.c.value  ('(properties/property[@name="PivotKeyValue"])[1]','int') AS PivotKeyValue
FROM dbo.SSISPackages p
CROSS APPLY pkgXML.nodes ('//DTS:Executable[@DTS:ExecutableType="SSIS.Pipeline.2"]') AS x(c)
cross APPLY x.c.nodes('DTS:ObjectData/pipeline/components/component[@componentClassID="{DD5A6456-8430-422B-9F50-7EF5A1B63CA8}"]') ldi(c)
CROSS APPLY ldi.c.nodes('outputs/output/outputColumns/outputColumn[properties/property/@name="PivotKeyValue"]') AS col(c)
 )
SELECT TOP 100 * FROM step1

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 6

Awesome, thank you so much.

What I had to do is find out which pivotkey values are used by the pivot task and put a conditional split before of pivot task, since when pivot task gets a pivotkeyvalue that is not handled inside it crashes. I like to learn more into how to query xml, but I tell you learning curve is different all this syntaxes and how to really know where the data is etc.

Yesterday I got the data with a dirty approach, I have couple of clr function for string manipulation, reading files etc, I got the data out from the xml file using below dirty query :-) (Util.FS.ReadAllTextFromFile  reads the text file, Util.dbo.ParseDelimited splits the string by delimiters creating rows with a fieldnum and field, Util.dbo.ParseDelimitedColumns128 splits string by delimiter and creates a single row up to 128 columns, Util.dbo.StringConcat (string aggregate) concatenating the string with the given delimiter, ignoring nulls).

I am not proud of what I did or recommending it at all, but I am guessing I will look into your xml query a while before I comprehend it.

Do you have any recommendations how I can speed up my learning curve and eventually be able to write efficient xml queries or at least know how to index it or tune it fore more efficient analysis. Most of the time I am going to scan through our dtsx packages to confirm things are done right, I do not have other needs for XML yet.

Do you use any xml tools to figure out the nodes etc?

Thank you Russ :-)

SELECT  b.*
INTO    Temporary.dbo.loadprop
FROM    (SELECT Util.FS.ReadAllTextFromFile ('C:\Temp\LoadProperty.dtsx') AS input) d
CROSS APPLY Util.dbo.ParseDelimited(input, '
') b

CREATE UNIQUE CLUSTERED INDEX fieldnum ON Temporary.dbo.loadprop(fieldnum)
GO
SELECT  d.FieldNum,
        REPLACE(REPLACE(g.field, '<DTS:Property DTS:Name="ObjectName">', ''), '</DTS:Property>', '') AS DataFlow,
        e.c4 AS PivotName,
        b.c2 AS PivotVal
INTO    #temp
FROM    Temporary.dbo.loadprop a
CROSS APPLY Util.dbo.ParseDelimitedColumns128(REPLACE(REPLACE(a.field, '="Notify">', '~'), '</property>', '~'), '~') b
OUTER APPLY (SELECT TOP 1
                    *
             FROM   Temporary.dbo.loadprop c
             WHERE  c.field LIKE '%{DD5A6456-8430-422B-9F50-7EF5A1B63CA8}%'
                    AND c.fieldnum < a.fieldnum
             ORDER BY fieldnum DESC) d
OUTER APPLY Util.dbo.ParseDelimitedColumns128(d.field, '"') e
OUTER APPLY (SELECT TOP 1
                    *
             FROM   Temporary.dbo.loadprop c
             WHERE  c.field = '<DTS:Property DTS:Name="CreationName">SSIS.Pipeline.2</DTS:Property>'
                    AND c.fieldnum < a.fieldnum
             ORDER BY fieldnum DESC) f
LEFT OUTER JOIN Temporary.dbo.loadprop g ON g.fieldnum = f.fieldnum - 3
WHERE   a.field LIKE '%pivotkeyvalue%'
GO
SELECT  DataFlow,
        PivotName,
        FieldNum,
        COUNT(DISTINCT PivotVal) AS Cnt,
        Util.dbo.StringConcat(DISTINCT '@Val == ' + PivotVal, ' || ') AS List
FROM    #temp
WHERE   Pivotval IS NOT NULL
GROUP BY DataFlow,
        PivotName,
        FieldNum
ORDER BY DataFlow,
        PivotName,
        FieldNum
GO

---

Gokhan Varol