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