Время выполнения пакетов ETL - скрипт

Я использую приведенный ниже скрипт для расчета времени выполнения пакетов ETL:

DECLARE @DATE DATE = GETDATE() - 7

SELECT
      [folder_name]
      ,[project_name]
      ,[package_name]
      ,CAST([start_time] AS datetime) AS [start_time]
      ,DATEDIFF(minute, [start_time], [end_time]) AS 'execution_time[min]'
FROM [SSISDB].[internal].[execution_info]
WHERE start_time >= @DATE
ORDER BY [start_time]

Можно ли повысить уровень детализации? Я хотел бы иметь время выполнения каждого блока ETL.


sql-server ssis etl
person Kulis    schedule 30.12.2015    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Вы можете. Следующий запрос должен возвращать продолжительность каждого исполняемого файла.

DECLARE @DATE DATE = GETDATE() - 7

SELECT  [executions].[folder_name]
      , [executions].[project_name]
      , [executions].[package_name]
      , [executable_statistics].[execution_path]
      , DATEDIFF(minute, [executable_statistics].[start_time], [executable_statistics].[end_time]) AS 'execution_time[min]'
FROM [SSISDB].[catalog].[executions]
INNER JOIN [SSISDB].[catalog].[executable_statistics]
    ON [executions].[execution_id] = [executable_statistics].[execution_id]
WHERE [executions].[start_time] >= @DATE
ORDER BY [executable_statistics].[start_time]

Дополнительную информацию о различных представлениях можно найти в каталоге SSISDB здесь.

person JodyT    schedule 30.12.2015

arrow_upward
1
arrow_downward

Другое решение:

Use SSISDB
DECLARE @DATE DATE = GETDATE() -7

SELECT     
            CAST(MSG.message_time AS datetime) AS message_time
            ,CASE message_source_type
                WHEN 10 THEN 'Entry APIs, such as T-SQL and CLR Stored procedures'
                WHEN 20 THEN 'External process used to run package (ISServerExec.exe)'
                WHEN 30 THEN 'Package-level objects'
                WHEN 40 THEN 'Control Flow tasks'
                WHEN 50 THEN 'Control Flow containers'
                WHEN 60 THEN 'Data Flow task'
            END AS message_source_type
            ,CAST(start_time AS datetime) AS start_time
            ,OPR.object_name
            ,LEFT(message, CHARINDEX(':', message) -1) AS Block
            ,CONVERT(TIME(0), LEFT(RIGHT(message, 13),12)) AS execution_time
FROM        catalog.operation_messages AS MSG
INNER JOIN  catalog.operations AS OPR
    ON      OPR.operation_id = MSG.operation_id
WHERE 
    start_time > @DATE 
    and message like '%:Finish%' 
    and message not like 'INSERT Record SQL Task:Finished%'
    and message_source_type <> 30
    and message_source_type <> 50
--ORDER BY message_time DESC
ORDER BY execution_time DESC
person Kulis    schedule 30.12.2015