При работе с пакетами SQL Server Integration Services (SSIS) часто возникают сбои выполнения пакетов. Для устранения этих сбоев важно получить сообщения об ошибках, связанные с выполнением пакета. Однако процесс получения сообщений об ошибках отличается в зависимости от того, был ли пакет развернут с использованием Business Intelligence Development Studio (BIDS) или SQL Server Data Tools (SSDT).
Ранее, когда пакеты развертывались с использованием BIDS, сведения о выполнении пакета хранились в той же базе данных (msdb), что и сведения о выполнении задания SQL Server Agent. Это упрощало получение сведений о выполнении пакета и настройку пользовательских служб уведомлений для информирования разработчиков об ошибках.
Однако с появлением SSDT была введена новая база данных с названием SSISDB для хранения сведений о выполнении пакетов. Это означает, что при работе с пакетами, развернутыми с использованием SSDT, нам необходимо выполнять запросы к базе данных SSISDB для получения сведений о выполнении пакета.
Рассмотрим пример, в котором у нас есть пакет SSIS, развернутый с использованием BIDS, и другой пакет, развернутый с использованием SSDT. Если мы выполним пакет, развернутый с использованием BIDS, с помощью задания SQL Server Agent, сведения о сбое выполнения пакета будут предоставлены в журнале истории задания SQL Server Agent. Мы легко можем настроить пользовательскую службу уведомлений для отправки электронных писем с подробностями об ошибках.
Однако, если мы выполним пакет, развернутый с использованием SSDT, с помощью задания SQL Server Agent, сообщение об ошибке, записанное в журнале задания SQL Server Agent, будет короче и будет указывать нам на поиск подробностей о выполнении пакета в отчете “Все выполнения” каталога служб интеграции. Это означает, что нам может потребоваться предоставить доступ к экземпляру SSISDB для устранения ошибки.
Чтобы получить сообщения об ошибках, связанные с пакетом, развернутым с использованием SSDT, мы можем выполнить следующие шаги:
- Получить последний идентификатор выполнения из таблицы [SSISDB].[internal].[executions].
- Получить сообщения об ошибках с использованием идентификатора выполнения из представления [SSISDB].[catalog].[event_messages].
- Настроить задание SQL Server Agent для включения шага задания, который отправляет сообщения об ошибках.
Вот пример скрипта для получения последнего идентификатора выполнения:
SELECT MAX([execution_id]) AS [execution_id]
FROM [SSISDB].[internal].[executions] (NOLOCK)
WHERE [package_name] = 'PackageTest.dtsx'
А вот пример скрипта для получения сообщений об ошибках:
SELECT event_message_id, event_name, message_time, [message], message_source_name, execution_path
FROM [SSISDB].[catalog].[event_messages] em
WHERE em.operation_id = (
SELECT MAX([execution_id])
FROM [SSISDB].[internal].[executions] (NOLOCK)
WHERE [package_name] = 'PackageTest.dtsx'
)
AND event_name NOT LIKE '%Validate%'
AND event_name = 'OnError'
После получения сообщений об ошибках мы можем настроить задание SQL Server Agent для включения шага задания, который отправляет сообщения об ошибках соответствующим получателям. Это можно сделать, выполнив хранимую процедуру, такую как хранимую процедуру spSendEmailISCatalog.
Следуя этим шагам, мы можем эффективно получать и обрабатывать сообщения об ошибках, связанные с пакетами, развернутыми с использованием SSDT, в заданиях SQL Server Agent.
Кроме того, вы также можете рассмотреть возможность использования SSIS Reporting Pack, который содержит список отчетов SSRS, аналогичных встроенным отчетам каталога служб интеграции. Это позволяет избежать предоставления доступа к SSISDB и вместо этого позволяет людям просматривать сведения о выполнении пакетов через отчеты SSRS.
В заключение, для получения сообщений об ошибках каталога SSIS в заданиях SQL Server Agent необходимо выполнять запросы к базе данных SSISDB и настраивать шаги задания для обработки сообщений об ошибках. Следуя описанным в этой статье шагам, вы сможете эффективно устранять сбои выполнения пакетов в ваших проектах SSIS.