Пакет служб SSIS Выполнение задачи SQL, динамический SQL (не SP) с несколькими возвратами, но захват только конечной переменной

У меня есть пакет SSIS, выполняющий динамический SQL. Динамический SQL иногда будет иметь набор результатов, а иногда нет, но я хочу зафиксировать только COUNT, который является возвращаемым значением в приведенном ниже сценарии.

(или я могу использовать задачу C# Script для выполнения этого?), я знаю, что могу перебирать наборы записей в C#, но у меня не всегда будет 2, есть ли способ определить разницу в наборах результатов по имени или что-то в этом роде, или только по индексу массива в наборе результатов [x]?

-- These are passed values in SSIS as paramaters
DECLARE @VariablePassedForSelect AS VARCHAR(50) = ?
DECLARE @ScriptToExecute NVARCHAR(MAX) = ?


-- putting return value count from dynamic script into variable
DECLARE @ReturnRowsEffectedA INT

-- this MAY (or may not) return a result set as dynaic code
exec sp_executesql @ScriptToExecute, N'@VariablePassedForSelect BIGINT, @ReturnRowsEffected INT OUTPUT', @VariablePassedForSelect = @VariablePassedForSelect, @ReturnRowsEffected = @ReturnRowsEffectedA OUTPUT

-- now select results for returning,this is only value I want
SELECT @ReturnRowsEffectedA   AS RowCountR

Все это отлично работает в SQL и возвращает набор результатов из динамического SQL и количество, но я хочу только, чтобы COUNT возвращался в переменную в SSIS.

Если я использую набор результатов, я получаю ошибку для одной строки (поскольку динамический sql может возвращать результаты и подсчитывать).

Три причины, по которым я делаю это таким образом, а не использую SP или другие средства, но эти детали не требуются для моего вопроса.

Как я могу зафиксировать только значение для @ReturnRowsEffectedA?


c# sql-server ssis dataset sp-executesql
person Brad    schedule 22.01.2020    source источник
comment
Вы должны иметь возможность настроить @ReturnRowsEffectedA в качестве выходного параметра на панели Parameter Mapping файла Execute SQL Task. Простой пример приведен по адресу sqlserverrider.wordpress.com/2014/08/31/   -  person digital.aaron    schedule 24.01.2020
comment
Я попробовал несколько вариантов этого, но код, который я запускаю для возврата счетчика, не является SP, поэтому официального оператора RETURN нет, и я не могу сделать RETURN из выбора не в SP (который я нашел) и код выше должен работать таким образом без SP. Таким образом, выходной параметр в SSIS не будет работать.   -  person Brad    schedule 24.01.2020
comment
Если вам нужен только подсчет, не могли бы вы просто изменить динамический sql, который вы создаете, чтобы он не возвращал первый набор записей?   -  person digital.aaron    schedule 24.01.2020

Ответы (1)


arrow_upward
1
arrow_downward

Это можно сделать с помощью выходного параметра. Сначала создайте переменную SSIS, которая будет содержать значение. Я собираюсь использовать имя, которое у вас уже есть, и назову эту переменную [User::ReturnRowsEffectedA]. Вам не понадобится @ReturnRowsEffectedA в реальном запросе. Вы можете оставить большую часть текста запроса без изменений, но замените @ReturnRowsEffectedA в вызове sp_execute на ?, чтобы обозначить, что мы собираемся использовать переменную параметра SSIS. Выполнение SQL-запроса будет выглядеть так:

-- These are passed values in SSIS as paramaters
DECLARE @VariablePassedForSelect AS VARCHAR(50) = ?
DECLARE @ScriptToExecute NVARCHAR(MAX) = ?


-- this MAY (or may not) return a result set as dynaic code
exec sp_executesql @ScriptToExecute, N'@VariablePassedForSelect BIGINT, @ReturnRowsEffected INT OUTPUT', @VariablePassedForSelect = @VariablePassedForSelect, @ReturnRowsEffected = ? OUTPUT

Затем в Parameter Mapping вы добавите третью запись (первые две будут вашими входными параметрами, которые передают @VariablePassedForSelect и @ScriptToExecute). Вы будете использовать [User::ReturnRowsEffectedA] вместо Variable Name, установите Direction на Output и измените Parameter Name на 2. Вы можете оставить тип данных равным LONG, а размер — -1.

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

Здесь вы можете видеть, что у меня ResultSet установлено на None: введите здесь описание изображения

Execute SQL Task использует только один параметр, поэтому Parameter Name здесь равно 0. введите здесь описание изображения

Я приостановил выполнение после завершения задачи, поэтому вы можете видеть, что [User::ResultVar] заполнено значением 1, которое здесь является ожидаемым значением. введите здесь описание изображения

Изменить: чтобы добавить результаты процедуры в переменную типа объекта в SSIS, вы должны сначала создать переменную типа объекта, затем установить для свойства ResultSet значение Full result set, а затем сопоставить результаты с переменной.

введите здесь описание изображения введите здесь описание изображения

person digital.aaron    schedule 24.01.2020
comment
Это сработало отлично! Благодарю вас. Я нашел это как вариант раньше, но он был недостаточно близок к моему сценарию, поэтому я, должно быть, не реализовал его правильно, ваш пример идеален! Спасибо еще раз!! - person Brad; 27.01.2020
comment
Эй, это отлично сработало для меня, но теперь у меня есть проблема, есть ли способ установить параметр, который выводится в ОБЪЕКТ? Я не нахожу способ сделать это. Причина в том, что вывод представляет собой строку длиннее, чем varchar(8000), и она усекается, я знаю, как это решить, но для этого мне нужно вывести объект. - person Brad; 13.02.2020
comment
@Brad, вы можете изменить значение ResultSet на Full result set, а затем на панели Result Set добавить эту запись, которая сопоставляет Result Name из 0 с вашей объектной переменной. Смотрите скриншоты, которые я добавил к ответу для справки. - person digital.aaron; 13.02.2020
comment
Мой код немного изменился по сравнению с приведенным выше, я не вывожу 2 переменные с разными значениями, поэтому я думаю, что должен иметь возможность удалить выходные параметры и использовать объект для полного набора результатов, как указано выше, а затем в коде С# (эта часть нужна для усечения длинных строк), прокручивать наборы результатов и сохранять их в правильные переменные? Я могу поискать, как это сделать в С#, но будет ли это работать, даже если один набор результатов будет нулевым/пустым? это все равно будет набор результатов, правильно - person Brad; 13.02.2020
comment
Это правильно. Если ваша процедура выводит набор записей NULL/пустой, этот набор записей должен присутствовать в объектной переменной ADO. - person digital.aaron; 13.02.2020