Главная

Выполнение пакетов в SSIS при помощи T-SQL

Начиная с SQL Server 2012 структура проектов SSIS претерпела существенные изменения, путем перехода от модели управления единичными пакетами (package deployment model) к модели управления проектами (project deployment model). Последний подход упрощает и унифицирует разработку, позволяя группировать логически связанные пакеты в проекты и управлять ими как общей единицей. При развертывании проекта на сервер, все его компоненты размещаются в общей базе данных SSISDB.

Сегодняшняя статья будет посвящена процессу выполнения пакетов, развернутых в SSIS при помощи project deployment model.

В SSIS, начиная с 2012 версии обновился компонент Execute package task. Для запуска пакета, находящихся в одном проекте можно воспользоваться данным компонентом с опцией Project Reference. В выпадающем списке PackageNameFromProjectReference отобразятся все пакеты текущего проекта. Перейдя на вкладку Parameter Bindings можно задать значения переменных запускаемого пакета. Особых комментариев по работе данного компонента не требуется, за исключением одного. Данные компонент не позволяет выполнить пакет из другого проекта.
Чтобы  запустить пакет из другого проекта можно воспользоваться одним из следующих способов:
  • T-SQL
  • Утилиты dtexec и dtexecUI
  • Программный код на .Net
Остановимся на первом способе. В базе данных SSISDB имеются следующие процедуры по запуску пакетов, расположенных на сервере:
  • [catalog].[create_execution] - создает контекст выполнения пакета, определяя параметры запускаемого пакета
  • [catalog].[set_execution_parameter_value] - позволяет изменить значения переменных запускаемого пакета
  • [catalog].[start_execution] - запускает процедуру выполнения пакета
По умолчанию пакеты на сервере выполняются в асинхронном режиме. Для того, чтобы изменить данное поведение, необходимо изменить значение переменной выполнения для пакета SYNCHRONIZED в значение true.

Отдельно стоит отметить, какие типы переменных существуют для пакета. В SSISDB они имеют собственные коды (object_type):
  • 20: переменная уровня проекта
  • 30: переменная уровня пакета
  • 50: переменная выполнения
На сервере SSIS также имеется такая сущность как окружение (enviroment). Она позволяет задать набор значений переменных уникальный для среды выполнения. Для этого необходимо создать окружение и определить набор переменных окружения. Затем необходимо привязать окружение к проекту (выбрать контекстное меню нужного проекта и нажать Configure... ) и выполнить привязку переменных окружения к переменным проекта. После этого пакет можно выполнять не определяя значение конкретных параметров, а лишь выбрав определенное окружение.

Реализуем процедуру, которая будет запускать пакет на сервере, инкапсулируя внутренние процедуры сервера.
Сперва создадим собственный табличный тип данных для хранения определения переменных пакета.
CREATE TYPE [dbo].[PackageParamListType] AS TABLE(
 [ObjectType] [int] NULL CHECK ([ObjectType] in(20, 30, 50)),
 [ParameterName] [nvarchar](128) NULL,
 [ParameterValue] [sql_variant] NULL
)
Ниже приведен код процедуры выполнения пакета:
CREATE PROCEDURE [dbo].[LaunchPackageExecutionInSSISCatalog]
         @PackageName NVARCHAR(255)
       , @ProjectFolder NVARCHAR(255)
       , @ProjectName NVARCHAR(255)
       , @EnviromentName NVARCHAR(255) = NULL
    , @ParameterList PackageParamListType  READONLY
