Службы преобразования данных в Microsoft SQL Server 7.0

Алексей Шуленин, Microsoft

Введение

Независимо от наличия или отсутствия корпоративных стандартов на предприятии разнообразие способов хранения и представления информации обусловлено рядом объективных обстоятельств, в частности, эволюционным характером развития информационной системы предприятия, например: табличный процессор -> персональная СУБД -> настольный (на 5-10 клиентов) БД-сервер -> полнофункциональный мощный сервер баз данных. При этом каждый следующий виток прогресса, как правило, не означает разрушение до основания старой системы во имя построения новой, какой бы производительной и масштабируемой она ни обещала быть. Перефразируя известное высказывание о душе математика, можно сказать, что за душу каждого программиста борются ангел бесконечного совершенства и дьявол здорового консерватизма. В любом случае внедрение новой технологии - процесс далеко не одномоментный, и в течение переходного периода необходимость максимально мирного сосуществования старых и новых форматов хранения в рамках одной информационной системы не вызывает сомнений. Более того, в подавляющем большинстве случаев унаследованные приложения не умирают бесследно, а продолжают тихо доживать свой век на каких-нибудь узкоспециализированных локальных задачах, данные из которых тем не менее должны быть доступны нашей комплексной современной и перспективной системе.

Другой вариант развития событий. Есть задача З₁, для которой покупается решение фирмы Ф₁ на платформе БД₁. Через некоторое время возникает задача З₂, которой фирма Ф₁ не занимается, но под которую есть решение у фирмы Ф₂, только вот беда - производится оно на БД₂ и ни на чем ином. Повздыхали, конечно, но делать нечего - купили. Так понемногу у нас на предприятии вместе с решениями собирается изрядный зоопарк СУБД. А спустя еще некоторое время появляется новая задача З₃, которой на входе требуются данные из БД₁, БД₂ и т.д., и вместо программирования бизнес-логики, разработчики вынуждены решать проблемы доступа к разнородным форматам данных.

Я думаю, приведенные примеры наверняка до боли знакомы большинству программистов, администраторов и IT-менеджеров. Разумеется, ими не исчерпываются все возможные сценарии, однако этого вполне достаточно для того, чтобы существование и актуальность проблемы интероперабельности различных систем управления данными считать доказанным. В сложившейся ситуации наиболее привлекательным и экономичным решением оказывается продукт, способный работать с данными любой природы независимо от их формата и места хранения. Этому требованию удовлетворяет Microsoft SQL Server 7.0, взаимодействующий как с собственным, так и с внешними механизмами хранения с помощью технологии универсального доступа OLE DB. Однако предметом нашего сегодняшнего разговора будет не сама технология OLE DB (предполагается, что слушателям она в той или иной степени известна), а ее реализация в Microsoft SQL Server 7.0 и открывающиеся в связи с этим возможности перед разработчиками гетерогенных серверных приложений.

Если отнести DTC-транзакции к тиражированию с максимально плотной (по протоколу двухфазной фиксации) целостностью, я бы выделил на стороне сервера три основных способа построения распределенных приложений - это тиражирование, службы преобразования данных (Data Transformation Services - DTS) и распределенные запросы. Все они логически связаны друг с другом: например, службы преобразования данных можно рассматривать как разновидность тиражирования мгновенных снимков данных (snapshots), а пакет DTS или шаг пакета может выступать в роли прилинкованного сервера. Каждый из них может в той или иной мере использоваться в реальных приложениях в процессе построения хранилищ данных при сборе и консолидации информации из внешних и распределенных OLTP-источников, однако наиболее яркая роль здесь безусловно принадлежит службам преобразования данных.

DTS: назначение и возможности.

Основной и наиболее сложной задачей, для решения которой применяются DTS, является создание хранилищ или витрин данных. Данные, которые наполняют хранилище, могут исходно быть представлены самыми различными форматами и системами хранения, как мы видели во Введении. Пусть, например, учетом торговых операций занимается приложение на Oracle, а отдел маркетинга отслеживает свои мероприятия с помощью Access. Мы хотим построить куб в MS OLAP Services, чтобы проанализировать как те или иные маркетинговые мероприятия сказываются на объемах наших продаж. Мораль - мы должны уметь осуществлять доступ к источникам данных различной природы. Перед тем, как положить данные в хранилище, данные должны быть очищены, проверены на непротиворечивость и приведены к единому виду. Например, одно приложение хранит месяц апрель как "Апрель", другое - как "апр", третье - как "April", четвертое - как "IV", пятое - как "04" и т.д. Даже если мы с помощью OLE DB, ODBC или как-то иначе доступились до всех наших разнородных источников данных, даже если (представим себе неправдоподобно хорошую ситуацию) это все хранится в одних форматах одной СУБД, мы получим несколько разных членов одного временнОго измерения, которым на самом деле соответствует всего лишь один член уровня "месяц". А ведь месяц может вообще храниться не отдельно, а в составе даты, например, 01/04/99, и тогда его нужно выцепить оттуда при наполнении хранилища. Еще одно требование - возможность частичной предагрегации. Представим себе, что продуктовое приложение хранит данные о продажах за каждый день. Но в процессе анализа будущего куба мы не собираемся погружаться до дней, нас вполне устроит месячный дискрет. Следовательно, нечего тащить все детали в куб и раздувать объем хранилища, стоит агрегировать месячные продажи еще на стадии наполнения хранилища. Итак, доступ к разнородным данным, их очистка, проверка на непротиворечивость, унификация и предагрегация - все эти задачи решают службы преобразования данных.

