Экспорт и импорт данных
Средства импорта/экспорта
Data Transformation Services
Поставщик OLE DB
Программа dtsrun
Создание DTS пакета
Уровни преобразования данных
DTS пакет изнутри
Особенности DTS пакетов, хранящихся в Repository
Вопросы для повторения

Экспорт и импорт данных

В поставку MS SQL Server 7.0 входит набор специальных средств, которые обеспечивают экспорт и импорт данных, преобразование данных (Data Transformation Services), а также создание и редактирование DTS пакетов (DTS Export/Import Wizard). Кроме того, имеется набор модулей поддержки API прикладных программ, которые позволяют использовать объекты DTS в пользовательских прикладных программах. Задачи управления данными, перемещения их между приложениями и системами, копирование, архивирование и миграция данных, на сегодняшний день являются очень актуальными и требуют отвлечения значительных сил администраторов баз данных. Как правило, перемещение данных между не однородными источниками и местами назначения (например, MS SQL Server – Oracle), сопровождается их промежуточной обработкой или трансформацией. Такие преобразования могут состоять только из простого преобразования типов, а могут и содержать более сложные логические элементы, вплоть до добавления недостающей информации, арифметических вычислений, перекодировки и т.п. Наиболее часто, в задачах импорта/экспорта, приходится изменять формат передаваемых данных, когда они не совпадают в формате хранения источника и места назначения. Например, может потребоваться преобразовать поля, хранящие дату, если формат её хранения различен. Если ваши исходные данные хранятся в нескольких источниках, может потребоваться преобразование и отображение данных, для того, что бы привести их к виду, определённому для места назначения. Наиболее частыми операциями для этого можно считать агрегирование и арифметические операции. Очень важным этапом управления импортируемыми или экспортируемыми данными является обеспечение их согласованности. Поддержка согласованности и связей у источника и места назначения может отличаться. Для обеспечения согласованности данных может потребоваться их преобразование или трансляция в момент передачи. Другой, важной задачей, является проверка достоверности данных. Для решения таких задач используют промежуточные проверки на соответствие передаваемых данных заданным критериям. Кроме непосредственно задач импорта/экспорта данных, SQL Server имеет средства автоматизации этих операций. Например, использование заданий по расписанию может существенно облегчить администратору выполнение типовых задач. Резюмируя вышесказанное, можно выделить несколько моментов, которые должен прояснить для себя DBA, прежде чем приступить к реализации задач импорта/экспорта:

- Где располагаются исходные данные и куда их необходимо перемещать;
- Существует ли разница в форматах исходных данных и данных места назначения;
- Подлежат ли импорту/экспорту объекты базы данных;
- Необходимо ли периодически повторять задачи импорта/экспорта;
- Как проще осуществлять импорт/экспорт, с помощью запросов T-SQL или применяя имеющийся графический интерфейс средств администрирования;
- Какой уровень производительности необходим для эффективного использования импорта/экспорта.

Средства импорта/экспорта

Теперь рассмотрим, какие средства импорта/экспорта входят в состав MS SQL Server 7.0 и какую функциональность они обеспечивают:

- Для простого перемещения и преобразования данных используют DTS Import Wizard и DTS Export Wizard. Эти мастера предназначены для создания DTS пакетов в интерактивном режиме.
- Если необходимо перемещать данные разных типов или из нескольких источников или данные должны быть подвергнуты сложной трансформации, и если Вы опытный DBA, вы можете использовать DTS Designer.
- Если источником и потребителем DTS пакетов являются MS SQL Server 7.0 серверы, или если кроме таблиц, Вам необходимо передавать: представления, хранимые процедуры, параметры по умолчанию, правила, пользовательские типы данных, учётные записи, роли, индексы или ограничения, Вам понадобится DTS Object Transfer.
- Если Вам нужно быстро и без преобразований «залить» в базу большой объём данных из текстового файла, лучше всего подойдёт средство DTS Bulk Insert, основанное на операторе BULK INSERT из состава T-SQL.
- Существует и специальная программа BCP, которая запускается из командной строки. С помощью этой программы удобно экспортировать, а потом импортировать данные из таблиц MS SQL Server 7.0 или импортировать данные из текстовых файлов в ASCII формате.

