SQL Server 2000 DTS. Часть 9. Задачи DTS Designer: Transform Data Task

По материалам статьи Marcin Policht: SQL Server 2000 DTS Part 9 - DTS Designer Tasks

В нашей предыдущей статье, мы начали рассмотрение основных принципов и возможностей задачи Transform Data Task. Мы продолжаем эту тему, сосредоточившись на её более "тонких" особенностях. Мы уже описали цели четырех закладок Transform Data Task Properties, реализующих наиболее распространённые сценарии использования этой задачи, не обращаясь к теме использования многопроходной, стадийной обработки данных.
Последняя закладка окна Transform Data Task Properties называется Options, и содержит несколько параметров, управляющих обработкой ошибок и более гибким преобразованием данных. Ошибки могут быть зарегистрированы в файле исключений, расположенном в произвольном месте и с любым именем. Его тип может быть совместимым с SQL Server 7.0 (в случае, если Вы используете парсер, написанный для этой версии) или его можно выбрать как отдельные файлы для сообщений об ошибках, ошибках источника и ошибок адресата (с расширениями .source и .dest, соответственно). В любом случае, Вы можете указать детали форматирования, например, используемые для разделителей строк и полей символы, а так же обрамление текстовых строк (кавычки). В средней секции закладки Options определяются параметры перемещения данных, например, максимальное число возможных ошибок до окончания исполнения задачи, размер промежуточного буфера (число записей, выгружаемых из источника единовременно), номер начальной и последней записи (это позволяет ограничить размер области преобразования, пропустить заголовки или колонтитулы и возобновлять передачу в случае отказов). Наконец, последняя секция на закладке Options применима только к трансформации данных SQL Server, и позволяет ускорить загрузку данных (она рекомендуется для повышения производительности, хотя и создаёт угрозу целостности данных и должна выполняться после полного резервирования). После соответствующей настройки, можно сформировать множество подвариантов, таких как передача NULL значений в виде значений по умолчанию, реализация проверки ограничений, блокировка таблиц или вставка идентификаторов. Также Вы можете предписать обработку данных пакетами. Назначение параметра размера пакета в указании числа строк, которые будут использоваться как одна единица (когда он установлен в 0, все данные будут загружены одним пакетом). Давайте коротко рассмотрим назначение этого параметра.
Главное преимущество использования SQL Server 2000 Transform Data Task по сравнению с его предшественником SQL 7.0 - это степень детализации. Полный процесс преобразования данных может быть разделен на несколько стадий. Однако, по умолчанию, эти функциональные возможности скрыты. Чтобы воспользоваться ими, нужно вернуться в SQL Server Enterprise Manager и выбрать пункт Properties в контекстном меню папки Data Transformation Services. Вы увидите окно Package Properties, и в секции Designer нужно пометить чекбокс "Show multi-phase pump in DTS Designer. Обратите также внимание на другие параметры этого окна (хоть они и не связаны непосредственно с задачами, но могут быть полезны в других ситуациях). В частности Вы можете включить кэширование, чтобы ускорить процесс создания и открытия пакетов в DTS Designer (это обеспечит кэширование DTS задач, провайдеров OLEDB и трансформации, зарегистрированных на SQL Server, так что они не будут перезагружаться каждый раз при запуске DTS Designer). Имейте в виду, что если Вы пометите этот чекбокс, придётся использовать кнопку Refresh Cache (или повторно запускать DTS Designer), чтобы сделать доступными новых провайдеров. Предоставление возможности использования отладки упрощает поиск неисправностей в ActiveX скриптах, для чего задействуется используемый по умолчанию в операционной системе Windows отладчик. И наконец, Вы можете разрешить сохранение пакетов средствами Meta Data Services, который мы рассмотрим в следующих статьях этого цикла.
Пометьте чекбокс "Show multi-phase pump in DTS Designer" и нажмите кнопку OK, откройте папку Local Packages и потом пакет, который мы создали перед этим (для получения информации о создании этого пакета, обратитесь к предыдущей статье этого цикла). Откройте диалоговое окно Data Transform Task Properties этого пакета и убедитесь, что закладка Source содержит следующий 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

Так как мы используем в качестве получателя данных базу tempdb, вероятно Вам потребуется обновить таблицу - адресат данных (иначе Вы получите сообщение об ошибке, указывающее на то, что получающая данные таблица не существует). Чтобы проверить так ли обстоит дело, посмотрите содержание закладки Destination. Если структура таблицы не указана, нажмите кнопку Create..., и вставьте следующий SQL скрипт:


CREATE TABLE [ShippersOrders] (
[ShipperID] int NOT NULL, 
[CompanyName] nvarchar (40) NOT NULL, 
[Phone] nvarchar (24) NULL,
[Orders] int)

Далее, перейдите на закладку Transformations и обратите внимание на то, что появилось новое поле со списком "Phases filter", в котором по умолчанию, указано значение "Row transform", так как это было единственно возможным значением в SQL Server 7.0. Вы можете легко проверить, что этот список содержит много других значений, соответствующих всем возможным стадиям (и подстадиям) Transform Data Task. Чтобы исследовать максимум функциональных возможностей многофазной трансформации данных, мы будем использовать ActiveX скрипт, так что удалите все уже существующие преобразования с помощью кнопки Delete. Нажмите кнопку New и убедитесь, что выбрано значение ActiveX Script для создания нового преобразования, и подтвердите Ваш выбор. В диалоговом окне Transformation Options, вызываемом кнопкой Edit, Вы увидите дополнительную закладку с именем Phases. На ней Вы можете определить какие стадии (и подстадии) будут использоваться в преобразовании. Как говорилось раньше, была установлена функция Row transform, и Мы её можем довольно просто изменить. Настройте содержание закладок Source и Destination так, чтобы все имеющиеся поля были внесены в список отбора полей. Перейдите опять на закладку General окна Transformation Options и нажмите кнопку Properties .... Вы увидите диалоговое окно ActiveX Script Transformation Properties, со всеми стадиями, внесенными в список на закладке Phases (новая закладка должна появиться в нижней - левой части диалогового окна). Отметьте все чекбоксы с левой стороны окна, и автоматически сгенерируйте код, нажав кнопку Auto Gen. Как только Вы это сделаете, код появится в правой части окна.
Как Вы можете видеть в этом окне, многофазная обработка данных разделена на следующие стадии и подстадии:

Pre Source Data - первая стадия, выполняемая в задаче только один раз, перед первой выборкой данных из источника. Эта стадия может использоваться, например, для создания файла системного журнала, калибровки данных или записи заголовков.
Row Transform - вторая стадия, которая полностью реализует функциональные возможности задачи Transform Data в SQL Server 7.0, и она исполняется тогда, когда новая запись получается из источника данных и записывается получателем данных.
Post Row Transform - третья стадия, которая осуществляет дополнительную обработку в зависимости от результата исполнения второй стадии. Этот результат определяет, какая из подстадий (Insert Success, Insert Failure или Transform Failure) будет выбрана. Подстадия Insert Success будет вызвана если не было ошибок, Insert Failure указывает на проблемы с получением данных (например, вставки NULL значений в не допускающие такие значения поля), а Transform Failure обычно появляется в результате ошибок преобразования типов (например, символьный тип в целое число). Post Row Transform обычно выполняется после стадии Row Transform, выполняющей выборку данных из источника, хотя этот порядок может зависеть от нескольких факторов. Если точнее, то Transform Failure и Insert Failure могут привести к преждевременному завершению задачи, если предыдущая стадия критична для результата или (см. выше) был установлен параметр максимального числа допустимых ошибок (закладка Options в Transform Data Task Properties). Из других возможностей можно отметить включение стадии Batch Complete, когда был достигнут заданный размер блока (его мы рассмотрим чуть ниже) и стадия Post Source, когда последние данные были обработаны.
Batch Complete - четвертая стадия, выполняется один раз после завершения обработки каждого блока данных (который задаётся описанным ранее параметром и определяет число вставляемых одним блоком записей, задать его также можно на закладке Options в Transform Data Task Properties). С помощью этой функции, Вы можете разделить выгрузку данных на меньшие части и управлять очисткой и сохранением данных отдельно для каждого такого блока.
Pump Complete - эта стадия выполняется после того, когда последние данные были обработаны. Так как действия, выполняемые на этой стадии, не осуществляют никакого доступа к источнику или адресату данных, эта стадия типична для независимых от данных операций (например, очистка промежуточных результатов, манипуляция с глобальными переменными или подсчёт ошибок).
Post Source Data - заключительная стадия задачи, которая обеспечивает доступ к данным. Она часто используется для записи нижних колонтитулов (хотя и может включить множество вариантов других действий, например, очистки глобальных переменных).