В простейшем случае DTS могут рассматриваться просто как средство импорта/экспорта данных в/из SQL Server. По большому счету этот инструмент можно применять безотносительно к SQL Server для переноса данных из одного источника в другой, например, из Oracle в Access, из dbf-формата в текстовый файл и т.д. Возникает законный вопрос - как обстоит дело с переносом метаданных и объектов БД, таких, как хранимые процедуры, триггера, индексы, правила ссылочной целостности и прочие ограничения? В случае, когда и источник, и назначение оба представляют собой один или разные SQL Server 7.0, перенос объектов не составляет труда и выполняется задачей типа Transfer SQL Server Objects. В остальных случаях автоматически решается только перенос схемы данных, например, структуры таблицы (и, само собой, данных в ней, как уже было сказано). Дело в том, что функциональность источника/назначения, не являющегося SQL Server, априори неизвестна DTS. Ну, например, что такое индекс, если речь идет об ODBC-драйвере для ASCII-файла? Поэтому если источник, скажем, тот же Oracle, поддерживает данный тип объекта, его перенос возможен, но потребует небольшой ручной работы, например, ввести в состав пакета задачу типа Execute SQL и написать в ней соответствующий SQL-скрипт. При переносе схемы данных отображение типов полей источника на типы полей назначения выполняется автоматически путем приведения и тех и других к стандартным типам OLE DB, за что отвечают OLE DB-провайдеры источника и назначения.

Базовой административной единицей DTS является пакет (package). Основными компонентами пакета служат соединение (connection), задача (task) и шаг (step). Пакет содержит самодостаточное описание всех входящих в него соединений, задач, шагов и других компонентов.

Соединения

Соединение как компонент пакета представляет собой описание источника или назначения, использующихся при переносе данных или других задач. В дальнейшем для краткости мы иногда будем употреблять термин "источник" независимо от того, идет ли речь об источнике или назначении. Соединение с источником осуществляется через OLE DB, следовательно DTS может работать с источниками данных любого типа и формата, в том числе нереляционными, для которых существует OLE DB-провайдер (или ODBC-драйвер, так как в этом случае будет задействован MSDASQL - OLE DB-провайдер для ODBC).

Наиболее часто встречающиеся типы источников имеют в DTS предопределенные соединения, что означает, что соединение уже почти полностью инициализирует провайдера, оставляя пользователю ввести DATASOURCE или какую-то часть PROVIDERSTRING. К таким источникам относятся различные версии MS SQL Server, Oracle, Excel, Access, Paradox, dBase и текстовые файлы. Для остальных требуется описание ODBC DSN (тип - Other Connection) или OLE DB-соединения через Microsoft Data Link (.udl-файлы). Последние позволяют провайдеру настроить пользовательское интерфейс для конфигурирования соединения. SQL Server 7.0 поставляется с OLE DB-провайдерами для SQL Server, OLAP Services, Jet версий 3.51 и 4.0, Index Server, Site Server, Oracle, ODBC и некоторыми другими, например, для самих DTS. Таким образом, все перечисленные источники доступны и могут быть использованы при работе DTS. В настоящее время число ресурсов, для которых написаны OLE DB-провайдеры, перевалило за полсотни и продолжает расти. Понятно, что всех их включить в SQL Server нереально. Поэтому, если Вам требуется соединение с ресурсом, OLE DB-провайдер для которого не включен в комплект поставки SQL Server 7.0 и не выпускается фирмой-производителем Вашей СУБД, следует обратиться на страницу http://www.microsoft.com/data/oledb/products/product.htm, где приведен максимально полный список доступных OLE DB-провайдеров для всех распространенных источников данных, таких, как Sybase, Informix, DB2, Rdb, Ingres, Red Brick, Non-Stop SQL, Adabas, Jasmine, FoxPro, Clipper, VSAM AS/400, Lotus Notes, MS Exchange, службы каталогов NetWare и Windows NT и др., производства как Microsoft, так и других фирм, например, ISG, Intersolv, Object Design Inc., Sagent Technology, Sequiter, X-Tension и т.д.

