Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?

share|improve this question

migrated from serverfault.com May 20 '13 at 7:44

This question came from our site for professional system and network administrators.

1  
What connection managers are there in a job? Except for the TSQL tasks that have a database to point to, I don't remember any way to save a connection string/file/manager the same way like in an SSIS package. Can you explain what do you mean (maybe give an example of such a job)? Thanks. –  Marian May 20 '13 at 8:15
1  
What version of SQL Server? –  Cougar9000 May 20 '13 at 14:14
1  
I'm going to go on the assumption that this is related to an SSIS job step. Theoretically, you could parse the package XML if it's stored in an accessible location (like msdb). Might be an interesting endeavour. –  Jon Seigel May 20 '13 at 14:20
    
The version is 2005. I need list the details of connection manager for any file and/or oledb connection used the SSIS package that is specified in the step. –  user24019 May 21 '13 at 3:56
1  
Okay. Could you please edit the question to clarify and provide more detail? Can we assume that the package specified in the job step is stored in the local SQL Server (as opposed to in the file system, or elsewhere)? –  Jon Seigel May 21 '13 at 13:13

3 Answers 3

Given that SSIS tasks are XML files, I would have thought that it would be possible to write a T-SQL XML query to find the connection objects.

You would need to search the XML for instances of

<DTS:ConnectionManager>

Here's a sample from a project I have:

<DTS:ConnectionManager>
  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
  <DTS:Property DTS:Name="ObjectName">SQLOverview</DTS:Property>
  <DTS:Property DTS:Name="DTSID">{1e0a262f-476a-49d8-873b-acf7e3d7d46e}</DTS:Property>
  <DTS:Property DTS:Name="Description">
</DTS:Property>
  <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>
  <DTS:ObjectData>
    <DTS:ConnectionManager>
      <DTS:Property DTS:Name="Retain">0</DTS:Property>
      <DTS:Property DTS:Name="ConnectionString">Data Source=MyPC\SQL2008R2Dev;Initial Catalog=SQLOverview;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;</DTS:Property>
    </DTS:ConnectionManager>
  </DTS:ObjectData>
</DTS:ConnectionManager>

A couple of things to note:

  • The top node is <DTS:Executable>. These <DTS:ConnectionManager> items are the next level down.
  • The above snip shows a static / fixed connection. If you use an expression / dynamic connection, the XML code is a bit different:

    0 MultiServer {02150dee-336e-4750-9159-3c4d3f0c62c3} OLEDB @[User::DynamicServerName] 0 Data Source=MyPC\SQL2008R2;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;

Please also note that all the code here has been formatted using an on-line XML formatter to aid readability. In reality, each of these ConnectionManager XML snippets is on a single line of the .DTSX file.

share|improve this answer

Well... I don't have access to 2005, but you can probably use this as a base.

