SSIS + Python + C#: правильная оркестрация с выводом в журнал SSIS

Почему Execute Process Task мало

Execute Process Task — простая обёртка над CreateProcess. По дефолту она не читает stdout дочернего процесса, просто ждёт exit-кода. У такой модели есть лимиты, которые всплывают не сразу:

• Опция StandardOutputVariable есть, но она бесполезна для живого лога. Она кладёт весь stdout в одну SSIS-переменную — и только после того, как процесс завершился. Упал на 20-й минуте — переменная пустая, потому что Python не успел flush-нуть буфер до падения.
• Нет потокового логирования. Если скрипт работает 30 минут и на 20-й минуте падает — вы не увидите, до какого шага он дошёл. В SSIS фиксируется только сам запуск и финальный exit-код.
• Нет способа разделить info и error. Всё, что Python написал в stderr — для SSIS такое же «чёрное место», как stdout.
• Email от SQL Agent приходит бесполезный. «Package failed. Exit code: 1» — и всё. Что именно случилось — надо залезать на сервер, читать te Журнал загрузки (если вы его написали), разбираться вручную.
• OnError в SSIS срабатывает только на exit-коде. Event handler получит сообщение уровня «Process exit code 1 does not match Success value 0» — без Python-трейса.

Полный пример C# Script Task

Идея простая: вместо того чтобы SSIS запускал python.exe напрямую, запускаем его из управляемого C#-кода внутри пакета. C# читает stdout и stderr построчно — асинхронно, через события Process.OutputDataReceived. Каждую полученную строку прокидываем в Dts.Events.FireInformation (или FireWarning для stderr). SSIS пишет эти события в свой штатный журнал — и дальше всё как обычно.