Разделение обработки на стадии обеспечивает несколько преимуществ. Вы получаете больше контроля над манипуляцией данными и возможность обработки ошибок с восстановлением. Преобразование может быть повторно начато с места сбоя, без повторной загрузки уже обработанных данных.
Давайте рассмотрим образец ActiveX скрипта, демонстрирующего многофазную обработку в Transform Data Task. Для примера, давайте предполагать, что мы будем использовать существующее преобразование, заполняя таблицу базирующуюся на Shippers с подсчётом общего количеством заказов для каждого ShipperID. Разобьём данные на два пакета и будем записывать информацию о процессинге в текстовый файл (название которого храниться в глобальной переменной "LogFile"), включающий такую информацию как: "TotalBatches", "TotalRows" и "FailedRows". Сейчас, автоматически сгенерированный код нашего основанного на ActiveX скрипте преобразования должен иметь следующий вид (Function Main выполняется на стадии Row Transform):


Function PreSourceMain()
	PreSourceMain = DTSTransformstat_OK
End Function

Function Main()
	DTSDestination("Orders") = DTSSource("Orders")
	DTSDestination("Phone") = DTSSource("Phone")
	DTSDestination("CompanyName") = DTSSource("CompanyName")
	DTSDestination("ShipperID") = DTSSource("ShipperID")
	Main = DTSTransformStat_OK
End Function

Function TransFailureMain()
	TransFailureMain = DTSTransformstat_OK
End Function

Function InsertSuccessMain()
	InsertSuccessMain = DTSTransformstat_OK
End Function

Function InsertFailureMain()
	InsertFailureMain = DTSTransformstat_OK
End Function

Function BatchCompleteMain()
	BatchCompleteMain = DTSTransformstat_OK
End Function

Function PumpCompleteMain()
	PumpCompleteMain = DTSTransformstat_OK
End Function

Function PostSourceMain()
	PostSourceMain = DTSTransformstat_OK
End Function

Вначале, создадим глобальную переменную DTS пакета с именем "LogFile", перейдя в диалоговое окно Package Properties (как это делается, описывалось в одной из предыдущих статей) и выбрав тип "строка" с каким - либо значением (например, "d:\logs\MPDPump.log" - который нужно предварительно создать). После этого, измените ActiveX скрипт, следующим образом:


'  Copy each source column to the destination column
Function PreSourceMain()
	Const Overwrite = TRUE
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	Set oLogFile = oFSO.CreateTextFile(DTSGlobalVariables("LogFile").Value, Overwrite)
	DTSGlobalVariables("TotalBatches").Value = 0
	DTSGlobalVariables("TotalRows").Value = 0
	oLogFile.Close
	Set oFSO = Nothing
	PreSourceMain = DTSTransformstat_OK
End Function

'  Copy each source column to the destination column
Function Main()
	DTSDestination("Orders") = DTSSource("Orders")
	DTSDestination("Phone") = DTSSource("Phone")
	DTSDestination("CompanyName") = DTSSource("CompanyName")
	DTSDestination("ShipperID") = DTSSource("ShipperID")
	DTSGlobalVariables("TotalRows").Value = _
		DTSGlobalVariables("TotalRows").Value + 1
	MsgBox "Row #: " & DTSGlobalVariables("TotalRows").Value
	Main = DTSTransformStat_OK
End Function

Function TransFailureMain()
	TransFailureMain = DTSTransformstat_OK
End Function

Function InsertSuccessMain()
	InsertSuccessMain = DTSTransformstat_OK
End Function

Function InsertFailureMain()
	InsertFailureMain = DTSTransformstat_OK
End Function

Function BatchCompleteMain()
	DTSGlobalVariables("TotalBatches").Value = _
		DTSGlobalVariables("TotalBatches").Value + 1
	MsgBox "Batch #: " & DTSGlobalVariables("TotalBatches").Value 
	BatchCompleteMain = DTSTransformstat_OK
End Function

Function PumpCompleteMain()
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	Set oLogFile = oFSO.OpenTextFile(DTSGlobalVariables("LogFile").Value, ForWriting)
	oLogFile.WriteLine "Total Rows:"  & vbTab & DTSGlobalVariables("TotalRows").Value
	oLogFile.WriteLine "Total Batches:"  & vbTab & DTSGlobalVariables("TotalBatches").Value
	oLogFile.Close
	Set oFSO = Nothing
	PumpCompleteMain = DTSTransformstat_OK
End Function

Function PostSourceMain()
	PostSourceMain = DTSTransformstat_OK
End Function

Этот простой пример демонстрирует разделение записей на блоки, и сохранение общего числа блоков и записей в файл журнала. Он призван помочь Вам создавать свои, более сложные пакеты. Также, Вы можете изучить главу Phased Transformation Samples в SQL Server 2000 Books Online для получения дополнительной информации на эту тему (Вы найдете там примеры, демонстрирующие TransformFailed и InsertFailed). В следующей статье, мы рассмотрим ещё две задачи DTS.

[В начало] [К следующей статье цикла]