問題描述

我正在嘗試從 SQL Server 中獲取以下信息:

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

• 所有 SSIS 作業(yè)代理名稱.
• 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 個表之間的相關(guān)性，無法將所有 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 正確指出的那樣，當包名稱相同時，這可能會出現(xiàn)問題.無論如何，我一直在研究提供以下內(nèi)容的查詢:

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. 包文件夾路徑

-- 獲取作業(yè)指向的包的文件夾路徑.這將所有內(nèi)容結(jié)合在一起.

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

這篇關(guān)于使用 T-SQL 獲取 SSIS 包名稱、作業(yè)名稱和描述的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！