using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        // 1. Параметры из SSIS-переменных
        string pythonPath = Dts.Variables["User::PythonPath"].Value.ToString();
        string scriptPath = Dts.Variables["User::ScriptPath"].Value.ToString();
        string arguments  = Dts.Variables["User::Args"].Value.ToString();
        bool fireAgain = true;

        // 2. Конфигурация процесса: обязательно UTF-8 и два redirect'а
        ProcessStartInfo psi = new ProcessStartInfo
        {
            FileName = pythonPath,
            Arguments = "\"" + scriptPath + "\" " + arguments,
            UseShellExecute = false,
            RedirectStandardOutput = true,
            RedirectStandardError = true,
            CreateNoWindow = true,
            StandardOutputEncoding = System.Text.Encoding.UTF8,
            StandardErrorEncoding = System.Text.Encoding.UTF8
        };

        try
        {
            using (Process proc = new Process { StartInfo = psi })
            {
                // 3. Подписываемся на stdout/stderr до Start()
                proc.OutputDataReceived += (s, e) =>
                {
                    if (!string.IsNullOrEmpty(e.Data))
                        Dts.Events.FireInformation(
                            0, "Python.stdout", e.Data, "", 0, ref fireAgain);
                };
                proc.ErrorDataReceived += (s, e) =>
                {
                    if (!string.IsNullOrEmpty(e.Data))
                        Dts.Events.FireWarning(
                            0, "Python.stderr", e.Data, "", 0);
                };

                // 4. Запуск + асинхронное чтение обоих потоков
                proc.Start();
                proc.BeginOutputReadLine();
                proc.BeginErrorReadLine();
                proc.WaitForExit();

                int exit = proc.ExitCode;

                // 5. Финальная строка в журнал + возврат в SSIS
                if (exit == 0)
                {
                    Dts.Events.FireInformation(
                        0, "Python", "Exited OK (" + exit + ")",
                        "", 0, ref fireAgain);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    Dts.Events.FireError(
                        0, "Python",
                        "Python process failed with exit " + exit, "", 0);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
        }
        catch (Exception ex)
        {
            // 6. Даже сбой самого запуска идёт в SSIS как FireError
            Dts.Events.FireError(0, "Python.launcher", ex.ToString(), "", 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

    enum ScriptResults { Success = 0, Failure = 1 }
}

Разбор по пунктам:

• (1) Параметры через Dts.Variables. В SSIS 2016+ достаточно указать имена переменных в ReadOnlyVariables задачи — они будут доступны в коллекции. Старый API с VariableDispenser.LockForRead больше не нужен.
• (2) StandardOutputEncoding = UTF8 — критично. Без этого русский текст из Python превратится в «кракозябры». Встречается в 90% кейсов, потому что по дефолту C# читает в Console.OutputEncoding, а у сервисного пользователя это обычно CP866 или CP1251.
• (3) Подписка до Start(). Если подписаться после — первые строки потеряются. Типичный косяк.
• (4) BeginOutputReadLine + BeginErrorReadLine. Это асинхронное чтение. Если вызвать только один из двух — процесс может залипнуть, когда второй буфер наполнится (типичный deadlock при redirect'е обоих потоков).
• (5) FireInformation для OK, FireError для ошибки. FireError автоматически помечает задачу красной и триггерит OnError event handler — дальше стандартный Send Mail Task.
• (6) Try/catch вокруг всего. Если не нашёлся python.exe, нет прав, сеть лежит — это тоже должно попасть в SSIS как FireError, а не как unhandled exception Script Task'а.

Почему stderr → FireWarning, а не FireError? Python часто пишет в stderr безобидные вещи — deprecation warnings, прогресс от tqdm, логи logging.warning. Если каждую такую строку превращать в SSIS-ошибку — пакет упадёт на первой же. Схема такая: stderr = Warning (видно в логе, но не фейлит), а финальный exit != 0 = Error (фейлит пакет).

Настройка Script Task в SSIS

В Control Flow пакета:

1. Drag & drop    Script Task на Control Flow
2. Double-click   → Script page
3. ScriptLanguage Microsoft Visual C# 2017  (или новее)
4. ReadOnlyVariables
                  User::PythonPath,User::ScriptPath,User::Args
5. Edit Script    → откроется VSTA-среда
6. Вставить код из блока выше в public void Main()
7. Build → File → Save → закрыть VSTA

Package-переменные, которые должны быть созданы:

• User::PythonPath — строка, например C:\Python311\python.exe. Ставим флаг -u при желании получить небуферизованный stdout (но лучше решить это на стороне Python — см. ниже).
• User::ScriptPath — строка, например D:\ETL\scripts\load_orders.py.
• User::Args — строка, собирается через expression. Схема точно же, как в Execute Process Task из предыдущей статьи: server, database, user, password, дата-от, дата-до — любые параметры, которые нужны скрипту.

Expression для Args ничем не отличается от привычного:

@[User::Server] + " " +
@[User::Database] + " " +
@[User::Username] + " " +
@[User::PasswordObf] + " " +
"\"" + @[User::FilePath] + "\""

Python читает sys.argv[1:] и не замечает, что его запустил не Execute Process Task, а Script Task — для него ничего не изменилось.

Как это выглядит в журнале SSIS

Разница между двумя режимами запуска — в SSISDB execution report:

В SSISDB это видно через SSISDB.catalog.event_messages — каждая строка Python становится отдельной записью с таймстампом, типом события, source name («Python.stdout»/«Python.stderr») и текстом. Удобно фильтровать SQL-ом, удобно строить мониторинг.

Как это встраивается в полноценный джоб

Паттерн на уровне всего пакета:

Что именно делает каждый шаг:

1. Execute SQL Task «старт» — пишет в te Журнал загрузки строку «пакет XYZ запущен». Запомнили SYSUTCDATETIME в SSIS-переменной для последующего WHERE.
2. Script Task (C#) — запускает Python, собирает stdout/stderr. Internal-события идут в SSISDB в реальном времени.
3. Execute SQL Task «финал» — читает последнюю запись из te Журнал загрузки (которую пишет сам Python через log2db). Даёт пользовательское сообщение типа «Загружено 14 532 строки» для email-уведомления.
4. OnError handler — срабатывает, если Script Task вернул Failure. Достаёт из SSIS-переменной System::ErrorDescription текст ошибки (это будет точка падения Python), цепляет последние N записей из te Журнал загрузки, шлёт в Send Mail.

Ограничения и грабли

1. Кодировка Python. StandardOutputEncoding = UTF8 в C# — половина дела. Вторая половина — Python должен писать в UTF-8. В Windows по дефолту он пишет в CP1251 (если локаль русская). Добавьте в начало скрипта sys.stdout.reconfigure(encoding='utf-8') или поставьте env-переменную PYTHONIOENCODING=utf-8 в ProcessStartInfo.EnvironmentVariables.
2. Буферизация stdout. Python буферизует вывод, если stdout не tty — а при запуске из C# это всегда не tty. Значит, вы увидите лог не по мере печати, а кусками по 4 КБ. Лечится одним из трёх: запуском python -u script.py, строкой sys.stdout.reconfigure(line_buffering=True) в начале скрипта, или print(..., flush=True) на каждой итерации. Первое — проще всего.
3. Пароль в Arguments всё ещё виден в журнале. Script Task не решает проблему секретов — строка запуска Python с паролем окажется в SSISDB, потому что мы сами её туда пишем через первое FireInformation (если логируем запуск). Для обфускации используйте shift-cipher или Integrated Security — разбирали в прошлой статье.
4. ExitCode 0 ≠ успех. Та же ловушка, что и с Execute Process Task: try/except: pass в Python проглатывает исключение, скрипт выходит чисто, SSIS считает «зелёное». Правило то же: в блоке except — всегда sys.exit(1).
5. Script Task компилируется внутрь пакета. Изменили C#-код — нужно пересобрать и передеплоить .ispac. Не как с Execute Process Task, где поправить конфиг и перезапустить — секунда.
6. Debug работает только в SSDT. Локально в Visual Studio можно ставить breakpoint в C#. На продсервере — нет: там исполняется скомпилированный пакет, и единственный источник информации — SSIS event log.
7. Большие объёмы stdout тормозят. Если Python печатает тысячи строк в секунду, каждая из них идёт в SSISDB через transactional insert — это может ощутимо замедлить пакет. Правило: логируйте фазы («лист 3 из 10 начат», «загружено 50 000 строк»), а не каждую строку данных.
8. SSIS 2012 и старше. В древних версиях Dts.Variables мог требовать явного LockForRead. Если работаете на SQL 2012 — либо мигрируйте, либо добавляйте Dts.VariableDispenser.LockForRead("User::PythonPath") в начале.

Когда переключать, а когда оставлять Execute Process Task

Не всё нужно перегонять на Script Task. Решение зависит от контекста:

В проектах обычно приходим к такому разделению: простые скрипты оставляем на Execute Process Task — их проще сопровождать. Ключевые (ночная полная перекачка, критичный источник, который нельзя пропустить) — переводим на Script Task. Обе задачи живут в одном пакете и в одном проекте — SSIS не против.

Связанные материалы

• SSIS + Python: параметризация ETL-скрипта через sys.argv — базовый паттерн, на который этот метод кладётся сверху. Там же — про shift-cipher для паролей в аргументах.
• Python внутри хранимой процедуры SQL Server — альтернатива, когда данные уже в SQL и внешний Python-скрипт избыточен.
• DWH на SQL Server — где в общей архитектуре живут Python-скрипты и SSIS-пакеты с Script Task'ами.
• Наша методология BI-проекта — как мы подходим к оркестрации и наблюдаемости ETL-слоя в целом.