Кроме перечисленных выше средств, для передачи данных между серверами, можно использовать механизм репликаций, что удобно в случае частого изменения содержащейся в базе информации. Такой подход позволяет копировать схему и сами таблицы, а также определения и вызовы хранимых процедур из исходной базы в базу назначения. Также, для импорта/экспорта, используются некоторые операторы T-SQL и системные хранимые процедуры, такие, как: SELECT INTO, INSERT SELECT, BULK INSERT, BACKUP, RESTORE, sp_attach_db и sp_detach_db.

Data Transformation Services

Если Ваши данные, которые необходимо собирать на MS SQL Server, хранятся в разных местах и в разных форматах, или необходимо передавать объекты базы данных на другой MS SQL Server, автоматизировать этот процесс поможет служба Data Transformation Services (DTS). Назначение этой службы в импорте/экспорте с промежуточной трансформацией данных между одним или несколькими источниками и получателем данных. В распоряжении DTS есть удобный графический интерфейс и несколько визардов (Import/Export, Designer и dtsrun). Эти средства позволяют генерировать и передавать пакеты объектов, которые выполняются в координируемом порядке и обеспечивают одну или несколько задач импорта/экспорта. Формат передачи данных может быть: OLE DB, ODBC или текстовый; причём поставщиками OLE DB могут выступать сторонние фирмы. Если источник данных имеет другой формат хранения, DTS может только перемещать схемы и данные. Триггеры, хранимые процедуры, правила, значения по умолчанию, ограничения и пользовательские типы при передаче между такими источниками трансформации не подвергаются. У Вас есть возможность создавать собственные объекты преобразования данных, которые можно встраивать в продукты сторонних фирм. Таким образом, у Вас имеется прекрасный инструмент для построения хранилища данных, источниками наполнения которого могут быть совершенно разные форматы данных, которые будут собираться и трансформироваться службой DTS в интерактивном режиме или по расписанию, заданному оператором с помощью средств автоматизации MS SQL Server.

Поставщик OLE DB

Для работы DTS требуется поставщик OLE DB, который может обеспечивать доступ, как к реляционным, так и к иным источникам данных. Ниже представлены возможные типы источников/мест назначения данных:

- OLE DB: приложения, такие как Access, SQL Server и другие базы данных.
- ODBC: MySQL, ORACLE, DB2, Access и другие, использующие поставщика OLE DB для ODBC.
- ASCII формат: текстовые файлы с полями фиксированной длинны или с разделителями. Используется поставщик OLE DB для MS SQL Server.
- HTML формат: публикация и преобразование данных HTML, которая не регистрируется в журнале.
- Excel формат: экспорт в электронную таблицу Microsoft Excel, создание сводных таблиц.

Существуют ещё и другие, специализированные источники, для которых поставщики OLE DB разработаны сторонними фирмами.

Рассмотрим теперь возможности DTS Import/Export Wizard.

Эти визарды можно запустить из Enterprise Manager или утилитой командной строки dtswiz. Синтаксис утилиты следующий:

Dtswiz [{{[/u Учётн_Запись_ПодклК_SQLServ][/p Пароль]}[{/i|/x}]
       {/r Имя_Поставщика|[/s Имя_Сервера][/d Имя_Базы_Данных]}}]
Где:
/i – обозначает импорт в SQL Server;
/x – экспорт в SQL Server;
/r – Имя поставщика OLE DB. Например, для ODBC это MSDASQL.

Пробелы после ключей утилиты dtswiz не ставятся. Полный синтаксис включает ещё несколько ключей. Например, использование ключа /f избавит Вас от запроса на сохранение DTS пакта в msdb.

Представленные выше визарды, позволяют передавать DTS пакеты между несколькими разнотипными источникам, передавать схемы между SQL серверами (в т.ч. триггеры, индексы, ограничения, правила, процедуры, значения по умолчанию, пользовательские типы), копировать таблицы, копировать результаты простых и распределённых запросов, копировать результаты объединений. Для удобства составления запросов в интерактивном режиме, допустимо использование программы Query Bilder. Кроме того, в интерактивном режиме можно задать преобразование данных и планировать автоматическое выполнение DTS пакетов.

