Хранимая процедура с возвращаемыми значениями в SSIS

    Для будущих учащихся на курсе "MS SQL Server Developer" преподаватель и эксперт по базам данных Евгений Туркестанов подготовил полезную статью.

    Приглашаем также на открытый вебинар по теме
    «Polybase: жизнь до и после». На занятии участники вместе с экспертом рассмотрят, как можно было взаимодействовать с другими базами данных до Polybase, и как это работает сейчас.


    При всем моем двадцатилетнем опыте работы с MS SQL Server и SSIS (когда-то еще DTS), никогда не любил хранимые процедуры с возвращаемым значением. Не знаю, почему так сложилось. Может быть потому, что «хранимки» чаще приходилось использовать для реализации какой-то логики или возвращении набора записей, а для получения одного значения — применял функции. Ну, так вот сложилось. Подразумеваю, что нелюбовь эта взаимная, что подтвердилось в последнем проекте, где хоть убей, но надо было с SSIS присваивать переменным возвращаемые значения процедуры. Изначально, пакет был не мой, а другого разработчика. Ничего плохого говорить не буду, все было сделано достаточно грамотно.

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

    Итак, дано:

    1.      Пакет SSIS

    2.      Переменные пакета:

    Пакет, естественно, привожу тестовый, но сделанный по аналогу. Извините, но оригинальный, к сожалению, показать не могу.

    3.      Есть процедура, которая забирает две даты из таблицы. Даты нужны для определения интервалов инкрементальной заливки хранилища базы. Ниже приводится упрощенный текст процедуры:

    Как видно, ничего сложного тут нет, даже слишком все просто. Можно было, в принципе, не делать возвращаемые параметры, а просто вернуть значения. Но, хозяин – барин, что есть, то есть. Если мы запустим процедуру, то результат будет такой:

    Теперь наш пакет.

    Берем Execute SQL Command задание, настройки:

    Как видим, возвращающий результат не выбран, так как у нас возвращаемые параметры. В выражение SQL ставим нашу процедуру:

    И теперь назначаем параметры. Здесь самое интересное.

    Для возвращаемых параметров выбираем Output и переменные пакета. Вопрос – какой тип данных для этих параметров мы должны выбрать?

    Для проверки того, что будет возвращаться, я создал скрипт задание, который будет показывать C# MessageBox со значением переменных. Настройки и код такие:

    Вернемся к нашим параметрам, вернее, к их типу. Какой тип выбрать? У возвращаемых параметров процедуры тип DATETIME. Посмотрим, что предлагает SSIS.

    SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" starting.
    Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query " exec dbo.testMultipleOutput ?, ? OUTPUT, ? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    Task failed: Execute SQL Task
    Warning: 0x80019002 at TestMultiplePutput: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" finished: Failure.
    The program '[50800] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)

    Интересно. Причем ошибка «вылезает» из COM компонента – «проблемы с запросом, не настроен результат или параметры». Но, как мы видели, процедура отрабатывает. Если выражение просто скопировать в SSMS, поменять параметры и запустить, все работает. Честно говоря, ошибка меня заставила потерять какое-то время, но вменяемого ответа почему так происходит, я не нашел. Возможно, здесь происходит ошибка конвертации DATETIME в DATE. Причем, это происходило только, если я использовал OUTPUT параметры в компоненте.

    Сам пакет выглядит таким образом:

    Все настолько просто, что даже скучно. Смотрим дальше.

    Выбираю DBDATE, сохраняю, запускаю. Как и следовало ожидать, вернулась дата.

    DBTIME выбирать смысла нет, поэтому идем дальше. Хотя я все-таки попробовал. Вернулось ожидаемое время, но с сегодняшней датой. То же самое произошло и с DBTIME2. Осталось еще пара типов.

    DBTIMESTAMP. Кто-то мне говорил, что не стоит с этим типом работать в SSIS. Дескать, неправильно отображает дату и время, так как это не совсем DATETIME. Сейчас мы это увидим.  Вернулось то, что и ожидалось:

    Если выбрать, ради эксперимента, последний «временной» тип DBTIMESTAMPOFFSET, который, в принципе, предназначен для работы с временными зонами, но чем черт не шутит, то он вернет правильную дату, но другое время:

    Есть еще один вариант работы с возвращаемыми параметрами, но это, скажем, на любителя или для тех, кто сильно, как и я их не любит. В Execute SQL Command в тексте можно написать выражение T-SQL, примерно такое:

    И настроить возвращаемый результат

    И определить переменные для присвоения значений

    Как говорится, дело вкуса. По производительности разницы нет, по стилю и самому решению, мне, даже при всей нелюбви к возвращаемым параметрам в пакете, больше импонирует первый вариант.


    Узнать подробнее о курсе "MS SQL Server Developer".

    Смотреть открытый вебинар по теме «Polybase: жизнь до и после».

    OTUS
    Цифровые навыки от ведущих экспертов

    Комментарии 1

      0

      Можно сделать параметры пакета типа строки, а в процедуре реализовать вывод в нужном формате — ГГГГ-ММ-ДД. Если со всех сторон от пакета (т.е. в запускаемой процедуре и, например, в процедуре куда далее пакет передает параметры) придерживаться концепции формата даты по ISO, то использовать параметры пакета типа строки вполне удобно.

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

      Самое читаемое