请求提供SSISDB查询SQL：获取包执行次数、状态及日期报表

Hey there, I’ve got a SQL query ready for your SSISDB that’ll generate exactly the report you need. It pulls all the details you asked for—package/project names, daily execution counts, statuses, and run dates.

This query joins core SSISDB catalog tables to tie executions to their parent packages and projects, maps numeric status codes to human-readable labels, and aggregates counts by day, package, and status.

SELECT
    CONCAT(pj.name, '/', pkg.name) AS [包名称/项目名称],
    COUNT(exec.execution_id) AS [当日包执行次数],
    CASE exec.status
        WHEN 1 THEN '运行中'
        WHEN 2 THEN '成功'
        WHEN 3 THEN '失败'
        WHEN 4 THEN '已取消'
        ELSE '未知状态'
    END AS [状态（成功/失败/运行中）],
    CAST(exec.start_time AS DATE) AS [执行日期]
FROM
    SSISDB.catalog.executions exec
INNER JOIN
    SSISDB.catalog.packages pkg ON exec.package_id = pkg.package_id
INNER JOIN
    SSISDB.catalog.projects pj ON pkg.project_id = pj.project_id
GROUP BY
    CONCAT(pj.name, '/', pkg.name),
    CAST(exec.start_time AS DATE),
    exec.status
ORDER BY
    [执行日期] DESC,
    [包名称/项目名称],
    [状态（成功/失败/运行中）];

Quick Notes: #

• Status Translations: The CASE statement converts SSISDB’s numeric status codes to the labels you requested, plus handles edge cases like canceled executions.
• Date Grouping: We’re using CAST(exec.start_time AS DATE) to group executions by calendar day. If you need hourly or weekly counts, adjust this to DATEPART(hour, exec.start_time) or DATEPART(week, exec.start_time) respectively.
• Filtering: To narrow results to a specific date range, add a WHERE clause after the joins. For example:

  WHERE CAST(exec.start_time AS DATE) BETWEEN '2024-01-01' AND '2024-01-31'
  

• Permissions: Ensure your account has read access to the SSISDB catalog (the ssis_admin role usually covers this, or you can grant explicit SELECT permissions on the catalog tables).

内容的提问来源于stack exchange，提问作者Younus Mohammed