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

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

В сочетании с данными журналов это оказалось очень эффективным способом управления пакетами с использованием модели развертывания проекта.

Пример:

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

create table dbo.PackageVariableValues
(PackageName NVARCHAR(200)
, VariableName NVARCHAR(200)
, VariableValue NVARCHAR(200)
)

create proc dbo.spGetVariableValues
@packageName NVARCHAR(200)
as
SELECT VariableName, VariableValue
FROM dbo.PackageVariableValues
WHERE PackageName = @packageName

insert into dbo.PackageVariableValues
select 'Package', 'strVariable1', 'NewValue'
union all select 'Package', 'intVariable2', '1000'

Сам пакет в этом примере будет содержать только задачу «Сценарий» и пару переменных, которые мы установим во время выполнения.

У меня есть две переменные, strVariable1 и intVariable2. Эти имена переменных сопоставляются с данными строки, которые я вставил в таблицу.

В задаче «Сценарий» я передаю PackageName и TaskName как переменные только для чтения и переменные, которые будут установлены как чтение-запись.

Код в задаче сценария выполняет следующие действия:

• Устанавливает строку подключения на основе указанного диспетчера подключений
• Создает вызов хранимой процедуры
• Выполняет хранимую процедуру и собирает ответ
• Итерирует по каждой строке, задавая имя и значение переменной
• Используя команду try / catch / finally, сценарий возвращает некоторые сведения о журнале, а также соответствующие сведения в случае сбоя.

Как я упоминал ранее, я использую библиотеку OLEDB для подключения к SQL и выполнения процедур.

Вот код задачи скрипта:

public void Main()
{
    string strPackageName;

    strPackageName = Dts.Variables["System::PackageName"].Value.ToString();

    string strCommand = "EXEC dbo.spGetVariableValues '" + strPackageName + "'";

    bool bFireAgain = false;

    OleDbDataReader readerResults;

    ConnectionManager cm = Dts.Connections["localhost"];
    string cmConnString = cm.ConnectionString.ToString();

    OleDbConnection oleDbConn = new OleDbConnection(cmConnString);

    OleDbCommand cmd = new OleDbCommand(strCommand);
    cmd.Connection = oleDbConn;

    Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "All necessary values set. Package name: " + strPackageName + " Connection String: " + cmConnString, String.Empty, 0, ref bFireAgain);

    try
    {
        oleDbConn.Open();
        readerResults = cmd.ExecuteReader();

        if (readerResults.HasRows)
        {
            while (readerResults.Read())
            {

                var VariableName = readerResults.GetValue(0);
                var VariableValue = readerResults.GetValue(1);

                Type VariableDataType = Dts.Variables[VariableName].Value.GetType();
                Dts.Variables[VariableName].Value = Convert.ChangeType(VariableValue, VariableDataType);

            }

            Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "Completed assigning variable values.  Closing connection", String.Empty, 0, ref bFireAgain);
        }
        else
        {
            Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "The query did not return any rows", String.Empty, 0);
        }
    }
    catch (Exception e)
    {

        Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "There was an error in the script.  The messsage returned is: " + e.Message, String.Empty, 0);
    }
    finally
    {
        oleDbConn.Close();
    }
}

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

            var VariableName = readerResults.GetValue(0);
            var VariableValue = readerResults.GetValue(1);

Во-вторых, поскольку столбец VariableValues ​​в таблице может содержать данные, которые нужно вводить по-другому, когда он попадает в переменную, я беру тип данных переменной и выполняю преобразование значения, чтобы убедиться, что оно совпадает. Поскольку это делается в рамках команды try / catch, в результате сбоя будет возвращено сообщение о преобразовании, которое я вижу в выходных данных.

            Type VariableDataType = Dts.Variables[VariableName].Value.GetType();
            Dts.Variables[VariableName].Value = Convert.ChangeType(VariableValue, VariableDataType);

Теперь результаты (через окно просмотра):

До

После

В сценарии я использую fireInformation для возврата обратной связи от задачи сценария, а также любые fireError в блоках catch. Это обеспечивает читабельность вывода во время отладки, а также при просмотре таблицы сообщений выполнения SSISDB (или отчетов о выполнении).

Чтобы показать пример вывода ошибки, вот неправильное значение, переданное из процедуры, которое не приведет к преобразованию.

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

При настройке пакета SSIS у вас есть 3 варианта: использовать значения времени разработки, вручную редактировать значения и использовать среду.

Подход 1

Я добился успеха со смесью двух последних. Я создаю папку: Configuration и отдельную среду, Settings. Никакие проекты не развертываются в Configuration.

