使用 T-SQL 获取 SSIS 包名称、作业名称和描述

2021-09-10 00:00:00 tsql sql-server

我正在尝试从 SQL Server 中获取以下信息:

I'm trying to get the following information out of SQL Server:

  • 所有 SSIS 作业代理名称.
  • SSIS 包名称.
  • [可选但很高兴] SSIS 工作代理描述.

我可以使用下表毫无问题地提取所有这 3 条信息:

I'm able to pull all 3 of these pieces of information with no problems using the following tables:

  • msdb.dbo.sysjobs 为我提供了工作名称和工作描述.
  • msdb.dbo.sysssispackages 为我提供了 SSIS 包名称.

不幸的是，我无法找到这 2 个表之间的相关性，无法将所有 3 条信息放在一起.

Unfortunately I'm not able to find a correlation between those 2 tables to gets me all 3 pieces of information together.

有什么想法吗?谢谢！

推荐答案

这似乎是 Microsoft 的一个明显漏洞 - 如果 PackageId 存储在 msdb.dbo.sysjobsteps 表，而不必解析来自 Command 列的信息.正如 OP 正确指出的那样，当包名称相同时，这可能会出现问题.无论如何，我一直在研究提供以下内容的查询:

This appears to be a glaring hole by Microsoft - it would be much nicer if the PackageId were stored in the msdb.dbo.sysjobsteps table, instead of having to parse the information from the Command column. As the OP correctly pointed out, this can be problematic when package names are the same. At any rate, I have been working on a query to provide the following:

  1. 职位名称
  2. 步骤名称
  3. 包文件夹路径

-- 获取作业指向的包的文件夹路径.这将所有内容结合在一起.

-- Get the folder path of the package that a job points to. This brings it all together.

;WITH CTE1 AS (
    SELECT 
        J.job_id
        ,JobName = J.name
        ,JS.step_id, JS.step_name, JS.command
        ,StartIndex = 
            CASE 
                WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%' OR JS.command LIKE '/ISSERVER%' THEN CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) --'
                WHEN JS.command LIKE '/SERVER%' THEN CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1
                ELSE 0
            END
        ,EndIndex = 
            CASE 
                WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%'  OR JS.command LIKE '/ISSERVER%' 
                    THEN  CHARINDEX('"',JS.command, CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1)) --'
                        - CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) - 1 --'
                WHEN JS.command LIKE '/SERVER%' 
                    THEN  CHARINDEX('"',command, CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1)
                        - CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) - 1
                ELSE 0
            END
    FROM msdb.dbo.sysjobsteps JS
    INNER JOIN msdb.dbo.sysjobs J
        ON JS.job_id = J.job_id
    WHERE JS.subsystem = 'SSIS'
)    
SELECT 
    C1.job_id
    , C1.JobName
    , C1.step_id
    , C1.step_name
    , PackageFolderPath = 
        CASE 
            WHEN C1.command LIKE '/DTS%' OR C1.command LIKE '/ISSERVER%' THEN SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            WHEN C1.command LIKE '/SQL%' THEN '\MSDB' + SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            WHEN C1.command LIKE '/SERVER%' THEN '\MSDB\' + SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            ELSE NULL
        END
    , C1.command
FROM CTE1 C1
ORDER BY C1.job_id, C1.step_id

相关文章