Описанные выше визарды позволяют выполнять достаточно простые операции и ориентированны на начинающих DBA. Для сложных преобразований данных применяют DTS Designer, который имеет развитые графические средства построения схем обработки DTS пакетов, использует транзакционный механизм, способен задавать сложные последовательности операций, интегрировать/объединять/преобразовывать разные типы данных из разных источников и поддерживает все функции предыдущих визардов.

Программа dtsrun

Если DTS пакеты уже созданы, дальнейшее их извлечение, выполнение, удаление и изменение можно поручить программе dtsrun. Для применения этой утилиты Вы должны иметь права на оператор SELECT в источнике и права владельца базы в месте назначения. Синтаксис следующий:

Dtsrun [{/?|{/[~]S Имя_Сервера{/[~]U Имя_Пользователя [/[~]P Пароль]|
       /e} | {/[~]F Имя_Файла /[~]R Имя_Базы_Данных_Репозитория}
       |{/[~]N Имя_Пакета [/[~]M Пароль_Пакета] | [/[~]G Строка_guid_Пакета]
       |[/[~]V Строка_guid_Версия_Пакета]} [/!x] [/!d] [!y] [/c!]}]

Подробное описание параметров dtsrun содержится в BOL.

Создание DTS пакета

Как правило, DTS пакет состоит из полного описания нескольких, последовательно/параллельно выполняемых задач, т.е. конкретных шагов (steps) состоящих из разнотипных или однотипных операций. Создать пакет Вы можете с помощью соответствующего визарда, DTS Designer или с помощью языка программирования с интерфейсом OLE Automation (VB, C#). Сохранить пакет можно в файле, репозитории или в базе msdb. Не сохранённый пакет будет незамедлительно исполнен. Сохранённые пакеты можно использовать повторно по расписанию, изменять и т.д.
Если DTS пакет сохраняется во внешнем файле, это облегчает планирование его выполнения, позволяет рассылать пакеты, как вложения, в письмах электронной почты, а также позволяет хранить в одном файле несколько пакетов или несколько версий одного пакета (пакеты сохраняются с другим именем, а имя файла не изменяется). Такие файлы имеют расширение .dts и объектную структуру Component Object Model (COM).
Если пакет сохранять в базе данных Microsoft Repository, предназначенной для хранения сведений об объектах и их взаимосвязях (метаданные), то у Вас появится возможность повторно использовать метаданные пакета и иметь информацию об его истории преобразования и происхождении пакета и сопутствующих данных. Кроме того, вся эта информация может быть доступна для других приложений.
Если пакет сохраняется в msdb, то он и его данные будут сохранены в таблице sysdtspackage. При таком варианте, не получится сохранять несколько пакетов в одном файле.
DTS пакет могут шифроваться, причём не зашифрованными останутся только имя, описание, идентификатор, номер версии и дата создания. У пакета может быть пароль владельца, предоставляющий на него все права, и пароль оператора, который применяется в основном при шифрации пакетов и предоставляет доступ к данным, не давая доступа к определению пакета, именам пользователей и паролям. По умолчанию, пакеты не шифруются и не имеют этих паролей. Т.о., пользователь, имеющий доступ к месту хранения пакета может прочитать содержащуюся там информацию, включая пароли и имена пользователей.
В процессе создания DTS пакета, Вы можете задать преобразование данных, при котором будет осуществляться их форматирование и/или изменение. Данные, после получения из источника, могут быть заменены обобщёнными или производными значениями. Эти значения могут быть результатом вычислений или других операций с разными полями набора строк источника данных, и наоборот, дно поле может быть разложено на несколько.

Уровни преобразования данных

Наиболее частой операцией преобразования данных на пути от источника к месту назначения является отображение типов. Для этого используются специальные флаги преобразования, а также можно установить правила преобразования, в соответствии с которыми данные из столбцов одного типа будут трансформироваться в данные столбцы другого типа. Кроме типа данных, разными могут быть размер, точность, масштаб или допустимость NULL. С помощью таких правил можно добиться, что бы точного соответствия исходных данных результирующим, даже если Вам придётся преобразовывать real во float (вот наоборот будет сложнее). При создании DTS пакетов, визарды и дизайнер стараются, как можно более точно установить соответствие типов данных источника и назначения, но у Вас всегда есть возможность внести свои коррективы. Вы можете задать следующие уровни преобразования данных:
- Allow all possible conversions: устанавливается по умолчанию и разрешает все допустимые виды преобразования. Передаваться будут все данные, разрешено расширение типов и преобразование значений NULL.
- Reguire exact match between source and destination: устанавливает уровень точного совпадения данных, т.е. если тип, размер, точность, масштаб или допустимость NULL в источнике и в назначении абсолютно идентичны.
- Allow data type promotion: задаёт уровень, когда возможно расширение типов (real в float), т.е. из короткого в расширенный (из 16 в 32 разрядный).
- Allow data type demotion: Обратное предыдущему, когда из расширенного типа можно сделать короткий. Возможны ошибки переполнения.
- Allow NULL conversion: позволяет копировать столбцы не допускающие NULL в столбцы его допускающие.

Также, часто приходится всячески интегрировать или объединять данные одного или нескольких источников или наоборот, данные одного источника размещаются в нескольких таблицах назначения. Объединятся могут даже разнотипные таблицы. То же самое возможно ина уровне столбцов (объединение или дробление). Например, операция агрегирования, когда общие суммы по некоторым категориям записей и сохраняет эти суммы в таблице назначения.

DTS пакет изнутри

Каждый DTS пакет может нести в себе одно или несколько заданий (не путать с серверными заданиями). Каждое такое задание является составной частью процесса трансформации осуществляемого пакетом. Допустимо пять типов заданий, смешивание которых в рамках одного задания не допускается. Первый тип позволяет выполнять набор операторов T-SQL, для чего существуют три специализированных объекта. За выполнение операторов T-SQL несёт ответственность объект Execute SQL Task. Операции массовой загрузки/выгрузки данных берёт на себя объект Bulk Insert Task. При необходимости исполнения запроса, управляемого данными и во всех других, не предусмотренных предыдущими двумя объектами случаях, используется объект Data-Driven Query Task. Второй тип заданий позволяет реализовать практически любую необходимую процедурную логику за счёт того, что предназначен для выполнения сценариев ActiveX, VBScript, JScript и PerlScript. Причём, при использовании ActiveX, у Вас появляются и все те замечательные возможности, которые предоставляют объекты ADO (ActiveX Data Objects) и интерфейс OLE Automation. С помощью третьего типа заданий Вы сможете заставить DTS пакет запускать внешние программы и процессы, а также отправлять сообщения электронной почты. Эти возможность обеспечивают два специальных объекта: Execute Process Task и Send Mail Task. Первый из этих объектов имеет наиболее широкую область применения, которая включает также и возможность отправки почтовых сообщений. Четвёртый тип заданий обеспечивает перемещение данных между источником и местом назначения. Пятый тип позволяет выполнить другой DTS пакет или воспользоваться его результатами.
Выполнение заданий DTS пакета не обязательно должно быть последовательным и безусловным. В службах DTS реализована достаточно богатая и удобная логика исполнения заданий пакета. Задания могут выполняться параллельно или последовательно, а также возможны комбинации в рамках одного пакета обоих вариантов. Такой подход позволяет чётко задать последовательность выполнения заданий и оптимизировать их выполнение. Для тех же случаев, когда задания имеют разную степень ответственности и важности, Вам пригодится возможность установки каждому заданию одного из трёх уровней приоритетов их исполнения (высокий, средний и обычный). Это позволит избежать возможных коллизий. В тех случаях, когда последовательность заданий пакета зависит от успешности исполнения конкретного задания, Вы можете наложить условия на запуск любого пакета. Вам доступны элементы простой логики, с помощью которой можно разрешить запуск задания в случае успешного (on success) исполнения предыдущего, или наоборот, запустить определённое задание, если предыдущее завершилось с ошибкой (on failure). Если результат исполнения задания не важен, возможен автоматический (безусловный) запуск следующих заданий (on completion). При формировании DTS пакета с помощью DTS Designer, возможно применить транзакционный механизм для нескольких заданий, объединяя их в транзакционные модули. В таком случае, при не успешном выполнении хотя бы одного задания  входящего в пакет транзакционного модуля, произойдёт откат всех изменений, которые были осуществлены в других заданиях этого модуля. В зависимости от того, произошёл ли откат в таком модуле или всё прошло нормально, можно запускать разные задания. Для того, что бы использование транзакционного механизма в рамках пакета было возможным, необходимо, что бы Ваш поставщик данных также поддерживал транзакции. Имеется и ограничение на применение транзакций в DTS пакете: единовременно DTS Designer допускает выполнение только одной транзакции в пакете. Т.е. становится невозможным параллельное выполнение двух транзакционных модулей одного пакета. Их нужно располагать последовательно, причём следующий модуль может быть запущен только после полного выполнения предыдущего или после завершения отката предыдущей транзакции.
Каждый DTS пакет после сохранения является самостоятельным объектом, запустить на исполнение который можно из Enterprise Manager или программой dtsrun. Программа dtsrun может запускаться с несколькими ключами, наиболее ходовыми из которых являются: /s после которого без пробела указывается имя сервера; /u за которым следует имя пользователя; /n за которым следует имя пакета. Этого, как правило, достаточно, т.к. полное описание всех действий находится внутри самого пакета. Поскольку dtsrun является обыкновенной программой, вы можете запланировать её периодическое или однократное выполнение с помощью механизма заданий MS SQL сервера. Также, планирование расписание исполнения DTS пакетов возможно с помощью средств DTS Import/Export Wizard.

Особенности DTS пакетов, хранящихся в Repository

Сохранение DTS пакетов в хранилище данных Microsoft Repository, позволяет кроме хранения нескольких версий самого пакета сохранять его метаданные, которые позволяют отслеживать происхождение данных DTS пакета до уровня строк, выдавать информацию об источнике каждой порции данных, показывать историю преобразования данных, выводить журнал аудита трансформации данных и сохранять аудит исполнения DTS пакетов в этом хранилище. Возможно также использование метаданных пакета, хранящихся в репозитории, в приложениях третьих фирм. Сведения о происхождении пакетов хранятся в виде объектов связанных с одной из его версий. Таким образом, для каждой версии пакета Вы можете определить время, имя пользователя и имя компьютера, причастных к исполнению пакета.
Для отслеживания происхождения данных составляют план использования метаданных, который может изменятся в зависимости от необходимости  аудиторского контроля, изменения объёма метаданных и от требований к производительности. Для размещения в хранилище информации о происхождении данных создаются специальные табличные поля. Кроме того, каждый пакет наделяется службой DTS двумя идентификаторами: коротким (целое число), который задаёт номер версии пакета и длинным Globally Unique Identifier (GUID), являющийся уникальным, глобальным идентификатором. Установить параметры отслеживания происхождения данных можно с помощью DTS Designer, в котором Вам также потребуется отобразить поля источника сведений о происхождении в результирующие поля репозитория.

Вопросы для повторения

ВОПРОС
Вы хотите создать пакет DTS, который извлекает данные при помощи элементарного запроса. Какое средство лучше всего подходит для этого?
ОТВЕТ
Для создания простых схем преобразований пользуйтесь услугами мастеров импорта и экспорта служб DTS.
ВОПРОС
Вам нужны гарантии безопасности пакета DTS (программы): никто не должен ее копировать или просматривать секретную информацию. Что можно сделать для защиты данного пакета DTS?
ОТВЕТ
Можно назначить пакету пароль во время его создания, чтобы никто не мог просматривать или изменять пакет. Впрочем, это не помешает любому желающему выполнить пакет.
ВОПРОС Вы пользуетесь сервером SQL Server и собираетесь переходить на новое оборудование. Оно будет работать быстрее. Каким средством лучше воспользоваться для перевода базы данных и всех ее объектов на новое оборудование?
ОТВЕТ
Лучше использовать задачу DTS Object Transfer, поскольку она позволяет передать на новое оборудование все данные и объекты одной операцией.