Открыть список
Как стать автором
Обновить
474,83
Рейтинг
OTUS
Цифровые навыки от ведущих экспертов

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

Блог компании OTUSMicrosoft SQL Server

Для будущих учащихся на курсе "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: жизнь до и после».

Теги:ms sqlбазы данныхразработка баз данныхpolybase
Хабы: Блог компании OTUS Microsoft SQL Server
Всего голосов 16: ↑13 и ↓3 +10
Просмотры2.5K

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

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

Похожие публикации

Лучшие публикации за сутки

Информация

Дата основания
Местоположение
Россия
Сайт
otus.ru
Численность
51–100 человек
Дата регистрации
Представитель
OTUS

Блог на Хабре