По материалам статьи Marcin Policht: SQL
Server 2000 DTS Part 10 - DTS Designer Tasks - The Data Driven
Query Task
В арсенале задач DTS Designer наиболее разносторонним и
сложным является Data Driven Query Task. Его основной
отличительной особенностью от других задач является не только
возможность исполнения операций вставки записей в процессе
трансформации, но и других инструкций T-SQL, таких как UPDATE,
DELETE или запуск хранимых процедур. Правда, такая гибкость
обходиться потерями производительности, так что лучше по
возможности избегать использования этой задачи. Операции
вставки записей наиболее эффективно исполняются описанными в
предыдущих статьях Transform
Data Task и Bulk
Insert Task.
Прежде, чем мы приступим к подробному
изучению работы этой задачи и примеров её использования,
давайте уточним основную идею, на которой основана её
функциональность. Короче говоря, само название "Data Driven
Query" очень точно отражает механизм, используемый в этом
случае для преобразования данных.
Преобразование каждой
строки данных, передаваемой от источника адресату, разделено
на две стадии. Вначале процессинг строки выполняется
специализированным ActiveX скриптом. Логика этого скрипта
подразумевает построчную обработку данных из источника (в
дополнение к исполнению любых других необходимых задач), и, в
зависимости от определяемых логикой условий (например, простое
соответствие указанному значению значения какого-нибудь поля),
может быть выполнена одна из составленных ранее инструкций
TSQL. Обычно, используются параметризованные инструкции TSQL,
параметры которых извлекаются средствами языка Data Driven
Query из запрашиваемых таблиц или связанных таблиц. Как
правило, это обычные табличные функции и получающие данные
таблицы, хотя это и не обязательно (т.к. таблица получатель
данных, указанная в инструкции TSQL, может быть любой
таблицей, доступной через DTS подключение, используемое
задачей Data Driven Query). Обратите внимание на то, что
реальные изменения в таблице-получателе данных происходят
только в результате исполнения заданной инструкции TSQL
(которая может динамически изменяться в зависимости от
значений в записях источника данных), использующей значения
параметров, полученных из полей связанной таблицы (которые, в
свою очередь, могут быть получены из основанного на ActiveX
скрипта преобразования записей исходной таблицы).
Теперь
поговорим о том, что характеризует основную цель этой задачи,
и подробно рассмотрим каждый из её компонент (Вы можете
увидеть эти компонентв в диалоговом окне Data Driven Query
Task Properties), проиллюстрировав функциональные возможности
этой задачи простым примером:
- Source - предоставляет исходные данные для трансформации
их ActiveX скриптом (поля его записей представляются как
ключи DTSSource), который, являясь началом преобразования,
определяет тип выполняемой инструкции TSQL. Это может быть
значением, указывающим на тип последующей операции (UPDATE,
DELETE или INSERT) или значением, из которого такая
информация может быть получена (например, дата с которой
будут удалены устаревшие записи). Кроме того, каждая
исходная запись включает поля, используемые в качестве
параметров в инструкциях TSQL, так как поля источника обычно
отображаются на поля адресата (в рамках преобразования
средствами ActiveX скрипта).
- Bindings - указывает на содержащие данные поля таблицы,
используемые в ActiveX преобразовании (ключи
DTSDestination), и передаёт значения параметров для
инструкции TSQL. Как говорилось ранее, реальные изменения в
получающей данные таблице выполняются обычно не в коде
ActiveX скрипта преобразования, а непосредственно с помощью
инструкции TSQL. Это означает, что в случаях, когда
привязываемая таблица не является получающей данные таблицей
(т.е. таблицей-адресатом, указанной в инструкции TSQL), её
данные вообще не будут изменяться. Обычно, тем не менее,
привязываемая таблица также является и получающей данные
таблицей.
- Transformations - определяет преобразование между
источником и привязываемой таблицей. По умолчанию, это
ActiveX скрипт, выполняемый как функция Row Transform (Data
Driven Query также является многофазной, как и описанная
ранее задача Multi-Phase Data Pump). Используемый для этого
интерфейс (включая диалоговое окно Transformation Options)
практически идентичен интерфейсу Multi-Phase Data Pump, хотя
там вместо закладки Binding Columns используется закладка
Target Columns. Однако, более существенными являются
различия в структуре ActiveX скрипта. В частности помимо уже
знакомого отображения между полями DTSSOURCE и
DSTDESTINATION, ActiveX скрипт предоставляет возможность
запустить на исполнение одну из четырех возможных инструкций
TSQL для каждой строки трансформируемых данных. Выбор
инструкции, которая будет исполнена, зависит от значения,
возвращенного функцией Row Transform, представленной одной
из четырех констант:
- DTSTransformStat_InsertQuery - указывает на то, что
будет выполнена инструкция INSERT;
- DTSTransformStat_UpdateQuery - указывает на то, что
будет выполнена инструкция UPDATE;
- DTSTransformStat_DeleteQuery - указывает на то, что
будет выполнена инструкция DELETE;
- DTSTransformStat_UserQuery - указывает на то, что
будет выполнена произвольно выбранная инструкция (UPDATE,
DELETE или INSERT).
Обратите внимание на то, что
несмотря на содержание в именах этих констант имени
выполняемого оператора, они используются только в качестве
идентифицирующих меток тех четырех инструкций TSQL, которые
Вы предварительно создали и хотите исполнить.
Соответствующие этим меткам инструкции TSQL, на самом деле,
могут содержать действия любого типа, и не обязательно те,
которые соответствуют имени метки.
- Queries - содержит четыре метки для параметризованной
инструкции TSQL. Эти метки-заполнители идентифицируют
INSERT, UPDATE, DELETE и SELECT. Они соответствуют четырем
разным возвращаемым ActiveX скриптом значениям (как описано
выше). По умолчанию, параметры указаны в том порядке, в
каком они появляются в запросе, для чего используя
последовательность полей привязываемой таблицы. Вы можете
проследить схему отображения параметров к полям, нажимая на
кнопке Parse/Show Parameters command (при этом, схема о
тображения появится в нижней части диалогового окна). Чтобы
изменять заданную по умолчанию схему, просто кликните оп
соответствующей полю-адресату записи в нижней части
диалогового окна и выберите то поле привязываемой таблицы,
которое Вы хотите отобразить на нужный параметр.
- Lookups - как уже говорилось ранее в статьях этого
цикла, с помощью Lookups Вы можете получать или
устанавливать значения с помощью дополнительного DTS
подключения внутри основанного на ActiveX скрипте
преобразования. Каждое такое дополнительное подключение
имеет уникальное имя, имя подключения, свои установки кэша
(число кэшируемых результатов поиска) и SQL запрос, который
определяет поисковую операцию. Запрос составляется с помощью
DTS Query Designer и может иметь один или более параметров
(которые обычно обозначаются вопросительными знаками,
заменяемыми значениями из глобальных переменных DTS во время
исполнения преобразования). Для получения подробной
информации о Lookups изучите одну из предыдущих статей этого
цикла о Transform Data Task.
- Options - содержит подмножество конфигурационных
параметров (позволяющих определить файл исключений и его
формат, а так же параметры передачи данных, таких как первая
и последняя строка, максимальное число возможных ошибок и
размер буфера выборки), которые мы описали в нашей
предыдущей статье при рассмотрении закладки Options
диалогового окна Properties задачи Transform Data.
Теперь пришло время рассмотреть, как используются описанные
выше компоненты на очень простом примере. Нашим источником
данных будет текстовый файл, содержащий записи, которые будут
использоваться для изменений в таблице Shippers базы данных
Northwind. Формат файла с разделителем в виде точки с запятой,
а содержать он будет следующие строки:
Update;1;Super Speedy Express;(503) 555-9831
Update;2;Offshored Package;91 (123) 555-3199
Insert;4;Road Runner;(503) 555-1234
Insert;5;Speedy Gonzalez;(503) 555-4321
Delete;4;Road Runner;(503) 555-1234
Скопируйте эти пять строк в текстовый файл и сохраните его
(место и имя можно выбрать произвольно). Как Вы могли уже
догадаться, первое поле указывает на тип выполняемого
изменения, второе - это идентификатор строки, а последние два
(название и телефон компании) используются для создания новой
записи или изменения существующей (очевидно, они не
существенны в случае удаления). Строка номер 4 будет
вставлена, а затем удалена (в целях демонстрации). Обратите
внимание на то, что мы не можем просто удалить три уже
существующие строки таблицы Shippers из-за ограничений
ссылочной целостности. После того, как DTS задача будет
исполнена, первые две строки должны измениться, а пятая строка
будет добавлена (со значением поля ShipperID равным 5).
Мы
будем использовать таблицу Shippers в качестве привязываемой
(а так же и получающей данные) таблицы. Так как источник и
привязываемая таблицы требуют отдельных подключений, мы должны
создать их. Первый источник имеет тип Text File (Source), Вы
можете определить его местонахождение в меню Connection или на
инструментальной панели окна DTS Designer. Укажите имя файла,
который Вы только что сохранили, и выберете формат файла -
Delimited. В качестве разделителей строк будет символ
{CR}{LF}, а в качестве разделителя полей - точка с запятой
(semicolon), который (как и Text qualifier) можно оставить в
предлагаемом по умолчанию значении. Подключение для получающей
данные таблицы будет стандартным Microsoft OLE DB Provider for
SQL Server, указывающим на базу данных Northwind вашего
тестового SQL сервера.
Как только подключения будут
созданы, выберете из меню Task в DTS Designer задачу Data
Driven Query (или перетащите её в рабочую область с
инструментальной панели). Что бы убедиться в том, что
подключение Text File (которое вы должны выбрать для поля
Connection на закладке Source) будет использоваться правильно,
нажмите кнопку Preview, и Вы должны увидеть хранимые в
созданном Вами файле данные. На закладке Bindings, выберите
подключение к базе данных Northwind и таблицу Shippers. На
следующей закладке удалите заданное по умолчанию
преобразование. Нажмите кнопку New, выберете в диалоговом окне
Create New Transformation пункт ActiveX Script. Появится окно
Transformation Options, в котором на закладке Columns Source
нужно перенести в правую область всё поля (Col001 - Col004) и
проверить, что на закладке Binding Columns указаны все колонки
привязываемой таблицы. На закладке General, нажмите кнопку
Properties, и Вы увидите в открывшемся окне следующий,
автоматически созданный шаблон скрипта преобразования:
Function Main()
DTSDestination("ShipperID") = DTSSource("Col001")
DTSDestination("CompanyName") = DTSSource("Col002")
DTSDestination("Phone") = DTSSource("Col003")
Main = DTSTransformstat_InsertQuery
End Function
Замените его на другой (см. ниже), который будет
анализировать значение первого поля и, в зависимости от этого,
определять какая инструкция будет исполнена для анализируемой
строки:
Function Main()
Select Case UCase(Trim(DTSSource("Col001")))
Case "UPDATE"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_UpdateQuery
Case "INSERT"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_InsertQuery
Case "DELETE"
DTSDestination("ShipperID") = DTSSource("Col002")
Main = DTSTransformstat_DeleteQuery
Case ELSE
Main = DTSTransformstat_UserQuery
End Select
End Function
Подтвердите сделанные изменения, нажав кнопку OK, и
возвращайтесь в диалоговое окно Data Driven Query Task
Properties. На закладке Queries заполните представленные ниже
инструкции для типов запросов (INSERT, UPDATE и DELETE)
следующим TSQL кодом (по одному для каждого типа):
INSERT Shippers ([CompanyName],[Phone])
VALUES (?, ?)
UPDATE Shippers
SET CompanyName = ?, Phone = ?
WHERE (ShipperID = ?)
DELETE FROM Shippers
WHERE ShipperID = ?
При этом для каждой инструкции стоит нажимать кнопку
Parse/Show Parameters, что бы проверить соответствие
параметров полям получающей данные таблицы Shippers.
Предлагаемое по умолчанию отображение параметров на поля
таблицы Shippers нужно изменить для получения верного
соответствия, нажав левую кнопку мыши на перечисленных в
нижней части диалогового окна именах полей принимающей данные
таблицы (это будет необходимо сделать для инструкций INSERT и
UPDATE). Наконец, нажмите кнопку OK, чтобы закрыть диалоговое
окно Data Driven Query Task Properties, после чего сохраните и
выполните этот пакет. После этого, Вы можете проверить, что
содержимое таблицы Shippers было изменено так, как мы ожидали,
для чего можно использовать стандартные методы просмотра
данных таблицы (Query Analyzer или Enterprise Manager):
1 Super Speedy Express (503) 555-9831
2 Offshored Package 91 (123) 555-3199
3 Federal Shipping (503) 555-9931
5 Speedy Gonzalez (503) 555-4321
Работа этой задачи может использоваться Вами в качестве
примера для разработки собственных пакетов, включая задачу
Data Driven Query. Для изучения других примеров, можно
обратиться к Books Online. Мы завершим рассмотрение задач DTS
в следующей статье этого цикла.
[В начало]
[К следующей статье цикла]
|