В более сложных ситуациях, например, когда Вы работаете с источником данных собственной архитектуры и разработки, OLE DB-провайдер придется писать самим с помощью Data Access SDK (входит в состав Microsoft Data Access Components 2.1, может быть свободно скачан с http://www.microsoft.com/data/download.htm). Пример создания соединения в DTS Designer показан на рис.1

Задачи

В DTS существует несколько типов задач. Одним из наиболее распространенных среди них является задача преобразования (Тransformation Тask). Она основана на интерфейсе IDTSDataPump и выполняет роль насоса по перекачке данных между двумя соединениями, т.е. переносит множество записей соединения-источника (таблица, SQL-запрос) в множество записей соединения-приемника. В качестве приемника может быть выбрана существующая таблица, либо создана новая, поля которой вообще говоря могут быть абсолютно неидентичны полям источника. Этап преобразования позволяет определить, как именно поля источника отображаются в поля назначения (см. рис.2), а также осуществить промежуточные преобразования в процессе переноса. Например, из рис.2 мы видим, что поля LastName и FirstName источника отображаются в одно поле EmployeeName приемника. Правило преобразования задается при помощи ActiveX-скрипта (см. рис.3). Функция Main описывает преобразование, выполняющееся для каждой записи источника. Встроенные объекты DTSSource и DTSDestination обозначают соответственно источник и приемник. Поля источника и приемника, участвующие в данной части преобразования, перечислены в текстовых окнах Source columns и Destination Columns. Процесс написания кода преобразования сделан максимально удобным. Например, двойной щелчок по полю EmployeeName приведет к появлению в окне Script выражения DTSDestination ("EmployeeName"). Аналогично появляется любая функция, допустимая в ActiveX Script, из числа перечисленных в списке Functions. Таким образом, мы видим, что поле EmployeeName в приемнике получается как конкатенация строковых полей FirstName и LastName источника, разделенных пробелом. Комбо Language позволяет выбрать язык программирования, на котором пишется преобразование. SQL Server 7.0 обеспечивает поддержку Scripting Host для VB Script, JavaScript и XML. Коды возврата функции преобразования позволяют управлять логикой выполнения преобразования в целом в случае возникновения исключительных ситуаций. Например, код возврата DTSTransformStat_SkipInsert означает не добавлять текущую обрабатываемую запись в приемник, DTSTransformStat_ExceptionRow прерывает обработку текущей записи, вызывает обработчик ошибок и записывает ее в файл исключений и т.д. Все коды возврата и генерируемые ими действия приводятся в документации на SQL Server (см. DTSTransformStatus).

В известном смысле Тransformation Тask можно рассматривать как частный случай задачи типа ActiveX Script, которая может содержать произвольный код на любом из упоминавшихся скриптовых языков с использованием встроенных объектов DTS, таких, как уже знакомые нам DTSSource, DTSDestination, DTSErrorRecords и т.д. Полный перечень этих объектов с описанием их методов и свойств содержится в документации на SQL Server (см. DTS Reference). Вот как мог бы выглядеть перенос данных "один в один" (поля приемника такие же, как поля источника) из одного формата в другой, оформленный в виде задачи ActiveX Script на JavaScript:

 function main() { 
     for ( var i = 1; i <= DTSSource.Count ; i++ ) 
     DTSDestination(i) = toUpperCase(DTSSource(i));
     return(DTSTransformStat_OK);
     } 
  

В ходе переноса выполняется приведение строковых значений полей к верхнему регистру.

Задача типа SQL Script может содержать любую последовательность команд на языке Transact-SQL. Отметим, что при ее создании Вы можете воспользоваться графическим построителем SQL-запросов (см. рис.4), что значительно облегчает процесс написания кода запроса.

Еще один тип задачи - это Data Driven Query Task (DDQ), название которого можно примерно перевести как определяемый данными запрос. Принцип ее действия легко понять также опираясь на рассмотренную нами выше Transformation Task. Последняя, как мы уже знаем, берет каждую запись из множества записей источника, делает (или не делает) над ее полями какое-либо преобразование и получившийся результат добавляет в назначение, т.е. выполняет INSERT. Иногда просто INSERT оказывается недостаточным. DDQ поддерживает 4 типа запроса (INSERT, UPDATE, DELETE и User Defined), которые оперируют над полями источника в зависимости от результата преобразования и которые прописываются в закладке Queries диалогового окна Data Driven Query Properties. Например,

   INSERT INTO Categories(CategoryID, CategoryName, Description, Picture)    VALUES (?,?,?,?)
   UPDATE Categories SET Description = 'Mustard' WHERE CategoryID =    (?) AND CategoryName = 'Produce'.

В качестве параметров запроса выступают поля приемника. Можно задать, какому полю какой параметр соответствует (см. рис.5). К кодам возврата DTSTransformStatus добавляются еще 4 кода: DTSTransformStat_InsertQuery, DTSTransformStat_UpdateQuery и т.д. Если функция Main в ActiveX-коде нашего преобразования, пример которого был рассмотрен выше, возвращает DTSTransformStat_InsertQuery, будет выполнен запрос на вставку, DTSTransformStat_UpdateQuery - на обновление и т.д. Этот тип задачи используется в ситуациях, когда в зависимости от значений полей приемника наши действия над ними должны варьироваться. В задачах типа Transformation и DDQ имеется еще одна полезная вещь под названием параметры поиска (Lookups). Представим себе ситуацию, когда в процессе переноса данных с одного соединения на другое нам нужно осуществить перекодировку данных, например, заменить фамилию сотрудника на его табельный номер, при этом таблица перекодировки может находиться вообще где-нибудь на третьем соединении. Это типичный пример, когда нам пригодится Lookup. Определением Lookup'a является запрос, который прописывается в диалоговом окне свойств задачи (закладка Advanced, кнопка Lookups). В нашем примере это будет, очевидно, такой запрос:

   SELECT au_id FROM authors WHERE (au_lname = ?) 

Остается только выбрать соединение из числа определенных внутри данного пакета (может быть отличным от источника и назначения), на котором будет выполняться этот запрос, и как-нибудь обозвать параметр поиска, например, MyLookup. Теперь в коде преобразования можем написать:

DTSDestination("au_id")= DTSLookups("?yLookup").Execute(DTSSource("au_lname").Value) 

При этом будет выполнен приведенный выше запрос, служащий определением параметра поиска, которому на входе будет передано текущее значение поля au_lname. Результат (значение au_id) будет присвоен в соответствующее поле приемника. Что затем произойдет с полями приемника - зависит от типа задачи. В случае Transformation они однозначно будут добавлены в назначение, в случае DDQ - зависит от кода возврата, т.е. от нас.

Оставшиеся типы задач достаточно тривиальны. Задача массированной вставки (Bulk Insert Task) есть частный случай SQL Task и представляет собой аналог одноименной команды Transact-SQL, обеспечивающей копирование данных из текстового файла в SQL Server. Отметим, что она доставляет максимальное быстродействие, но не позволяет применить никаких преобразований над данными в процессе переноса и требует от провайдера назначения поддержки интерфейса IRowsetFastLoad. Перенос объектов (Transfer SQL Server Objects) между двумя SQL Server-соединениями нами уже обсуждался. Напомним, что этот тип задачи переносит индексы, хранимые процедуры, представления, пользователей и пр. и основан на интерфейсах SQL-DMO. Execute Process Task выполняет команду операционной системы. Send Mail предназначен для отправки электронной почты и для своей работы требует, чтобы на сервере был установлен MAPI-клиент с действительным профилем пользователя.

Понятно, что сколько бы типов задач ни было предусмотрено в пакете, в жизни всегда найдется место подвигу, т.е. встретится ситуация, когда этого будет недостаточно. Поэтому в пакет введено понятие базового интерфейса IDTSCustomTask, от которого, кстати, произведены все рассмотренные нами типы задач. При написании собственного типа задач Вы поступаете точно так же, наследуя данный интерфейс, после чего спокойно совершаете свой подвиг, реализуя коллекцию свойств, метод Execute и т.д. Кстати, чтобы в графическом интерфейсе DTS Designer было удобнее работать с созданным Вами типом задач, стоит обратить внимание на DTSCustomTaskUI.

Шаги.

Шаг есть задача, рассмотренная с точки зрения последовательности выполнения. Шаги позволяют реализовать простейший workflow внутри пакета, т.е. ветвление логики выполнения задач в зависимости от результата работы предыдущих этапов (см. рис.6). Несмотря на то, что объектная модель DTS рассматривает шаг как самостоятельную, отличную от задачи сущность, нам будет удобно представлять себе шаг в виде обертки вокруг задачи. Одному шагу может соответствовать 0 или 1 задача, одну задачу, вообще говоря, могут инкапсулировать несколько различных шагов. ActiveX-код этой обертки (контекстное меню задачи шага, пункт Workflow Properties, закладка Options, Use ActiveX Script -> Properties) выполняется ранее привязанной к ней задачи, что позволяет нам на основе результатов этого кода принять решение о способе выполнения соответствующей задачи: пропустить, выполнить пять раз, выполнять, пока не ... и т.д. Переход от одного шага к другому может происходить в случае успешного завершения предыдущего шага, в случае неудачного завершения, либо просто по завершении независимо от результата. Например, первый шаг включает в себя ActiveX Script-задачу создания базы данных с помощью SQL-DMO, в случае успешного создания производится переход к шагу, состоящему из SQL-задачи создания таблиц, в случае неудачи выполняется шаг с задачей SQL Mail, отсылающей сообщение администратору. Графически управление последовательностью выполнения производится из меню DTS Designer (пункт Workflow) в режиме редактирования пакета. Программно это достигается с помощью коллекции PrecedenceConstraints, входящей в состав свойств шага. Каждый объект PrecedenceConstraint имеет свойства StepName, в котором указывается имя шага, от которого будет зависеть выполнение данного шага, и PrecedenceBasis, где прописывается, что будет приниматься в рассмотрение: просто завершение того шага, либо его результат. Возможны ситуации, когда шаг имеет право выполняться только если не один, а несколько предшествующих шагов закончились, допустим, успешно, или наоборот, когда в результате выполнения одного шага происходит переход сразу к нескольким шагам, как мы видим на рис.6 (зеленые пунктирные стрелки означают "при успехе"). В этом случае сразу несколько шагов, т.е. задач будут выполняться параллельно, например, данные из Oracle и Sybase будут одновременно грузиться в таблицы на SQL Server на разных потоках внутри одного процесса. (Процесс соответствует пакету). Некоторые OLE DB-провайдеры не поддерживают модели free-threading, например, Microsoft Jet OLE DB Provider (он используется для доступа к данным Access, Excel, dBase, Paradox, HTML). Для них безопаснее запретить параллельное выполнение, выбрав опцию Execute on main package thread. По умолчанию, все потоки выполняются с одинаковыми приоритетами, равными приоритету процесса, но для более важных и критичных задач можно подправить относительный приоритет. Это делается из контекстного меню задачи, пункт Workflow Properties, закладка Options, бегунок Task Priority. Приоритет всего пакета в целом управляется из диалогового окна DTS Package Properties, закладка General, группа Execution Parameters, бегунок Priority Class. Там же определяется максимальное число параллельно выполняющихся задач (по умолчанию 4). Если не прибегать к DTS Designer, то все это можно сделать изнутри приложения с использованием DTS API: свойства DTSPackage.PackagePriorityClass и DTSStep.RelativePriority.

Пакеты: создание и хранение

Напомним, что пакет служит контейнером для логического и административного объединения задач преобразования, включая коллекции соединений, задач, шагов и глобальных переменных, т.е. переменных, которые видны изо всех составляющих пакета. Пакеты могут создаваться из графического интерфейса SQL Enterprise Manager и программным путем. Графически пакет создается через DTS Designer, либо неявно через DTS Import/Export Wizard. Последний решает простейшие задачи переноса данных (и объектов в случае SQL Server 7.0 - см. Назначение и Возможности) и может быть запущен как из контекстного меню соответствующего объекта SQL Server, так и из командной строки (dtswiz.exe). DTS Designer, как мы уже знаем, решает более сложные задачи с преобразованием данных в процессе переноса, ветвлением последовательности выполнения и т.д. Он вызывается из раздела Data Transformation Services snap-in'a SQL Server в Microsoft Management Console (MMC). Все действия над пакетом и его компонентами, которые доступны из графического интерфейса: создание, изменение тех или иных параметров, выполнение и пр., также могут быть выполнены из приложения. Microsoft DTS Package Object Library устанавливается вместе с SQL Server 7.0 (:\binn\ Resources\1033\dtspkg.rll). Возможны три способа хранения пакета. Локальные пакеты хранятся на SQL Server в msdb..sysdtspackages. Эта системная таблица содержит всю информацию о пакете (название, номер версии, владелец, ...),

включая сам пакет в бинарном виде (поле PackageData типа Image). Пакеты могут храниться в MS Repository. По умолчанию, репозиторий располагается также на SQL Server (msdb..RTbl*), хотя это необязательно. Репозиторий позволяет сохранить метаданные пакета и информацию о его запусках. Проиллюстрируем сказанное на примере. Создадим простейший пакет, "тупо" переносящий данные из одной таблицы в другую, можно в пределах одной базы данных. В свойствах пакета (закладка Advanced) отметим опции Show lineage variables as source columns и Write lineage to repository. Зайдем в преобразование. Мы увидим, что в исходной таблице появились поля DTSLineage_Full и DTSLineage_Short. Например, по полю Lineage легко определить сам пакет, кто его создал, кто совершил данное преобразование, когда и т.д. Первое (типа uniqueidentifier) есть GUID версии пакета, второе (типа integer) - ее контрольная сумма. Создадим одно из или оба соответствующих поля в таблице назначения и установим перенос "один в один" между источником и назначением. Выполним пакет. Теперь посмотрите данные в таблице назначения. Представьте себе, что Вы потрясены тем, как мастерски были перенесены данные. Вы хотите найти того, кто это сделал, и должным образом отметить, однако никто почему-то не сознается. Чтобы награда все-таки нашла своего героя, поступаем следующим образом. Берем значение lineage из таблицы назначения, копируем его в соответствующее поле ввода (Lineage Long или Lineage Short) формы Repository Lineage Lookup (Data Transformation Services -> Metadata -> закладка Lineage) и кликаем на ссылку Find Lineage. Мы немедленно получаем инфомацию о пакете, его версии, кем он был создан, кем модифицировался, когда был выполнен и т.д. (см. рис.7). Щелчок по ссылке Design откроет данный пакет в DTS Designer. Репозиторное хранение позволяет хранить различные версии одного пакета. Если мы имеем созданный пакет, вносим в него какие-либо изменения, а затем сохраняем в пакет с тем же именем, то он не перепишет старую версию, а наряду с ней будет сохранен как новая версия того же пакета. Закладка Package позволяет просматривать пакеты в репозитории вместе с их версиями и информацией о том, кто и когда запускал какую версию пакета на выполнение (см. рис.8). Метаданные об объектах базы данных, на которые ссылается репозиторный пакет DTS, также хранятся в репозитории. К ним относятся первичные и внешние ключи, поля (названия, типы, длины, точность, ...), а также индексы. Утилита под названием OLE DB Scanner for Microsoft Repository (\Program Files\Common Files\Microsoft Shared\Repostry\dbscan.dll) переносит информацию о схеме в DBM (Database Information Model) репозитория.

Последний, третий, вариант представляет собой хранение пакета в виде файла СОМ-структуры. По умолчанию ему дается расширение .dts. Это наиболее удобный способ хранения пакетов при их копировании, пересылке по e-mail и т.п. В одном файле могут храниться пакеты с разными именами и разные версии пакета с одним именем. Версию можно загрузить в DTS Designer из контекстного меню раздела Data Transformation Services: Аll Tasks -> Open Package -> выбрать файл на диске -> выбрать пакет из числа хранящихся на диске и, при необходимости, его версию (см.рис.9).

Пакеты: выполнение и безопасность

Равно как и создавать, выполнять пакеты можно из графического интерфейса, из командной строки и программно. Если пакет был запущен на выполнение из DTS Designer или DTS Import/Export Wizard, он выполняется в контексте mmc.exe, за вычетом тех случаев, когда мастер импорта/экспорта был вызван не через ММС, а из командной строки (dtswiz.exe). Для запуска пакета из командной строки применяется утилита dtsrun.exe. Основными ключами служат /S - имя сервера; /U - имя пользователя на сервере; /P - пароль на сервере; /E - доверительное соединение (аутентификацию гарантирует Windows NT); /N - имя пакета; /V - GUID версии; /M - пароль доступа к пакету; /R - имя БД, где лежит репозиторий, сигнализирует о том, что мы выполняем репозиторный пакет; /F - в случае хранения пакета в виде СОМ-файла, его UNC-имя и т.д. Подробнее см. dtsrun /?. В этом случае пакет выполняется в контексте командного окна. Хранилище должно пополняться в строго определенные моменты времени, поэтому для пакетов DTS существует возможность назначить расписание их выполнения (контекстное меню пакета -> пункт Schedule Package). При этом в SQL Agent создается новое задание с именем пакета, состоящее всего из одного шага типа Operating System Command, который содержит команду dtsrun /~N <имя пакета> и т.д. В этом случае выполнение пакета происходит в контексте сервиса SQLAgent. Для выполнения пакета из приложения применяется метод Execute объекта DTS.Package.

Существуют два уровня безопасности пакета: владелец (owner) и пользователь (user). При сохранении пакета ему можно назначить как пользовательский пароль, так и пароль владельца. Уровень владельца предоставляет права на редактирование и выполнение пакета, уровень пользователя - только на выполнение. Задание уровня безопасности владельца вызывает шифрование значений всех свойств пакета, кроме Name, Description, ID, VersionID и CreationDate. Следует обратить внимание, что знание пароля пользователя или владельца является необходимым, но недостаточным условием для работы с пакетом. Если пакет локальный, либо хранится в репозитории на SQL Server, для его запуска должны существовать права на чтение в этой базе, а для редактирования - на запись. Если пакет находится в .dts-файле, то должны иметься соответствующие права операционной системы на доступ к этому файлу.

Пакеты: некоторые прикладные вопросы

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

Шаги определяют последовательность выполнения задач пакета. Как только речь заходит о последовательности действий, встает вопрос о том, нельзя ли рассматривать эту последовательность как единую транзакцию. Шаги обладают свойствами, позволяющими объединять их в транзакцию и в зависимости от результата работы шага фиксировать, либо откатывать результаты предшествующих шагов. Как и все распределенные транзакции, транзакции внутри пакета DTS выполняются с помощью MS DTC, поэтому, если Вы собираетесь их использовать, проследите, чтобы его сервис был стартован (snap-in SQL Server -> Support Services -> Distributed Transaction Coordinator). Для того, чтобы соединение, с которым работает задача в шаге, понимало команды фиксации и отката транзакции и могло наследовать контекст внешней транзакции пакета, требуется, чтобы OLE DB-провайдер данного соединения поддерживал интерфейс IТtransactionJoin. В противном случае при выполнении пакета Вы получите сообщение об ошибке: Connection : for task : doesn't support joining distributed transactions or failed when attempting to join. No such interface supported. Кроме того, несмотря на то, что в ходе данного конкретного выполнения пакета несколько шагов могут выполняться параллельно, в пакете в каждый момент времени допускается одна и только одна транзакция. Таково ограничение текущей версии DTS. Это не значит, что в пакете не может быть несколько транзакций, это означает лишь, что в настоящее время они могут выполняться лишь последовательно, друг за другом. Следовательно, когда мы устанавливаем workflow в пакете, необходимо внимательно следить, чтобы ни при каких исходах и ветвлениях логики наши последовательные транзакции не наложились друг на друга. Для разрешения транзакций внутри пакета нужно отметить опцию Use Transactions в диалоговом окне свойств пакета, закладка Advanced. Там же задается уровень изоляции транзакций (Transaction Isolation Level), по умолчанию он установлен в Read Committed. Объединение шагов в транзакции осуществляется с помощью контекстного меню задачи шага, пункт Workflow Properties, закладка Options, опция Join Transaction If Present. Завершение транзакции на данном шаге с фиксацией или откатом изменений данного и предыдущих шагов транзакции управляется опциями Сommit transaction on successful completion of this step и Rollback transaction on failure. Предположим, мы отметили обе эти опции и данный шаг состоит из ActiveX Script Task. Тогда при Main =DTSTaskExecResult_Failure транзакция будет откачена, при Main = DTSTaskExecResult_Success - фиксирована. Тех же результатов можно добиться программным путем, используя практически одноименные свойства объектов Package и Step.

Следующая практическая задача, о которой мне бы хотелось поговорить, - это задача перебора пакетов. Ошибается тот, кто считает, что для этого достаточно пробежаться по какой-нибудь коллекции вроде oSrv.DTS.Packages. Чтобы понять, почему все не так просто, вспомним, что существует три способа хранения пакетов. Работа с пакетом в приложении осуществляется так: в проекте делается ссылка на Microsoft DTSPackage Object Library, создается пустой объект типа пакет, после чего одним из трех методов в зависимости от способа хранения происходит загрузка экземпляра интересующего нас пакета.

   Private Sub cmdLoadPkg_Click()
   Dim dtsPkg As New dts.Package, i
   Select Case lstStorageType.ListIndex
   Case 0 'На сервере как локальный пакет
     dtsPkg.LoadFromSQLServer ServerName:=txtSrvName.Text, _
           Flags:=DTSSQLStgFlag_UseTrustedConnection, _
           PackageName:=cmbPkgName.Text
   Case 1 'Репозиторий
     dtsPkg.LoadFromRepository RepositoryServerName:=txtSrvName.Text, _
           RepositoryDatabaseName:="msdb", _
           RepositoryUserName:="dbo", RepositoryUserPassword:="", _
           PackageID:="", _
           Flags:=DTSReposFlag_UseTrustedConnection, _
           PackageName:=cmbPkgName.Text
      'dtsPkg.LoadFromRepository
   Case 2 'В виде СОМ-объекта (.dts-файл)
       dtsPkg.LoadFromStorageFile UNCFile:=txtFileName.Text, Password:=""
   End Select
   EnumPkgCnns dtsPkg
   End Sub 

Cама форма приведена на рис.10. Мы выбираем способ хранения пакета, выбираем из комбо "Имя пакета" интересующий нас пакет, нажимаем на кнопку "Загрузить пакет в приложение" и в нижнем окне получаем перечень всех соединений внутри пакета. Как происходит загрузка пакета в приложение, мы уже видели из только что приведенного фрагмента кода. Вообще говоря, как мы помним, репозиторные и СОМовские пакеты отличаются не только своими именами, но и версиями внутри одного имени, так что при загрузке необходимо было еще указывать GUID версии. Однако это слишком усложнит наше модельное приложение, поэтому простоты ради будем считать, что: а) внутри каждого пакета находится не более одной версии; б) в одном dts-файле хранится только один пакет. Основным идейным моментом нашего приложения является загрузка списка имен серверных или репозиторных пакетов в комбо. Для перебора локальных пакетов применяется хранимая процедура msdb..sp_enum_dtspackages. Перебор репозиторных пакетов предсталяет собой менее тривиальную задачу, так как подобная процедура для репозитория по понятным причинам отстутствует. Для ее решения я выбрал несколько хакерский путь решения, отследив в SQL Profiler, какие команды поступают на SQL Server, когда мы броузим репозиторий через ММС. После "доработки напильником" родилась следующая "самопальная" хранимая процедура

