none
Connection manager usage RRS feed

  • Question

  • Hello

    I have a package that uses multiple connection managers.  The package has 9 containers, each of which have 1 or more data flow tasks.

    I need to know which connection manager is being use in each task, two are due to be replaced

    Is there an easy way to do this besides manually go through each task or trying to read the xml file?

    Thanks

    Roy

    Thursday, May 16, 2019 2:29 PM

Answers

  • Hi ryan09,

    Here is how to cover SSIS Data Flow:

    DECLARE @xml XML;
    
    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N'c:\~\SQLStatement4000CharsLimit.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(100)') as refId
    		, col.value('@connectionManagerRefId', 'VARCHAR(100)') as connectionManagerRefId
    		, col.value('@connectionManagerID', 'VARCHAR(100)') as connectionManagerID
    		, SUBSTRING(PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(100)'), ':', '.'), 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-- 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;
    • Marked as answer by ryand09 Saturday, May 25, 2019 12:26 PM
    Wednesday, May 22, 2019 12:12 PM

All replies

  • Hi Ryan09,

    Because XML packages are XML files, it is very easy to query them as virtual DB table on the files system.

    Here is a starting point for you:

    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('@connectionManagerRefId', 'VARCHAR(100)') as connectionManagerRefId
    	FROM @xml.nodes('(//pipeline/components/component/connections/connection)[1]') as tab(col)
    )
    SELECT * FROM rs;
    Thursday, May 16, 2019 2:43 PM
  • Hi ryan09,

    A more elaborate SQL:

    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('@connectionManagerRefId', 'VARCHAR(100)') as connectionManagerRefId
    		, col.value('@connectionManagerID', 'VARCHAR(100)') as connectionManagerID
    		, SUBSTRING(PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(100)'), ':', '.'), 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-- 2017 and later
    	FROM @xml.nodes('//pipeline/components/component/connections/connection') as tab(col)
    )
    SELECT * FROM rs;
    
    
    ;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('../@*:SqlStatementSource[1]', 'VARCHAR(1024)') as SqlStatementSource
    	FROM @xml.nodes('(//*/@*:Connection)') as tab(col)
    )
    SELECT * FROM rs;
    


    Output #1:

    connectionManagerRefId	connectionManagerID	pristineGUID	GUID
    Project.ConnectionManagers[SQLDBConnection]	{9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}:external	9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16	9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16

    Output #2:

    connectionManagerID	TaskName	SqlStatementSource
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	SELECT ISNULL(MAX(TestRun),0)+1 AS TestRunNo FROM MDXTestResult
    {9AAC5808-A1FC-4F9D-B1EF-EC7D90CB1C16}	SqlTaskData	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	INSERT INTO MDXTestResult (TestRun, MDXTestKey, DT, Cube, Duration) VALUES (0, 0, '2018-10-22 16:27:25.781000000',  'CentralAnalyticsCube', 0)


    • Edited by Yitzhak Khabinsky Thursday, May 16, 2019 5:05 PM
    • Proposed as answer by Yang.Z Monday, May 20, 2019 4:36 AM
    Thursday, May 16, 2019 3:23 PM
  • Many thanks for the quick reply.

    Your code returns the SQL and connectionManagerID ( DTS:DTSID)  from any SqlTaskData.

    This is doing a lot of what I need, but the connection managers I’m replacing are also used in data flow tasks (Source and Destination). Is there a modification to your code that would return these tasks also?

    Regards

    Wednesday, May 22, 2019 10:52 AM
  • Hi ryan09,

    Here is how to cover SSIS Data Flow:

    DECLARE @xml XML;
    
    SELECT @xml = XmlDoc   
    FROM OPENROWSET (BULK N'c:\~\SQLStatement4000CharsLimit.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(100)') as refId
    		, col.value('@connectionManagerRefId', 'VARCHAR(100)') as connectionManagerRefId
    		, col.value('@connectionManagerID', 'VARCHAR(100)') as connectionManagerID
    		, SUBSTRING(PARSENAME(REPLACE(col.value('@connectionManagerID', 'VARCHAR(100)'), ':', '.'), 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-- 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;
    • Marked as answer by ryand09 Saturday, May 25, 2019 12:26 PM
    Wednesday, May 22, 2019 12:12 PM
  • Perfect,

    Just what  I needed

    Thanks Yitzhak

    Saturday, May 25, 2019 12:26 PM