none
Getting data from Package XML RRS feed

  • Question

  • Hello

    I have 2 questions …

    1. The code below (Thanks Yitzhak), returns a packages task and connection manager, how do I return the contents of Execute SQL tasks?
    2. Can someone recommend a tutorial on querying XML documents in TSQL,  I thought when I submitted my original question, it would be a one-off, but now we’re using it, I’m getting more questions

    DECLARE @xml XML;

    SELECT @xml = XmlDoc  

    FROM OPENROWSET (BULK N'C:\ImportTest.dtsx', SINGLE_BLOB) AS Tab(XmlDoc);

    -- Data Flow Tasks

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS),  rs AS

    (

           SELECT col.value('@refId', 'VARCHAR(1024)') as refId

                 , col.value('@connectionManagerRefId', 'VARCHAR(1024)') as connectionManagerRefId

                 , col.value('@connectionManagerID', 'VARCHAR(1024)') as connectionManagerID

                 , SUBSTRING(PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(1024)'), ':', '.'), 2), 2, LEN(PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(100)'), ':', '.'), 2))-2) AS pristineGUID

                 --, TRIM('{}' FROM PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(100)'), ':', '.'), 2)) AS GUID-- SQL Server 2017 and later

           FROM @xml.nodes('(//pipeline/components/component/connections/connection)') as tab(col)

    )

    SELECT * FROM rs;

    -- Data Flow Tasks

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS),  rs AS

    (

           SELECT col.value('@refId', 'VARCHAR(100)') as refId

                 , col.value('@name', 'VARCHAR(1024)') as TaskName

                 , col.value('@componentClassID', 'VARCHAR(1024)') as ExecutableType

                 , col.value('(connections/connection/@connectionManagerRefId)[1]', 'VARCHAR(1024)') as connectionManagerRefId

                 , col.value('../@*:SqlStatementSource[1]', 'VARCHAR(1024)') as SqlStatementSource

           FROM @xml.nodes('//pipeline/components/component') as tab(col)

    )

    SELECT * FROM rs;

    Thanks in advande

    Roy

    Thursday, June 13, 2019 10:35 AM

Answers

  • Hi ryand09,

    Here we go:

    DECLARE @xml XML;
    
    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N'h:\...\MDX Test.dtsx', SINGLE_BLOB) AS Tab(XmlDoc);
    
    
    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS),  rs AS
    (
    	SELECT col.value('.', 'VARCHAR(1024)') as connectionManagerID
    		, col.value('local-name(..)[1]', 'VARCHAR(1024)') as TaskName
    		, col.value('../../../@*:ExecutableType[1]', 'VARCHAR(1024)') as ExecutableType
    		, col.value('../../../@*:ObjectName[1]', 'VARCHAR(1024)') as ObjectName
    		, col.value('../@*:SqlStatementSource[1]', 'VARCHAR(1024)') as SqlStatementSource
    	FROM @xml.nodes('(//*/@*:Connection)') as tab(col)
    )
    SELECT * FROM rs;


    Output:

    connectionManagerID	TaskName	ExecutableType	ObjectName	SqlStatementSource
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Find Test Run No	SELECT ISNULL(MAX(TestRun),0)+1 AS TestRunNo FROM MDXTestResult
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Insert Error	INSERT INTO MDXTestError (TestRun, MDXTestKey, DT, Cube, Duration, ErrorMsg) VALUES (0, 0, '2018-10-22 16:27:25.780000000',  'CentralAnalyticsCube', 0,'')
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Insert Log	INSERT INTO MDXTestResult (TestRun, MDXTestKey, DT, Cube, Duration) VALUES (0, 0, '2018-10-22 16:27:25.781000000',  'CentralAnalyticsCube', 0)

    Here is a good link on the subject:

    nodes() Method (xml Data Type)

    Thursday, June 13, 2019 5:56 PM

