CODEWITHSUNDEEP
sql-serverssis
Younus Mohammed
Younus Mohammed

Reputation: 121

SSIS DB write SQL query to pull report 1) Number of times executed 2) Success/Fail/Running 3) date of execution

I need a SQL Query to run against SSISDB which should result in below format.

  1. Package Name/Project Name
  2. Number times Packages executed for Current date
  3. Status=Success/Failure/Running
  4. Execution Date

Please help.

Upvotes: 4

Views: 85

Answers (1)

MK_
MK_

Reputation: 1169

Based on the documentation, something along the lines of...

Hacky version:

SELECT
    CONCAT(package_name, '/', project_name) AS [Package Name/Project Name]
  , COUNT(execution_id) AS [Number times Packages executed for Current date]
  , FIRST_VALUE(status) OVER (PARTITION BY CONCAT(package_name, '/', project_name) ORDER BY (SELECT 1)) AS [Status]
  , CAST(start_time AS date) AS [Execution Date]
FROM catalog.executions
WHERE CAST(start_time AS date) = CAST(GETDATE() AS date)
GROUP BY CONCAT(package_name, '/', project_name), CAST(start_time AS date);

Non-hacky version:

SELECT
    CONCAT(e.package_name, '/', e.project_name) AS [Package Name/Project Name]
  , COUNT(e.execution_id) AS [Number times Packages executed for Current date]
  , st.[Status]
  , CAST(e.start_time AS date) AS [Execution Date]
FROM catalog.executions e
    CROSS APPLY (
        SELECT TOP(1) FIRST_VALUE(s_e.status) OVER (ORDER BY s_e.start_time DESC) AS [Status]
        FROM catalog.executions s_e
        WHERE CONCAT(e.package_name, '/', e.project_name) = CONCAT(s_e.package_name, '/', s_e.project_name)
            AND CAST(e.start_time AS date) = CAST(s_e.start_time AS date)
    ) st
WHERE CAST(e.start_time AS date) = CAST(GETDATE() AS date)
GROUP BY CONCAT(e.package_name, '/', e.project_name), CAST(e.start_time AS date);

Note that the status attribute is a number (described in the documentation). I also used FIRST_VALUE() function (available from SQL Server) just for the sake of it being practical but it can be avoided. Also, you might want to wrap this query in a CTE and then use a CASE statement based on Status to return the actual meaning of the number.

Upvotes: 2

Related Questions