Sql-Server

SQL 代理作業步驟的連接管理器

  • May 23, 2013

T-SQL 有沒有辦法列出 SQL 代理作業步驟中使用的連接管理器？

鑑於 SSIS 任務是 XML 文件，我原以為可以編寫 T-SQL XML 查詢來查找連接對象。

您需要在 XML 中搜尋以下實例

<DTS:ConnectionManager>

這是我擁有的項目的範例：

<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>

有幾點需要注意：

  • 頂部節點是<DTS:Executable>。這些<DTS:ConnectionManager>項目是下一個級別。
  • 上面的片段顯示了一個靜態/固定連接。如果使用表達式/動態連接，則 XML 程式碼有點不同：

0 多伺服器 {02150dee-336e-4750-9159-3c4d3f0c62c3} OLEDB @

$$ User::DynamicServerName $$

0 數據源=MyPC\SQL2008R2；初始目錄=master；提供者=SQLNCLI.1；集成安全=SSPI；自動翻譯=False；應用程序名稱=SQLOverview；

另請注意，此處的所有程式碼都已使用線上 XML 格式化程序進行格式化，以提高可讀性。實際上，這些 ConnectionManager XML 片段中的每一個都位於 .DTSX 文件的一行中。

嗯…我無法訪問 2005，但您可能可以將其用作基礎。

假設：

  • 呼叫此腳本的主體有權讀取其中的表msdb（儘管您可以從任何上下文執行腳本，不一定從 inside msdb）。

  • 感興趣的 SSIS 包是：

    • 儲存在本地 SQL Server 包儲存中
    • 在包儲存的根文件夾中（支持任意文件夾的難度很高）
    • XML 格式，未加密
    • 2008 R2 或 2012 格式（我現在可以訪問的僅有的兩種格式；我不確定這是否適用於非 R2 2008）

我對您在輸出中想要什麼做出了合理的猜測，因為只是連接管理器名稱本身並不是非常有用。

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 */

引用自：https://dba.stackexchange.com/questions/42560

相關問答

Sql-Server

由於不正確的 SET 選項“QUOTED_IDENTIFIER”，SQL 作業中的 INSERT 失敗

  • April 6, 2022
檢視問答
Sql-Server

SQL Server 代理和 sp_send_dbmail 權限失敗

  • March 6, 2022
檢視問答
Sql-Server

SQL 代理作業 - 更改所有作業步驟以在作業失敗時轉到特定步驟

  • February 10, 2022
檢視問答
Sql-Server

安排每周代理作業的下次執行日期

  • January 4, 2022
檢視問答
Ssis

將 SSIS 包作為 SQL Server 代理作業執行的問題

  • December 14, 2021
檢視問答
Sql-Server

在特定時間停止其他作業的作業

  • August 13, 2021
檢視問答