По материалам статьи Marcin Policht: SQL
Server 2000 DTS Part 8 - DTS Designer Tasks - Transform Data
Task
Нам осталось рассмотреть ещё несколько задач DTS Designer,
которых, как вы помните из предыдущих
статей, всего 17, и они также решают задачи SQL Server
2000 Data Transformation Services. Возможно, наибольшим
изменениям относительно SQL 7.0 подвергся Transform Data Task.
В этой и следующей статье мы рассмотрим стандартное поведение
этой задачи, а также, её дополнительные возможности, объяснив
заодно, почему эта задача часто упоминается как Multiphase
Data Pump.
Transform Data Task предназначен для копирования
и трансформации данных из их источника в получающие информацию
базы данных. Трансформация может быть как простым копированием
полей (не делая никаких изменений, а только копируя содержание
исходного поля в поле получателя) с помощью файловых операций
(чтение и запись), так и производить манипуляции над строками
(преобразование регистра, урезание), исполнять ActiveX скрипты
(для получения подробной информации об этом, обратитесь к
предыдущей статье), которые могут вызывать специализированные
COM объекты.
Для Transform Data Task требуется два
подключения к существующим реляционным базам данных. Для
простоты, давайте использовать в качестве источника базу
данных Northwind, а tempdb в качестве адресата (tempdb удобна
потому, что её содержимое очищается при каждом рестарте SQL
сервера). Как только оба подключения будут созданы, установите
указатель на источник данных (что бы он был подсвечен), и
выберите пункт Transform Data Task из меню Task интерфейса DTS
Package Designer (или перетащите соответствующий значок с
инструментальной панели на левой стороне окна). Мастер
запросит у Вас указание базы данных адресата, для этого нужно
просто указать мышкой на второе подключение. Чтобы изменить
свойства нашей новой задачи, дважды щёлкните мышкой по
появившейся между подключениями стрелке и выберите пункт
Properties в открывшемся контекстном меню.
Диалоговое окно
Properties разделено на пять закладок. Первые две,
соответственно называются Source и Destination, и позволяют
Вам определять источник и адресат для данных. В то время как
для адресата нужно выбрать название таблицы (это может быть
существующая или новая таблица), исходные данные могут быть
получены из таблицы, представления или SQL запроса. Кроме
того, при использовании SQL запроса, Вы можете добавить его
параметры (с вопросительными знаками в качестве
меток-заполнителей), которые отобразятся на глобальные
переменные DTS (для получения подробной информации о
глобальных переменных DTS, обращайтесь к нашим предыдущим
статьям этого цикла). В нашем примере, выберите в качестве
источника данных таблицу Shippers, и создайте соответствующую
таблицу в базе данных tempdb. Transform Data Task будет
пытаться автоматически соотнести поля источника на поля
адресата и применить трансформацию копирования полей для
каждой пары существующих полей (это означает, что
трансформация будет индивидуальной для каждого поля источника
данных). Каждая трансформация будет отражена на закладке
Transformations, и каждая стрелка от источника к адресату
будет определять отдельное преобразование, с именами:
DTSTransformation_1, DTSTransformation_2 и DTSTransformation_3
соответственно (как Вы можете видеть, их имена появляются в
списке Name). Если Вы нажмёте кнопку Edit, предварительно
выбрав любое из имеющихся преобразований, Вы попадёте в
диалоговое окно Transformation Options. На его первой закладке
General, помещена информация о типе преобразования (Copy
Column) и его порядок расположения (индикатор очерёдности
исполнения преобразований). Также, тут Вы можете изменить
название преобразования, написав новое имя в текстовом поле
Name, и изменить отражение полей, нажав по кнопку Properties.
Список полей, доступных после этого в диалоговом окне, может
быть изменен путём изменения полей источник и адресата на двух
других закладках диалогового окна Transformation Options
(называемых соответственно Source Columns и Destination
Columns). Как только Вы закончите настраивать доступные там
параметры, щёлкните по кнопке OK, чтобы вернуться на закладку
Transformations диалогового окна Transform Data Task
Properties.
Как правило, для повышения производительности
желательно ограничить число преобразований. В нашем случае,
это можно легко сделать, удалив три автоматически созданных
преобразования Copy Column и заменив их на одно, которое
включает сразу все поля. Чтобы это сделать, в нижней части
диалогового окна нажимает сначала на кнопку Select All, а
затем на Delete All. Вы увидите, что три стрелки исчезли.
Щёлкните по кнопке New, после чего отобразится диалоговое окно
Create New Transformation. Здесь, выберите опцию Copy Column и
щёлкните по кнопке OK. Должно появиться диалоговое окно
Transformation Options. Если Вы нажмёте на кнопку Properties,
Вы увидите, что осталось единственное преобразование, которое
содержит отражения между всеми полями в таблицах источника и
адресата. Как только Вы нажмёте OK, Вы увидите графическое
представление преобразования, отражающее его новую
конфигурацию. Для проверки его работоспособности, нажмите
кнопку Test, и Вы должны получить подтверждение, что пакет был
выполнен успешно (также, Вы можете посмотреть результаты
работы пакета).
Теперь, давайте рассмотрим, как мы можем
получить то же самое другим путём, используя скрипт ActiveX. В
окне Transform Data Task Properties нажмите кнопку Delete,
чтобы удалить все имеющиеся преобразования. Далее, нажмите
кнопку Select All и убедитесь, что выбраны все поля в Source и
Destination. Зайдите в диалоговое окно Create New
Transformation, нажав кнопку New, и выберите пункт ActiveX
Script. Щёлкните по OK, и Вы попадёте в диалоговое окно
Transformation Options. Проверьте его свойства (используя
кнопку Properties), в них Вы увидите, что был автоматически
создан следующий скрипт:
Function Main()
DTSDestination("ShipperID") = DTSSource("ShipperID")
DTSDestination("CompanyName") = DTSSource("CompanyName")
DTSDestination("Phone") = DTSSource("Phone")
Main = DTSTransformStat_OK
End Function
Как и прежде, Вы можете проверить его и получить
подтверждение успешности его исполнения. Это показывает, что
можно использовать различные типы преобразований для
достижения одной цели. В то время как метод с Copy Column
работает быстрее, ActiveX Script более гибок и позволяет
использовать объекты Lookups.
Объекты Lookups, конфигурация
которых задаётся на закладке Lookups, позволяют отыскивать или
устанавливать значения через подключения в DTS. Каждый
поисковый запрос имеет уникальное имя, тип подключения,
установку кэша (число кэшируемых результатов поиска) и SQL
запрос, который определяет операцию поиска. Запрос
составляется в DTS Query Designer и включает один или более
параметров (определяемые вопросительными знаками и заменяемые
значениями, полученными из источника данных или глобальных
переменных DTS, в момент выполнения преобразования). Если
точнее, обычно это достигается следующим кодом в ActiveX
скрипте:
DTSDestination("DestinationColumn") =
DTSLookups("LookupQuery").Execute("ParameterList")
Например, давайте вообразим, что мы хотим создать таблицу в
базе данных адресата, которая будет содержать, в дополнение к
информации из таблицы Shippers, общее количество заказов,
отправленных каждой компании. Это может быть сделано следующим
запросом:
SELECT COUNT(OrderID) AS Expr1
FROM Orders
WHERE (ShipVia = ?)
Создайте этот запрос на закладке Lookups, и назовите его
GetOrders. Далее, измените данные о таблице на закладке
Destination, создав новую таблицу в tempdb с именем
ShippersOrders, используя следующую инструкцию SQL:
CREATE TABLE [ShippersOrders] (
[ShipperID] int NOT NULL,
[CompanyName] nvarchar (40) NOT NULL,
[Phone] nvarchar (24) NULL,
[Orders] int)
Когда Вы переключитесь на закладку Transformations,
обратите внимание, что в таблице адресата появилось поле
Orders, но оно не затрагивает существующее преобразование.
Чтобы изменять это, нажмите сначала кнопку Edit, а затем
кнопку Properties (в диалоговом окне Transformation Options).
Наконец, в разделе ввода кода диалогового окна ActiveX Script
Transformation Properties замените имеющийся там код на
следующий:
Function Main()
DTSDestination("ShipperID") = DTSSource("ShipperID")
DTSDestination("CompanyName") = DTSSource("CompanyName")
DTSDestination("Phone") = DTSSource("Phone")
DTSDestination("Orders") = DTSLookups("GetOrders").Execute(DTSSource("ShipperID"))
Main = DTSTransformStat_OK
End Function
Убедитесь, что на закладке Transformations к полю Orders
теперь тоже протянута стрелка. Теперь, исполнение этой задачи
заполнит дополнительное поле значениями, полученными из
таблицы Orders, используя поле ShipperID для каждой строки в
исходной таблице. Вы должны обратить внимание, что для
производительности лучше избегать поисковых запросов и искать
альтернативные им решения. Например, в нашем случае, тот же
самый результат может быть получен путём другого определения
источника данных. Вместо прямого использования таблицы
Shippers, мы могли бы определить следующий SQL запрос:
SELECT Shippers.ShipperID, Shippers.CompanyName, Shippers.Phone, COUNT(OrderID) AS Orders
FROM Shippers
INNER JOIN Orders
ON ShipVia = ShipperID
GROUP BY Shippers.ShipperID, Shippers.CompanyName, Shippers.Phone
Оставив без изменений таблицу адресата (таблица
ShippersOrders в базе данных tempdb), мы внесём изменения в
преобразование, и будем использовать единственное Copy Column
между источником и адресатом. В итоге Transform Data Task
будет исполняться более эффективно. Имейте в виду, что
поисковые запросы должны использоваться только тогда, когда
необходимо (например, когда часть данных находятся в не
реляционном источнике, должна использоваться хранимая
процедура или если объединения (join) генерируют очень большое
количество данных).
В нашей следующей статье, мы рассмотрим
оставшиеся варианты конфигурации Transform Data Task и изучим
более детально его параметры, которые позволяют реализовать
многофазный процессинг данных.
[В начало]
[К следующей статье цикла]