Логи в SSIS

Провайдеры логов (logging providers).
Самый простой вариант организации логирования в пакетах SSIS – использование logging providers. Это решение позволяет писать логи в текстовые файлы, в базу данных или, например, в журнал операционной системы. Плюс можно гибко настраивать какие события для какого компонента будут писаться в логи. Как правило я оставляю только события OnError, дабы таблица сильно не дулась, хотя для целей отладки вполне можно фиксировать события OnPreExecute и OnPostExecute.

Обработчики событий (event handlers).
Как показывает практика, информация, которую пишут провайдеры логов, не всегда достаточно. Особенно это проявляется в случаях, когда через SSIS проходит большое число файлов для загрузки. И на основе информации, поступившей от logging provider’а, не всегда можно быстро выявить файл, с которым возникли проблемы. К счастью SSIS дает возможность работы с обработчиками событий, что позволяет значительно расширить функционал классического провайдера логов.

Решение, которое будет описано ниже позволяет фиксировать события, происходящие с каждым конкретным файлом, проходящим через пакет SSIS. В связи с тем, что для записи данных в базу в пакете SSIS будет использоваться SQL Task, то логика будет реализована с помощью хранимой процедуры.

Для начала создается таблица, в которой будут храниться логи:

CREATE TABLE [Logging].[UploadedFiles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File Name] [nvarchar](255) NOT NULL,
[Task ID] [nvarchar](255) NOT NULL,
[Task Name] [nvarchar](255) NOT NULL,
[Package ID] [nvarchar](255) NOT NULL,
[Package Name] [nvarchar](255) NOT NULL,
[Action Object] [nvarchar](25) NOT NULL,
[Action Flag] [int] NOT NULL,
[Action TimeStamp] [datetime] NOT NULL,
[Record Locked] [bit] NOT NULL
) ON [PRIMARY]
GO

В ней фиксируются имя загружаемого файла, идентификатор и имя задачи, идентификатор и имя пакета.
На поле [Action Object] я остановлюсь подробнее. В связи с тем, что меня интересует состояние загрузки данных в базу данных или на FTP сервер, то в поле [Action Object] падает либо строка ‘FTP’, либо ‘DB’. Естественно можно настроить систему и на мониторинг других задач, например, отправку писем, с помощью соответствующего таска SSIS. Поле [Action Flag] хранит статус выполнения: -1 – ошибка, 0 – в процессе загрузки, 1 – успешное выполнение. [Record Locked] – служебное поле, о нем расскажу чуть ниже.

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

create procedure [Logging].[sp_LogSsisEvents]
@LogFileName nvarchar(255) = null, -- Name of the file being logged
@TaskID nvarchar(255) = null,		-- ID of the component that raised event
@TaskName nvarchar(255) = null,	-- Name of the component that raised event
@PackageID nvarchar(255) = null,	-- ID of the package that raised event
@PackageName nvarchar(255) = null,	-- Name of the package that raised event
@ActionObject nvarchar(10) = null,	-- DB or FTP
@EventType nvarchar(25) = null,	-- Name of the event
@ComputerName nvarchar(100) = null	-- Computer's network name

/*	Action Object
Type of the object where action is performed:
FTP
DB
*/

/*	Event Type
Type of events that trigger logging
OnPreExecute
OnPostExecute
OnError

[Additional Events For Reporting Purposes]
null -- Return events summary total vs. errors
GetErrorsDetails
*/

/* Action Flags
-1 Failed
0 In Progress
1 Success
*/

--@ActionFlag int
as
begin
declare @sql nvarchar(MAX)
declare @sqlinsert nvarchar(MAX)
declare @sqlupdate nvarchar(MAX)
declare @filepath nvarchar(100)

-- Part common for all queries
set @sqlinsert = 'insert into [Logging].[UploadedFiles]([File Name],[Task ID],[Task Name],[Package ID],[Package Name],[Action Object],[Action Flag],[Action TimeStamp],[Record Locked]) '
set @sqlupdate = 'update  [Logging].[UploadedFiles] '

set @sql =

case
when @EventType = 'OnPreExecute' then
@sqlinsert +
'values(''' + @LogFileName +''',''' + @TaskID + ''',''' + @TaskName + ''',''' + @PackageID + ''','''
+ @PackageName + ''',''' + @ActionObject + ''',0,GETDATE(),0)'
when @EventType = 'OnPostExecute' then
@sqlupdate +
'set [Action Flag] = 1, [Record Locked] = 1
where [File Name] = ''' + @LogFileName + ''' and [Action Object] = ''' + @ActionObject + ''' and [Action Flag] <> -1 and [Record Locked] <> 1'
when @EventType = 'OnError' then
@sqlupdate +
'set [Action Flag] = -1, [Record Locked] = 1
where [File Name] = ''' + @LogFileName + ''' and [Action Object] = ''' + @ActionObject + ''' and [Record Locked] <> 1'

end
exec (@sql)
end
go

В числе прочих параметров хранимой процедуре передается параметр @EventType, который содержит информацию о том, какое событие произошло на уровне пакета SSIS. В зависимости от этого формируется динамический SQL запрос. В связи с тем, что первым из числа логируемых событий вызывается OnPreExecute, в динамическом запросе используется переменная @sqlinsert, все последующие события используют @sqlupdate, для обновления статусов.
Так как событие OnPostExecute наступает независимо от наступления события OnError, флаг [Record Locked] используется для того, чтобы исключить изменение данных в таблице.

Теперь можно вносить изменения в пакет SSIS. Для начала создаются необходимые переменные:
logActionObject_DB Значение: DB
logActionObject_FTP Значение: FTP
logEventType_OnError Значение: OnError
logEventType_OnPostExecute Значение: OnPostExecute
logEventType_OnPreExecute Значение: OnPreExecute
logFileName Значение: Присваивается динамически

Как я писал выше записи в таблице с логами будут разделяться по типу объектов, в моем случае – база данных и ФТП-сервер. Если необходимо вести логи для других объектов, для каждого из них необходимо создать свою переменную. Также потребуются переменные для каждого события – то есть OnError, OnPostExecute, OnPreExecute. Переменная logFileName формируется динамически.

Далее на вкладке Event Handlers создается обработчик для необходимых событий. Благодаря использованию хранимой процедуры он будет состоять из одного Execute SQL Task:


Execute SQL Task настраивается для вызова созданной ранее хранимой процедуры:


И для параметров процедуры мапятся соответствующие системные и пользовательские переменные:

Ключевой момент – к параметрам 5 и 6 должны мапиться переменные для соответствующего события и объекта, над которым выполняется действие (DB, FTP и т.д.)

Состояние загрузок можно посмотреть напрямую в базе или создать отчет SSRS:

Запись опубликована в рубрике Business Intelligence, TSQL с метками , , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *