Связывание идентификатора выполнения sysssislog с журналом заданий агента SSIS

У меня есть пакет служб SSIS, настроенный для ведения журнала на SQL Server. С настроенным пакетом системная таблица создается в: MyDatabase > System Tables > dbo.sysssislog

Эта таблица имеет схему, соответствующую таблица с таким же именем, хранящаяся в базе данных msdb.

В этой таблице каждое выполнение пакета имеет уникальный executionid, который определяется как:

уникальный идентификатор

GUID исполняемого экземпляра исполняемого файла, сгенерировавшего запись журнала.

Это создается каждый раз, когда пакет SSIS запускается и сохраняется в течение следующих системная переменная: System::ExecutionInstanceGUID

Пример запроса Журналы:

SELECT  [event] ,
        [source] ,
        [executionid] ,
        [starttime] ,
        [endtime]        
FROM    MyDatabase.[dbo].[sysssislog]
WHERE   [event] IN ( 'PackageEnd', 'PackageStart' )
ORDER BY id desc, starttime

Производит:

event        source   starttime            endtime              executionid
PackageEnd   Package  2017-04-10 11:12:01  2017-04-10 11:12:01  4EDBF979-5E99-44DB-AA08-839D5DCF3F2F
PackageStart Package  2017-04-10 11:12:01  2017-04-10 11:12:01  4EDBF979-5E99-44DB-AA08-839D5DCF3F2F
PackageEnd   Package  2017-04-05 13:39:11  2017-04-05 13:39:11  9E212747-3CB7-44D8-8728-9E442082DB8B
PackageStart Package  2017-04-05 13:39:11  2017-04-05 13:39:11  9E212747-3CB7-44D8-8728-9E442082DB8B

В моем приложении я использую различные Хранимые процедуры задания агента SQL Server для получения сведений о задании SSIS и истории. Например:

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'MyJobName',
    @step_id = null       
GO

Выдает (сумма столбцов, 1 исполнение = 3 строки):

job_id                                 job_name     run_date    run_time run_duration
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170410    111145   16
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170410    111200   1
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170410    111145   15
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170405    133855   16
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170405    133910   1
52916CFE-A652-4AAA-A052-738E4B349966   MyJobName    20170405    133855   15

Я создаю страницу администратора ETL в своем приложении, которая показывает историю заданий SSIS и сводку журналов, но я не могу найти способ связать журналы на основе уникального executionid с историей заданий, возвращаемой из различных систем заданий агента. Хранимые процедуры.

Есть ли способ связать executionid из sysssislog с информацией, хранящейся в истории выполнения заданий агента? Лучшее, что я могу придумать, — это использовать сопоставление даты и времени для определения журналов, наиболее близких по времени к статистике заданий агента.

Я рассмотрел возможность использования задач сценариев и запуска пользовательских событий для записи System::ExecutionInstanceGUID в историю заданий агента, но я не могу использовать задачи сценариев, поскольку они не будут работать при развертывании для клиентов с более поздними версиями SQL Server.

Обратите внимание: любые решения должны быть совместимы с 2008R2.


sql-server ssis sql-server-2008-r2 ssis-2008
person Tanner    schedule 10.04.2017    source источник
comment
Перейдите по этой ссылке, чтобы узнать, есть ли там какая-либо полезная информация...troywitthoeft.com/   -  person Isaac    schedule 10.04.2017
comment
@nscheaffer спасибо за ссылку, посмотрю.   -  person Tanner    schedule 10.04.2017

Ответы (1)


arrow_upward
0
arrow_downward

В итоге я получил следующее решение, которое не самое красивое и не самое производительное, но оно удовлетворило моим требованиям. Мне нужно было последнее n количество выполнений моего пакета ssis, указанное двумя параметрами. Я держу этот код в хранимой процедуре, которая вызывается моим приложением:

DECLARE @packageName NVARCHAR(250) = 'MySqlServerAgentJobName',
        @rowsToTake INT = 15;

WITH cte
AS (SELECT TOP 15 msdb.dbo.agent_datetime(h.run_date, h.run_time) AS LastRunDate,
           h.run_status RunStatus,
           h.run_duration RunDurationSeconds,
           (
               SELECT TOP 1 executionid
               FROM [dbo].[sysssislog]
               WHERE starttime >= msdb.dbo.agent_datetime(h.run_date, h.run_time)
                     AND [event] = ('PackageStart')
           ) ExecutionId
    FROM msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
    WHERE j.name = @packageName AND h.step_name = '(Job outcome)'
    ORDER BY LastRunDate DESC
   )
SELECT cte.LastRunDate,
       cte.RunStatus,
       cte.RunDurationSeconds,
       cte.ExecutionId
FROM cte

Он использует встроенную функцию для анализа действительного datetime из полей задания агента:

msdb.dbo.agent_datetime(h.run_date, h.run_time) AS LastRunDate

Он извлекает top 1 execution id из таблицы sysssislogs, фильтруя по [event] = ('PackageStart'), которая существует для каждого выполнения задания, и starttime должно быть >= LastRunDate, зарегистрированным заданием агента.

Это дает такие результаты:

LastRunDate              RunStatus  RunDurationSecs  ExecutionId
2017-07-12 12:32:18.000  1          210              99A8A715-890D-4115-975F-AC3DA660770D
2017-07-12 12:00:01.000  1          215              D152C1C6-530F-45D6-B962-41784EC7D9E5
2017-07-12 11:22:13.000  1          204              BBAC73C1-1600-477E-AFC9-1F66D7CFD07A
2017-07-12 10:25:44.000  1          213              A4C69B3C-462B-4000-B2BD-8C9EB47230DA
2017-07-12 10:16:19.000  1          230              3D4D8572-E4CC-4B70-875A-C5D3B513A480
2017-07-12 07:33:21.000  1          244              D8283955-B3EB-4E6D-BFDC-8D7C0D954574
2017-07-11 12:00:00.000  1          211              553FC580-54B2-490C-BB1D-6B1F9BB35C84
2017-07-10 16:21:16.000  1          212              B9A78077-A04C-49AC-A4C6-EF6AB636D862
2017-07-10 16:13:18.000  1          255              DFC853D8-67F5-43CE-B8A4-00B846425C6B
2017-07-10 14:04:00.000  1          217              8C6861B1-6CDE-44FD-8606-18F24F339C05
2017-07-10 13:58:27.000  1          224              5A05011C-9F14-4098-B579-0E75C1E76BDB
2017-07-10 13:53:14.000  1          231              9A2B52FB-2BD4-4FAA-A4A0-40AC48A41326
2017-07-10 13:42:06.000  1          210              9BDBB290-1C84-49ED-87E6-97522DB4AB81
2017-07-10 12:06:44.000  1          215              F4D0A847-F7E4-4F2B-B328-58C387D2C50E
2017-07-10 12:00:01.000  1          241              5EC52369-9832-4718-AF92-2C502D454A41
person Tanner    schedule 12.07.2017