Использование динамического SQL в источнике OLE DB в SSIS 2012

У меня есть сохраненный процесс в виде текста команды SQL, которому передается параметр, содержащий имя таблицы. Затем процедура возвращает данные из этой таблицы. Я не могу вызвать таблицу напрямую как источник OLE DB, потому что некоторая бизнес-логика должна произойти с набором результатов в процедуре. В SQL 2008 это работало нормально. В обновленном пакете 2012 года я получаю сообщение «Не удалось определить метаданные, поскольку... содержит динамический SQL. Рассмотрите возможность использования предложения WITH RESULT SETS для явного описания набора результатов».

Проблема в том, что я не могу определить имена полей в процедуре, потому что имя таблицы, которое передается в качестве параметра, может быть другим значением, и результирующие поля могут каждый раз быть разными. Кто-нибудь сталкивался с этой проблемой или есть идеи? Я пробовал всевозможные вещи с динамическим SQL, используя «dm_exec_describe_first_result_set», временные таблицы и CTE, которые содержат WITH RESULT SETS, но это не работает в SSIS 2012, та же ошибка. Контекст — это проблема многих подходов к динамическому SQL.

Это последнее, что я пробовал, но безуспешно:

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM ' + @dataTableName

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr +',','') + [name] + ' ' + system_type_name FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1)

exec('exec(''SELECT * FROM myDataTable'') WITH RESULT SETS ((' + @listStr + '))')

sql sql-server ssis dynamic-sql
person jdf35    schedule 29.10.2013    source источник
comment
Что, если вы попробуете SET FMTONLY OFF; EXEC MyProc в своем исходном коде OLEDB. Делает ли это лучше?   -  person billinkc    schedule 29.10.2013
comment
Пробовал, но не работает. Службе SSIS нужны метаданные.   -  person jdf35    schedule 29.10.2013
comment
облом. Этот трюк сработал для друга, работающего с временными таблицами, когда они переехали с 2008 на 2012 год.   -  person billinkc    schedule 29.10.2013
comment
Ваши данные поступают из SQL Server 2012?   -  person Kyle Hale    schedule 30.10.2013
comment
Да. Я могу заставить это работать, если мой хранимый процесс вызывает функцию, которая использует RETURN TABLE, но я еще не понял, как заставить функцию вести себя динамически, поэтому, возможно, проблема только что переместилась в новую область.   -  person jdf35    schedule 30.10.2013

Ответы (2)


arrow_upward
3
arrow_downward

Итак, я спрашиваю из доброты, почему, черт возьми, вы используете задачу потока данных SSIS для обработки динамических исходных данных, подобных этой?

Причина, по которой у вас возникают проблемы, заключается в том, что вы извращаете все цели задачи потока данных служб SSIS:

  • для извлечения известного источника с известными метаданными, которые могут быть статически типизированы и кэшированы во время разработки
  • запустить известный процесс с простыми (и в идеале асинхронными) преобразованиями
  • взять эти преобразованные данные и загрузить их в известное место назначения также с известными метаданными

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

Вот почему он хочет, чтобы вы добавили WITH RESULTS SET в запрос SSIS, чтобы он мог генерировать некоторые метаданные. Он не делает этого во время выполнения - он не может! Он должен иметь известный набор столбцов (потому что он все равно использует псевдонимы для всех скомпилированных переменных) для работы. Он ожидает одни и те же столбцы каждый раз, когда запускает эту задачу потока данных — одни и те же столбцы, вплоть до имен, типов и ограничений.

Что приводит к одному (ужасному, ужасному) решению - просто вставьте все данные во временную таблицу с Column1, Column2... ColumnN, а затем используйте ту же переменную, которую вы используете в качестве параметра имени таблицы, чтобы условно разветвить свой код и сделать все, что вы хотите с колонками.

Другим более разумным решением было бы создать задачу потока данных для каждой из ваших исходных таблиц и использовать ваш параметр в ограничении приоритета, чтобы просто выбрать, какая задача потока данных должна выполняться.

Для решения, которое плохо адаптировано для стандартного ETL, вам также следует рассмотреть возможность простого развертывания собственной задачи на C# или сценария вместо задачи потока данных, предоставляемой SSIS.

Короче, пожалуйста, не делайте этого. Подумайте о детях (пакетах)!

person Kyle Hale    schedule 29.10.2013
comment
Теоретически это хорошо, но на практике, если у вас есть 230 пакетов SSIS, которые необходимо обновить до версии 2012 с сотнями источников ole DB, настроенных и отлично работающих так же, как в 2008 году, вам нужно быстрое решение. - person jdf35; 30.10.2013

arrow_upward
1
arrow_downward

Для этого я использовал CozyRoc Dynamic DataFlow Plus.

Используя таблицы конфигурации для создания операторов SQL Select, у меня есть один пакет SSIS, который загружает данные из Oracle и Sybase (или любого источника OLEDB) в MS SQL. Некоторые наборы результатов содержат миллионы строк, и производительность превосходна.

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

Без него я был бы готов написать сотни пакетов.

person user3093281    schedule 11.12.2013