CREATE PROCEDURE sp_enum_dtspkgs_in_repository AS
   set nocount on
   create table #MyRTblObjColTemp ( Z_ColNum_Z int NOT NULL DEFAULT 0, IntID
   binary(8) NOT NULL, Z_BranchID_Z int NOT NULL DEFAULT 0,
   Z_VS_Z int NOT    NULL DEFAULT 0, TypeID binary(8) NOT NULL DEFAULT 0x0000000000000000)


   Insert into #MyRTblObjColTemp (IntID) select distinct IntID from RTblVersions
   A, RTblRelships B where B.DstID = 0x13000000CA320000 and
   B.RelTypeID = 0x000000001A000000    and A.TypeID = B.OrgID


   update #MyRTblObjColTemp set Z_ColNum_Z = 1, Z_BranchID_Z = A.Z_BranchID_Z,    Z_VS_Z = A.Z_VS_Z,
   TypeID = A.TypeID from RTblVersions A, #MyRTblObjColTemp    B
    where A.IntID = B.IntID and B.Z_ColNum_Z = 0 and A.Z_LClock_Z in (Select    Max(Z_LClock_Z)
    from RTblVersions where IntID = A.IntID)


   SELECT distinct b.IntID,b.Z_BranchID_Z,b.Z_VS_Z,b.Z_VE_Z,b.Name from RTblVersions
    a, #MyRTblObjColTemp d, RTblNamedObj b where a.IntID = d.IntID and b.IntID    = d.IntID and a.Z_VS_Z >= b.Z_VS_Z and
    b.Z_VE_Z >= a.Z_VS_Z and d.Z_ColNum_Z    = 1
   return 0 