Я заполняю среду настроек всем, что может использоваться в разных проектах. Строки подключения к базе данных, пользователи и пароли ftp, общие места обработки файлов и т. Д.

В каждом развернутом проекте все, что нам нужно настроить, обрабатывается с помощью явных переопределений. Например, имя файла изменяется в зависимости от среды, поэтому мы бы установили значение через редактор, но вместо того, чтобы нажимать ОК, мы нажимаем кнопку «Сценарий» вверху. Это вызывает звонок вроде

DECLARE @var sql_variant = N'DEV_Transpo*.txt';
EXEC SSISDB.catalog.set_object_parameter_value
    @object_type = 20
,   @parameter_name = N'FileMask'
,   @object_name = N'LoadJobCosting'
,   @folder_name = N'Accounting'
,   @project_name = N'Costing'
,   @value_type = V
,   @parameter_value = @var;

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

SELECT @var = CASE @@SERVERNAME
    WHEN 'SQLSSISD01' THEN N'DEV_Transpo*.txt'
    WHEN 'SQLSSIST01' THEN N'TEST_Transpo*.txt'
    WHEN 'SQLSSISP01' THEN N'PROD_Transpo*.txt'
    END

Но это разовая задача, поэтому я не думаю, что это обременительно. Предположение о том, как работает наш материал, заключается в том, что он довольно статичен, как только мы его разберем, так что отток после того, как он работает, не так уж велик. Редко производители пересматривают свои стандарты именования.

Подход 2

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

Вариант А

Первый устанавливается внешним субъектом. По сути, это шаг настройки, описанный выше, но вместо сохранения статических скриптов простой курсор будет применять их.

--------------------------------------------------------------------------------
-- Set up
--------------------------------------------------------------------------------
CREATE TABLE dbo.OptionA
(
    FolderName sysname
,   ProjectName sysname
,   ObjectName sysname
,   ParameterName sysname
,   ParameterValue sql_variant
);

INSERT INTO
    dbo.OptionA
(
    FolderName
,   ProjectName
,   ObjectName
,   ParameterName
,   ParameterValue
)
VALUES
(
    'MyFolder'
,   'MyProject'
,   'MyPackage'
,   'MyParameter'
,   100
);
INSERT INTO
    dbo.OptionA
(
    FolderName
,   ProjectName
,   ObjectName
,   ParameterName
,   ParameterValue
)
VALUES
(
    'MyFolder'
,   'MyProject'
,   'MyPackage'
,   'MySecondParameter'
,   'Foo'
);

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

--------------------------------------------------------------------------------
-- You might want to unconfigure anything that matches the following query.
-- Use cursor logic from below substituting this as your source

--SELECT
--    *
--FROM
--    SSISDB.catalog.object_parameters AS OP
--WHERE
--    OP.value_type = 'V'
--    AND OP.value_set = CAST(1 AS bit);
-- 
-- Use the following method to remove existing configurations
-- in place of adding them
--
--EXECUTE SSISDB.catalog.clear_object_parameter_value
--    @folder_name = @FolderName
--    @project_name = @ProjectName
--    @object_type = 20
--    @object_name = @ObjectName
--    @parameter_name = @ParameterName 
--------------------------------------------------------------------------------

Так начинается применение конфигураций

--------------------------------------------------------------------------------
-- Apply configurations
--------------------------------------------------------------------------------

DECLARE
    @ProjectName sysname
,   @FolderName sysname
,   @ObjectName sysname
,   @ParameterName sysname
,   @ParameterValue sql_variant;

DECLARE Csr CURSOR
READ_ONLY FOR 
SELECT
    OA.FolderName
,   OA.ProjectName
,   OA.ObjectName
,   OA.ParameterName
,   OA.ParameterValue
FROM
    dbo.OptionA AS OA

OPEN Csr;
FETCH NEXT FROM Csr INTO
    @ProjectName
,   @FolderName
,   @ObjectName
,   @ParameterName
,   @ParameterValue;

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

        EXEC SSISDB.catalog.set_object_parameter_value
            -- 20 = project
            -- 30 = package
            @object_type = 30
        ,   @folder_name = @FolderName
        ,   @project_name = @ProjectName
        ,   @parameter_name = @ParameterName
        ,   @parameter_value = @ParameterValue
        ,   @object_name = @ObjectName
        ,   @value_type = V;

    END
    FETCH NEXT FROM Csr INTO
        @ProjectName
    ,   @FolderName
    ,   @ObjectName
    ,   @ParameterName
    ,   @ParameterValue;

END
CLOSE Csr;
DEALLOCATE Csr;

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

Вариант Б

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

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