AS
BEGIN TRY
    
 /* Определяем значение окружения проекта, если оно не задано */   
 DECLARE @EnvironmentID AS INT;

 IF(@EnviromentName is not null)
  SELECT  @EnvironmentID = e.reference_id
  FROM SSISDB.catalog.environment_references e 
   INNER JOIN catalog.projects p on e.project_id = p.project_id
  WHERE e.[environment_name] = @EnviromentName
  AND  p.name = @ProjectName
    
 DECLARE @ExecutionId BIGINT = 0;

 EXEC [SSISDB].[catalog].[create_execution]
        @package_name=@PackageName,
        @execution_id=@ExecutionId OUTPUT,
        @folder_name=@ProjectFolder,
        @project_name=@ProjectName,
  @reference_id = @EnvironmentID,
        @use32bitruntime=False;

 /*
 object_type list:
                    20: project level variables
                    30: package level variables
                    50: execution parameter
   
    Определяем параметр выполнения пакета  SYNCHRONIZED
 
 Так же есть параметры:
  DUMP_ON_EVENT
  DUMP_EVENT_CODE
  LOGGING_LEVEL
  DUMP_ON_ERROR
 */

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
                @ExecutionId, 
                @object_type=50,
                @parameter_name=N'SYNCHRONIZED',
                @parameter_value=1; -- true

 /* Определяем пользовательские параметры выполнения пакета */
 
 DECLARE @objectType int
 DECLARE @parameterName nvarchar(128)
 DECLARE @parameterValue sql_variant
 DECLARE @parameterId int

 DECLARE paramCursor CURSOR FOR 
 SELECT ObjectType, ParameterName, ParameterValue FROM @ParameterList

 OPEN paramCursor

 FETCH NEXT FROM paramCursor 
 INTO @objectType, @parameterName, @parameterValue

 WHILE @@FETCH_STATUS = 0
 BEGIN 
  SELECT @parameterId = params.parameter_id 
  FROM internal.object_parameters params
   INNER JOIN internal.projects proj
    ON 
      proj.project_id = params.project_id 
     AND proj.object_version_lsn = params.project_version_lsn
     AND params.object_type = @objectType
     AND proj.name = @ProjectName
     AND object_name = @PackageName 
     AND params.parameter_name = @parameterName


  EXEC [catalog].[set_execution_parameter_value]
     @ExecutionId, 
     @object_type=@objectType,
     @parameter_name=@parameterName,
     @parameter_value=@parameterValue; 


  FETCH NEXT FROM paramCursor 
  INTO @objectType, @parameterName, @parameterValue

 END

 CLOSE paramCursor
 DEALLOCATE paramCursor
 

 /* Выполняем  пакет в синхронном режиме */
    EXEC [SSISDB].[catalog].[start_execution]
                 @ExecutionId;
 
    /* Статусы выполнения пакетов:
       1: created
                2: running
                3: cancelled
                4: failed
                5: pending
                6: ended unexpectedly
                7: succeeded
                8: stopping
                9: completed

 Если пакет выполнен неудачно выбрасываем исключение */
       
 IF EXISTS(
    SELECT *
    FROM [SSISDB].[catalog].[executions] WITH(NOLOCK)
             WHERE [execution_id] = @ExecutionId
             AND [status] NOT IN (2, 7, 9)) 
 BEGIN
      DECLARE @msg nvarchar(200) = N'Пакет ' + @PackageName + N' в проекте ' + @ProjectName + N' расположенный в папке ' + @ProjectFolder + N' выполнен неудачно';
      RAISERROR (@msg, 16, 1)
    END
END TRY
BEGIN CATCH
 THROW;
END CATCH;

Создадим тестовый SSIS проект с двумя пакетами, один из которых выполняется успешно, другой падает с ошибкой, определим для каждого пакета переменные и опубликуем проект на сервере. После публикации создадим тестовое окружение для данного проекта и привяжем его к переменным проекта.

Приступим к тестированию процедуры выполнения пакета.
Запустим оба пакета, не определяя для них никаких параметров

exec  [dbo].[LaunchPackageExecutionInSSISCatalog]
@PackageName    = N'FailPackage.dtsx',
@ProjectFolder  = N'Test',
@ProjectName    = N'SampleProject',
@EnviromentName = null

exec  [dbo].[LaunchPackageExecutionInSSISCatalog]
@PackageName    = N'SuccessPackage.dtsx',
@ProjectFolder  = N'Test',
@ProjectName    = N'SampleProject',
@EnviromentName = null
Для первого пакета процедура вернула ошибку, что было ожидаемым результатом. Процедура выбрасывает ошибку специально, для того, чтобы при определении в теле пакета данная процедура вызвала событие onError и мы могли бы решить, что делать с этой ошибкой дальше.
Окружение настроено следующим образом:

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

declare @var  [dbo].[PackageParamListType];
insert into @var values(30, N'SuccessPackageVariable', 10)

exec  [dbo].[LaunchPackageExecutionInSSISCatalog]
@PackageName    = N'SuccessPackage.dtsx',
@ProjectFolder  = N'Test',
@ProjectName    = N'SampleProject',
@EnviromentName = null,
@ParameterList = @var

exec  [dbo].[LaunchPackageExecutionInSSISCatalog]
@PackageName    = N'SuccessPackage.dtsx',
@ProjectFolder  = N'Test',
@ProjectName    = N'SampleProject',
@EnviromentName = N'SampleEnviroment'
Оба пакета выполнились успешно. Если посмотреть отчет по выполнению, то увидим, что для первого пакета значения параметров были заданы именно так, как мы и хотели.


Ну собственно и всё.
Для того, чтобы запустить пакет из другого пакета можно воспользоваться компонентом Exceute SQL Task и вставить в него вызов процедуры, аналогично тому, как это описано выше.
30 июня 2015 г.