Процедура была положена в msdb на нескольких серверах и благополучно доказала свою работоспособность, когда Брайан Уэлкер (Brian Welcker) предложил использовать ODBC-шлюз к репозиторию - библиотека Microsoft Repository (\Program Files\Common Files\Microsoft Shared\Repostry\repodbc.dll). Несмотря на то, что в этом случае доступ через ODBC осуществляется несколько медленнее, чем через OLE DB, идея показалась мне более изящной, так как допускает размещение репозитория не только на SQL Server. Кроме того, мы будем использовать в проекте Microsoft ActiveX Data Objects 2.0 Library (d:\Program Files\Common Files\System\ADO\msado20.tlb) и элемент управления Data Bound Combobox из Microsoft DataList Controls 6.0 (OLEDB) (\Winnt4EE\System32\msdatlst.ocx). Для наполнения его списка именами пакета предлагается следующий способ:

 Const OBJID_IDts
 TransformationPackage = "{{EBB9995C-BA15-11d1-901B-0000F87A3B33},000032CA}"

   .....................................................................
   Sub EnumDTSPackages()
   Dim adoRS As ADODB.Recordset
   Set adoRS = New ADODB.Recordset
   Select Case lstStorageType.ListIndex
   Case 0 'На сервере как локальный пакет
      adoRS.ActiveConnection = "Provider = SQLOLEDB.1; Integrated Security = SSPI;Persist