Assumptions:

  • The principal that calls this script has permissions to read tables in msdb (though you can run the script from any context, not necessarily from within msdb).
  • The SSIS package(s) of interest are:
    • Stored in the local SQL Server package store
    • In the root folder of the package store (very high difficulty to support arbitrary folders)
    • In XML format, unencrypted
    • Either in 2008 R2 or 2012 format (the only two formats I have access to right now; I'm not sure if this works with non-R2 2008)

I made a reasonable guess as to what you wanted in the output, as just the connection manager names aren't terribly useful by themselves.

WITH XMLNAMESPACES(N'www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT
    --e.JobId,
    e.JobName,
    --e.StepId,
    e.StepName,
    p.name AS PackageName,
    pd.ConnectionManagerName,
    pd.ConnectionType,
    pd.ConnectionString
    FROM
    (
        SELECT
            d.JobId, d.JobName, d.StepId, d.StepName,
            (
                /* Strip leading optional backslash */
                CASE WHEN LEFT(d.PackagePath, 1) = N'\' THEN
                    SUBSTRING(d.PackagePath, 2, LEN(d.PackagePath) - 1)
                ELSE
                    d.PackagePath
                END
            ) AS PackagePath
            FROM
            (
                SELECT
                    c.JobId, c.JobName, c.StepId, c.StepName,
                    (
                        /* Strip surrounding double quotes */
                        CASE WHEN LEFT(c.PackagePath, 1) = N'"' AND RIGHT(c.PackagePath, 1) = N'"' THEN
                            SUBSTRING(c.PackagePath, 2, LEN(c.PackagePath) - 2)
                        ELSE
                            c.PackagePath
                        END
                    ) AS PackagePath
                    FROM
                    (
                        SELECT
                            b.JobId, b.JobName, b.StepId, b.StepName,
                            /* Strip surrounding double quotes, and unescape escaped double quotes */
                            REPLACE
                            (
                                (
                                    CASE WHEN LEFT(b.PackagePath, 1) = N'"' AND RIGHT(b.PackagePath, 1) = N'"' THEN
                                        SUBSTRING(b.PackagePath, 2, LEN(b.PackagePath) - 2)
                                    ELSE
                                        b.PackagePath
                                    END
                                )
                                , N'\"',
                                N'"'
                            ) AS PackagePath
                            FROM
                            (
                                SELECT
                                    a.JobId, a.JobName, a.StepId, a.StepName,
                                    SUBSTRING
                                    (
                                        a.Command,
                                        a._PackagePathIndex,
                                        (
                                            CASE WHEN CHARINDEX(N' /', a.Command, a._PackagePathIndex + 1) > 0 THEN
                                                /* Another option follows */
                                                CHARINDEX(N' /', a.Command, a._PackagePathIndex + 1) - a._PackagePathIndex
                                            ELSE
                                                /* Last option in the list */
                                                LEN(a.Command)
                                            END
                                        )
                                    ) AS PackagePath
                                    FROM
                                    (
                                        SELECT
                                            j.job_id AS JobId,
                                            j.name AS JobName,
                                            js.step_id AS StepId,
                                            js.step_name AS StepName,
                                            js.command AS Command,
                                            CHARINDEX(N'/SQL ', js.command) + 5 AS _PackagePathIndex
                                            FROM [msdb]..[sysjobs] j
                                            INNER JOIN [msdb]..[sysjobsteps] js ON js.job_id = j.job_id
                                            WHERE js.subsystem = N'SSIS'
                                    ) a
                            ) b
                    ) c
            ) d
    ) e
    INNER JOIN [msdb]..[sysssispackages] p ON p.name = e.PackagePath
    CROSS APPLY
    (
        SELECT
            g.ConnectionManagerName,
            g.ConnectionType,
            g.ConnectionString
            FROM
            (
                SELECT
                    f.PackageXml,
                    CAST(CAST(f.PackageXml.query(N'/DTS:Executable[1]/DTS:Property[@DTS:Name="PackageFormatVersion"]/text()') AS nvarchar(25)) AS int) AS PackageXmlVersion
                    FROM (SELECT CAST(CAST(p.packagedata AS varbinary(MAX)) AS xml) AS PackageXml) f
            ) px
            CROSS APPLY
            (
                SELECT
                    CAST(x.query(N'./DTS:Property[@DTS:Name="ObjectName"]/text()') AS nvarchar(256)) AS ConnectionManagerName,
                    CAST(x.query(N'./DTS:Property[@DTS:Name="CreationName"]/text()') AS nvarchar(512)) AS ConnectionType,
                    CAST(x.query(N'./DTS:ObjectData[1]/DTS:ConnectionManager[1]/DTS:Property[@DTS:Name="ConnectionString"]/text()') AS nvarchar(2048)) AS ConnectionString
                    FROM px.PackageXml.nodes(N'/DTS:Executable[1]/DTS:ConnectionManager') cm(x)
                    WHERE px.PackageXmlVersion = 3 /* 2008 R2 */

                UNION ALL

                SELECT
                    x.value(N'@DTS:ObjectName', 'nvarchar(256)'),
                    x.value(N'@DTS:CreationName', 'nvarchar(512)'),
                    (
                        SELECT
                            y.value(N'@DTS:ConnectionString', 'nvarchar(2048)')
                            FROM x.nodes(N'./DTS:ObjectData[1]/DTS:ConnectionManager[1]') cp(y)
                    )
                    FROM px.PackageXml.nodes(N'/DTS:Executable[1]/DTS:ConnectionManagers[1]/DTS:ConnectionManager') cm(x)
                    WHERE px.PackageXmlVersion = 6 /* 2012 */
            ) g
    ) pd
    WHERE
        (p.packageformat = 0) AND /* XML format */
        (p.isencrypted = 0) AND
        (p.folderid = '00000000-0000-0000-0000-000000000000'); /* Look in the root only */
share|improve this answer

There is no way to do this with T-SQL; this is specific to SSIS.

share|improve this answer
    
Really? Given that SSIS tasks are XML files, I would have that it would be possible to write a T-SQL XML query to find the connection objects... –  Thomas Rushton May 21 '13 at 19:28
    
That's a good thought, put that in as another answer. –  Jason Cumberland May 22 '13 at 2:03
    
Jason - Thanks. Answer done. –  Thomas Rushton May 22 '13 at 5:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.