All replies

  • Hi Roy,

    If you're talking about the deployed packages, you can't because the content is encrypted.

    If you need to programmatically read a package flat file which is a xml file saved under .dtsx extension it will be hard as the XML design format is not publicly available yet changes between SSIS versions.

    I do not understand even why you need that.


    Arthur

    MyBlog


    Twitter

    Thursday, June 13, 2019 2:01 PM
    Moderator
  • Hi ryand09,

    Here we go:

    DECLARE @xml XML;
    
    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N'h:\...\MDX Test.dtsx', SINGLE_BLOB) AS Tab(XmlDoc);
    
    
    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS),  rs AS
    (
    	SELECT col.value('.', 'VARCHAR(1024)') as connectionManagerID
    		, col.value('local-name(..)[1]', 'VARCHAR(1024)') as TaskName
    		, col.value('../../../@*:ExecutableType[1]', 'VARCHAR(1024)') as ExecutableType
    		, col.value('../../../@*:ObjectName[1]', 'VARCHAR(1024)') as ObjectName
    		, col.value('../@*:SqlStatementSource[1]', 'VARCHAR(1024)') as SqlStatementSource
    	FROM @xml.nodes('(//*/@*:Connection)') as tab(col)
    )
    SELECT * FROM rs;


    Output:

    connectionManagerID	TaskName	ExecutableType	ObjectName	SqlStatementSource
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Find Test Run No	SELECT ISNULL(MAX(TestRun),0)+1 AS TestRunNo FROM MDXTestResult
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Insert Error	INSERT INTO MDXTestError (TestRun, MDXTestKey, DT, Cube, Duration, ErrorMsg) VALUES (0, 0, '2018-10-22 16:27:25.780000000',  'CentralAnalyticsCube', 0,'')
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	Microsoft.ExecuteSQLTask	Insert Log	INSERT INTO MDXTestResult (TestRun, MDXTestKey, DT, Cube, Duration) VALUES (0, 0, '2018-10-22 16:27:25.781000000',  'CentralAnalyticsCube', 0)

    Here is a good link on the subject:

    nodes() Method (xml Data Type)

    Thursday, June 13, 2019 5:56 PM
  • Many thanks Yitzhak. for the solution and article. 

    Friday, June 14, 2019 8:52 AM
  • Yitzhak

    I have one follow up question, 

    I use this pattern to extract the anonymous SQL code from the SQLCommand property…

    DECLARE @xml XML;

    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N'C:\Import_Data.dtsx', SINGLE_BLOB) AS Tab(XmlDoc);
    SELECT @xml.query('//pipeline/components/component/properties/property[@name="SqlCommand"]')


    How do I include this in the SQL Command in the code block you provided? I tried a few unsuccessful ways ..

    DECLARE @xml XML;

    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N' N'C:\Import_Data.dtsx ', SINGLE_BLOB) AS Tab(XmlDoc);

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS),  rs AS
    (
    SELECT col.value('.', 'VARCHAR(1024)') as connectionManagerID
    , col.value('local-name(..)[1]', 'VARCHAR(1024)') as TaskName
    , col.value('../../../@*:ExecutableType[1]', 'VARCHAR(1024)') as ExecutableType
    , col.value('../../../@*:ObjectName[1]', 'VARCHAR(1024)') as ObjectName
    , col.value('../@*:SqlStatementSource[1]', 'VARCHAR(1024)') as SqlStatementSource
    , col.value('../@*:SqlCommand[1]', 'VARCHAR(1024)') as ABC


    FROM @xml.nodes('(//*/@*:Connection)') as tab(col)
    )
    SELECT * FROM rs;
    Friday, June 14, 2019 11:50 AM
  • Hi ryand09,

    These two statements are doing two different things.

    • Mine is going after each connection as a starting point: FROM @xml.nodes('(//*/@*:Connection)') as tab(col)
      After that it retrieves Task name and SQL statement that are using that connection.
    • Yours is going and retrieves directly just …/property[@name="SqlCommand"]') SQL statement on its own and nothing else in its context.

    Friday, June 14, 2019 1:55 PM