Security Info = False; Initial Catalog = msdb; Data Source = " & txtSrvName.Text
      adoRS.CursorLocation = adUseClient
      adoRS.CursorType = adOpenStatic
      adoRS.Source = "sp_enum_dtspackages"
      adoRS.Open , , , , adCmdStoredProc
   Case 1 'Репозиторий
      Dim Rep As New RepositoryTypeLib.Repository
      Dim IFaceDef As RepositoryTypeLib.InterfaceDef
      Dim Pkg As RepositoryTypeLib.RepositoryObject
      Rep.Open ("SERVER=" & txtSrvName & "; DATABASE = msdb; Integrated    Security = SSPI")
      Set IFaceDef = Rep.object(OBJID_IDtsTransformationPackage)
      adoRS.Fields.Append "Name", adVarChar, 50
      adoRS.Open
      For Each Pkg In IFaceDef.ObjectInstances
      adoRS.AddNew "Name", Pkg.Name
      Next
   End Select
   Set cmbPkgName.RowSource = adoRS
      cmbPkgName.ListField = "name"
   If adoRS.EOF Then
      cmbPkgName.Text = ""
   Else
      cmbPkgName.Text = adoRS(cmbPkgName.ListField).Value
   End If
   End Sub 

Заметим, что никакого ADO Data Control для нашего DataBound Combo в данном случае не требуется. Генерация множества записей в качестве источника данных происходит для него динамически через объект ADO.Recordset.

