2.2.2 Transform Data Task

Transform Data Task в DTS (Data Transformation Services) в SQL Server 2000, передача параметров хранимой процедуре, преобразования (transformations) в Transform Data Task, запросы к внешним источникам данных (lookups)

Это - наиболее часто используемое задание. Необходимы два (как минимум) источника данных. Первая вкладка - Source, указываем, откуда мы получаем данные. Есть возможность выбрать таблицу/представление на сервере напрямую, а есть возможность написать запрос, который будет передаваться на источник данных (и в нем, например, использовать хранимую процедуру или функцию). Применение хранимой процедуры, принимающей параметры, может выглядеть так:

EXEC CustOrdersDetail ?

Затем через кнопку Parameters настроить соответствие параметров глобальным переменным пакета. Если несколько параметров, они перечисляются через запятую и в списке выглядят как Parameter 1, Parameter 2 и т.п. - по позиции. Квадратные скобки в имени хранимой процедуры использовать нельзя.

Вторая вкладка - Destination, настраиваются параметры получателя данных. Свойства определяются в зависимости от типа источника данных, можно создать таблицу в базе данных SQL Server или Oracle, определить формат и имя текстового файла и т.п.

Третья вкладка - Transformations, преобразования. По умолчанию SQL Server создает по одному преобразованию Copy Columns для каждого используемого столбца. Это - неоптимальный способ, поскольку для каждого преобразования в оперативной памяти создается отдельный объект Data Pump, и чем больше столбцов, тем больше расход ресурсов. Правильнее сделать так:

• нажать на кнопку Delete All, чтобы удалить все созданные автоматически преобразования;
• нажать на кнопку New создать новое преобразование. Выберите тип преобразования Copy Column;
• далее в окне Transformation Options на вкладке Source Columns поместите в список Selected Columns все нужные вам столбцы и то же самое сделайте на вкладке Destination Columns (списки столбцов должны совпадать). Нажмите OK и обратите внимание, как изменилась схема преобразований на вкладке Transformations.

Другие преобразования, доступные из Transform Data Task:

• ActiveX Script Task - самое богатое по возможностям (но и самое медленное) - возможность применить в преобразовании любые встроенные функции VBScript/Jscript или любого другого скриптового языка, для которого в системе установлен интерпретатор. Применяется для пользовательского преобразования данных (например, конкатенации), для дополнительной проверки условий, вместе с lookup queries и т.п.
• DateTime String - возможность произвести очень сложные преобразования даты/времени, например, автоматически использовать полные/сокращенные названия месяцев/дней недели на русском и других языках и т.п.
• Lowercase String/Uppercase String - принудительный перевод данных соответственно в верхний и нижний регистры.
• Trim String/Middle Of String - возможность удалить символы пустого пространства/обрезать часть символов.
• Read File - открывается файл, имя которого записано в столбце источника, и его содержимое копируется в столбец назначения (все файлы должны лежать в одном каталоге).
• Write File - информация из одного столбца источника записывается в файл, имя которого определяется другим столбцом источника.

Возможности, доступные из контекстного меню к преобразованию:

• Delete - возможность удалить именно это преобразование;
• Flags - возможность установить разрешенные/запрещенные возможности преобразований;
• Test - возможность протестировать преобразование, сбросив его результаты во временный текстовый файл (очень часто столбцы показываются в обратном порядке - не обращать внимание).
• Properties и Edit - изменить свойства преобразования.

Вкладка Lookups позволяет определить запросы к внешним источникам данных, возвращаемый результат из которых может использоваться в преобразовании. Очень удобно, поскольку в отличие от джойна можно получать дополнительную информацию из совсем внешних источников данных - например, обращаться к справочникам в DBF. Пример применения Lookup Query:

предположим, что у вас имеется справочник соответствий территорий (таблица Territories в БД Northwind) регионам (таблица Region в той же БД, для наших целей предположим, что она доступна через отдельное соединение). Тогда в определение Lookup Query потребуется записать текст запроса:

SELECT RegionDescription FROM Region WHERE RegionID = ?

А затем использовать то, что возвращает этот запрос, в преобразовании типа ActiveX Script на предыдущей вкладке:

DTSDestination("TerritoryDescription") = _ DTSLookups("Look1").Execute(DTSSource("RegionID"))

Look1 при этом - имя Lookup-запроса, а DTSSource("RegionID") - то, что этому запросу будет передаваться. При демонстрации в базе данных Northwind лучше использовать конструктор преобразования ActiveX Script, кроме того, в наборе Source Columns должны быть столбцы RegionID и TerritoryDescription.

На вкладке Options задания Transform Data Task - дополнительные параметры, которые используются очень часто.

Файл исключения - при важных и больших загрузках данных рекомендуется использовать всегда (желательно с установленными флажками Source error rows и Dest error rows). В этом случае при возникновении каких-либо ошибок при запуске пакета на выполнение будет создано три файла:

1) протокол ошибок - с указанным вами в поле Name именем. Записывается информация об имени пакета, времени запуска/времени завершения выполнения, всех ошибках которые возникли и т.п.

2) файл имя_файла_протокола.source - все строки, при получении которых с источника и/или при выполнении преобразований над которыми возникла ошибка

3) файл имя_файла_протокола.dest - все строки, которые не удалось записать в место назначения (нарушения ограничений целостности, типов данных и т.п.)

Формат файлов (2) и (3) - в соответствии с параметрами, настроенными в группе элементов управления File format.

При закачке значительных объемов данных из разнородных источников возникновение ошибок практически неизбежно - очень удобно проанализировать только те строки, на которых возникли ошибки, и, например, вставить их вручную.

Max Error Count - максимальное кол-во ошибок, которое допускается до прерывания задания. Если используются файлы ошибок, необходимо установить отличным от 0.

Fetch buffer size - кол-во записей, одновременно записываемое в буфер. Используется только для оптимизации производительности.

First row/last row - возможность перекачать не все данные с источника, а только указанные вами.

Параметры SQL Server поддерживаются только OLE DB provider for SQL Server (то есть и источником, и назначением должен выступать SQL Server).

Получить учебные материалы по этому курсу