SSIS — как создать параметр для параметризованного сложного сценария с несколькими операторами в источнике OLE DB и соединить этот параметр с другим источником

Лео,

Сначала немного предыстории. При использовании SQL-запроса с параметрами в исходном компоненте ole db должны произойти две вещи. Во-первых, необходимо определить тип данных (необъявленных) параметров, а во-вторых, необходимо определить метаданные вывода запроса.

Я говорю, что параметры не объявлены, потому что в команде sql, отправляемой на SQL Server, нет операторов объявления, определяющих типы данных этих параметров. Таким образом, с точки зрения SQL Server, он не знает тип данных, поэтому эти параметры не объявлены. Сравните это с тем, как мы напишем тот же запрос в SSMS; где мы сначала объявляем переменные (например: объявляем @my_var int; выбираем val из таблицы, где col = @my_var; здесь мы объявили нашу переменную @my_var заранее.).

Поэтому, поскольку параметры «необъявлены», при выполнении запроса к SQL Server сначала необходимо определить типы данных этих параметров. И когда дело доходит до этого, SQL Server 2012 и более поздние версии имеют лучшую функциональность, чем предыдущие версии.

В SQL Server 2012 появилась новая процедура под названием sys.sp_describe_undeclared_parameters. . Эта процедура помогает определить типы данных параметров даже для сложных операторов tsql. Он может не всегда давать ожидаемые типы данных, но вы можете дать достаточно подсказок, чтобы получить точные типы данных, которые вам нужны. Однако это доступно только в SQL Server 2012 и более поздних версиях.

Вы сказали, что используете SQL Server 2008. В 2008 году драйвер (собственный клиент 10.x) создает специальный оператор выбора (со столбцами, которым назначаются параметры) и использует fmtonly для вывода типов данных для необъявленных параметров. И в моем тестировании это работает только в более простых случаях и не работает в более сложных операторах tsql. И, таким образом, вы получаете сообщение об ошибке, в котором говорится, что «поставщик не способен ...». Однако если вы инкапсулируете логику в хранимой процедуре и имеете параметры для этих хранимых процедур, SQL Server может легко найти тип данных для этих «необъявленных» параметров. Поскольку: когда мы создаем хранимую процедуру, мы объявляем типы данных для параметров, драйвер 2008 использует процедуру sys.sp_procedure_params_100_rowset для получения типов данных.

Таким образом, вам может подойти хранимая процедура с предопределенными параметрами (потому что вы находитесь в 2008 году). Однако, если вы перейдете на версию 2012 или более позднюю, вы сможете использовать более новые функции.

Что касается второй половины получения метаданных вывода запроса, наличие временных таблиц будет представлять собой проблему. Если вы работаете с SQL Server 2008, вы можете творчески использовать fmtonly, чтобы обойти присутствие временных таблиц в хранимой процедуре (поищите в Интернете или посетите здесь). А если вы используете SQL Server 2012 и более поздние версии, вы можете использовать предложение WITH RESULT SETS для работы с временными таблицами.

Обновление (на основе первого комментария Лео ниже):

Короткий ответ: да. В мире 2008 года сложные операторы tsl не могли быть проанализированы для вывода параметров и их типов данных. Похоже, что только простые операторы select или операторы «exec proc» являются правильными кандидатами. С операторами SELECT СОЕДИНЕНИЯ допустимы; такие операторы, как UNION/INTERSECT/EXCEPT и т. д., допустимы (если каждый отдельный оператор является оператором выбора). Подзапросы были не в порядке. Даже теги комментариев, предшествующие простой операции select/exec, вызывают ошибку.

Это то, что я наблюдал за кулисами с драйвером 2008 года. Скажем, следующий оператор select с параметрами:

select A.col_a, A.col_b from dbo.MyTable A where A.col_c = ? and A.col_d = ?

Затем при выводе типов параметров либо драйвер, либо BIDS (не уверен, что именно) создает и отправляет в базу данных следующие SQL-команды:

set fmtonly on;
select col_c, col_d from dbo.MyTable where 1 = 2;
set fmtonly off;

Обратите внимание на конструкцию этого оператора sql. Он содержит два столбца (col_c и col_d), которым назначаются параметры. Кроме того, есть предложение where для предотвращения возврата каких-либо фактических строк. После построения этот оператор в основном отправляется на сервер sql, чтобы он мог знать, каковы типы данных этих двух столбцов; и, таким образом, присвоить эти типы данных нашим необъявленным параметрам.

Таким образом, процесс (драйвер или BIDS), пытающийся вывести эти типы данных, способен создавать эти операторы SQL только в более простых сценариях и не может делать это в сложных сценариях; в этот момент выдает ошибку

Однако, если вы должны были использовать procs с 2008, создание специальных операторов sql не выполняется, и вместо этого используется sys.sp_procedure_params_100_rowset proc для вывода типов данных.

Наконец, если вы будете использовать 2012 (или более позднюю версию), недавно добавленный proc sys.sp_describe_undeclared_parameters будет намного более функциональным и может обрабатывать некоторые сложные сценарии. По сути, здесь оператор sql (что бы это ни было) из вашего компонента oledb src отправляется как есть в качестве входных данных для вышеупомянутой процедуры; и процедура возвращает набор данных, описывающий типы данных параметров. Итак, для нашего примера в 2012 году это произойдет:

exec sys.sp_describe_undeclared_parameters N'select A.col_a, A.col_b from dbo.MyTable A where A.col_c = ? and A.col_d = ?'