После того, как пакет загружен, мы получаем доступ из приложения ко всем его свойствам и коллекциям рассматривавшихся нами выше соединений, задач и шагов. Предположим, мы хотим в SQL Server создать прилинкованный сервер для Clipper. Какой провайдер при этом использовать и как его при этом инициализировать, нам неизвестно, но мы знаем, что в состав DTS входит предопределенное соединение с форматом dBase 5, совместимое с файлами CA-Clipper 5.3. Создаем пакет с фиктивным соединением такого типа, и с помощью данного приложения просматриваем коллекцию соединений этого пакета, в частности, свойства ProviderID и Extended Properties.

Sub EnumPkgCnns(dtsPkg As dts.Package)
   Dim dtsCnn As dts.Connection
   txtPkgCnns.Text = ""
   For Each dtsCnn In dtsPkg.Connections
   txtPkgCnns.Text = txtPkgCnns.Text & dtsCnn.ProviderID & vbTab &
dtsCnn.DataSource & vbTab & dtsCnn.ConnectionProperties("Extended    Properties") & vbNewLine
   Next
   End Sub 

Для соединения dBase 5 c помощью нашего приложения получаем: ProviderID = Microsoft.Jet.OLEDB.4.0; DataSource = D:\TEMP\HetQueries; Extended Properties = dBase 5.0. Отсюда с очевидностью создание прилинкованного сервера для dBase 5 / Clipper:

   if exists (select srvname from master.dbo.sysservers where srvname='MyDBF')
   exec sp_dropserver @server='MyDBF', @droplogins='droplogins'
   go
   exec sp_addlinkedserver @server='MyDBF', @srvproduct='dBase 5', @provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='D:\TEMP\HetQueries', @provstr='dBase 5.0'
   go
   exec sp_addlinkedsrvlogin @rmtsrvname='MyDBF', @useself=false, @locallogin=NULL,    @rmtuser='Admin', @rmtpassword='' 

