Проблема:

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

Решение:

SQL Server Integration Services (SSIS) предоставляет базу данных SSISDB, которая включает хранимые процедуры и представления для работы с проектами SSIS и развернутыми пакетами в каталоге Integration Services. Для достижения этой цели мы можем создать SQL-объекты, которые позволят нам выполнять пакет SSIS из каталога с использованием хранимой процедуры. Такой подход позволяет нам повторно использовать существующие пакеты SSIS и выполнять их в различных точках нашего приложения.

Вот пример хранимой процедуры, которая может выполнять пакет SSIS, развернутый в каталоге SSIS:

CREATE PROCEDURE [dbo].[ExecuteSSISPackage]
    @PACKAGE_NAME NVARCHAR(128),
    @PARAMETER_NAME NVARCHAR(128),
    @PARAMETER_VALUE NVARCHAR(128)
AS
BEGIN
    DECLARE @EXECUTION_ID BIGINT;

    -- Создание выполнения пакета
    EXEC [SSISDB].[catalog].[create_execution]
        @folder_name = 'MyFolder',
        @project_name = 'MyProject',
        @package_name = @PACKAGE_NAME,
        @execution_id = @EXECUTION_ID OUTPUT;

    -- Установка значения параметра пакета
    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
        @execution_id = @EXECUTION_ID,
        @object_type = 30, -- параметр пакета
        @parameter_name = @PARAMETER_NAME,
        @parameter_value = @PARAMETER_VALUE;

    -- Запуск выполнения
    EXEC [SSISDB].[catalog].[start_execution]
        @execution_id = @EXECUTION_ID;
END

Для выполнения хранимой процедуры можно использовать следующий T-SQL:

DECLARE @P_EXECUTION_ID BIGINT;

EXEC [dbo].[ExecuteSSISPackage]
    @PACKAGE_NAME = 'MyPackage',
    @PARAMETER_NAME = 'MyParameter',
    @PARAMETER_VALUE = 'MyValue';

SELECT @P_EXECUTION_ID;

SELECT [start_time], [end_time], [status]
FROM [SSISDB].[catalog].[executions]
WHERE [execution_id] = @P_EXECUTION_ID;

Эта хранимая процедура создает выполнение для указанного пакета SSIS, устанавливает значение для параметра пакета и запускает выполнение. Идентификатор выполнения возвращается в качестве выходного параметра, который можно использовать для проверки статуса выполнения в каталоге SSIS.

Используя такой подход, мы можем выполнять пакеты SSIS из наших приложений и предоставлять значения параметров во время выполнения. Это позволяет автоматизировать процессы и интегрировать функциональность SSIS в наши приложения.

Заключение:

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