После чего можно работать со всеми dbf-таблицами в каталоге D:\TEMP\HetQueries:

 sp_tables_ex MyDBF 
 go 
 select * from MyDBF...clp_tbl 

Таким образом мы плавно подошли к нашему третьему вопросу: интеграции службы преобразования данных и гетерогенных запросов. Предполагается, что читатели в той или иной мере знакомы с механизмом гетерогенных запросов. Желающие могут обратиться, в частности, к журналу "СУБД", ©3 за 1998 г., статья называется "Процессор запросов SQL Server 7.0. Некоторые стратегии оптимизатора при построении сложных, параллельных и распределенных планов". С помощью OLE DB-провайдера DTSPackageDSO пакет DTS или отдельный шаг внутри пакета может выступать в качестве источника данных по отношению к внешнему потребителю. Указанный провайдер на самом деле является оберткой, которая запускает пакет (dtsrun в режиме in-process) и возвращает полученное на выходе заданного шага множество записей. Все вызовы, которые потребитель делает к IRowset провайдера тот на самом деле тут же переправляет к множеству записей, полученному от пакета, т.е. играет роль proxy. Для того, чтобы шаг возвращал наружу результаты своей деятельности, нужно присвоить Step. IsPackageDSORowset=true. В DTS Designer это делается с помощью контекстного меню задачи шага -> Workflow Properties -> закладка Options -> отметить DSO Rowset Provider. При этом во время обычного выполнения пакета данный шаг перестает срабатывать, т.е. он переносит в назначение 0 записей. Если требуется, чтобы шаг срабатывал и в то же время служил как источник данных для внешних потребителей, можно создать его копию с IsPackageDSORowset=false на параллельной ветви. Множество записей из пакета можно получить через интерфейсы IOpenRowset и ICommand. Первый способен возвращать только одно множество записей. Если шаг указан, он трактуется как таблица, если нет, пакет выполняется до первого шага, возвращающего результат наружу. Если несколько шагов пакета имеют IsPackageDSORowset=true, можно воспользоваться ICommand и вернуть в приложение сразу несколько множеств записей за один раз, к которым можно обращаться через IMultipleResults::GetResult. Пример создания прилинкованного сервера для DTS-пакета:

exec sp_addlinkedserver @server='MyDTS', @srvproduct='PDSO', @provider='DTSPackageDSO',
@datasrc='/E /S /NDemoPack: SQL->dbf' 

Обратим внимание, что для источника данных употребляется строка, аналогичная той, которая применяется в утилите dtsrun. В данном примере мы создали прилинкованный сервер из пакета с именем DemoPack: SQL->dbf, находящийся на локальном сервере (пустая строка после ключа /S), на который мы ходим под безопасностью Windows NT (ключ /E). Пакет совершает перенос таблицы pubs..employee из SQL Server в dBase 5. В опциях задачи переноса (Workflow Properties) отмечено DSO Rowset Provider. До этого мы убедились в том, что пакет действительно работает. Тогда select * from openquery(MyDTS, 'select *') дает клиенту в точности то множество записей, которое должна получить таблица назначения. Аналогичный результат можно получить без предварительного создания прилинкованного сервера:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/E /S /NDemoPack: SQL->dbf',    'Select *') 

Схожим образом осуществляется работа с выделенной версией того или иного пакета. Перенесем наш пакет в репозиторий (Package->Save As, Location=SQL Server Repository), посмотрим GUID версии (папка Data Transformation Services в ММС -> Metadata -> закладка Package в правой части экрана -> раскрыть пакет с нужным названием -> кликнуть на версию за интересующую дату) и перепишем предыдущий запрос так:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/E /S /R /NDemoPack: SQL->dbf
/V {B50FA41D-DEF5-11D2-BECD-C05A4A000000}', 'Select *')

Представим себе, что каждое преобразование в пакете, показанном на рис.6, помечено как DSO Rowset Provider, нас же интересует результат только одного из них. Загрузим пакет из файла в DTS Designer, выбрав версию за нужную нам дату. В свойствах пакета посмотрим Version GUID. В Workflow Properties найдем имя интересующего шага. Окончательный запрос может выглядеть так:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/FD:\Temp\Olap\NorthwindMart_Fill.dts
/V{10E05829-C260-11D2-BE93-0020AFB67169}', 'Select * from DTSStep_DTSDataPumpTask_4'),

где /V характеризует версию пакета, а DTSStep_DTSDataPumpTask_4 - имя шага.

Об авторе:

Алексей Шуленин,
Microsoft Sales Specialist
Microsoft Certified Systems Engineer
Microsoft Certified Database Administrator
Microsoft Certified Solutions Developer
системный инженер отдела бизнес-приложений российского представительства Microsoft. Имеет сертификаты MCSE, MCDA, MSS. С ним можно связаться по адресу: rusdev@microsoft.com.