Практическое применение Integration Services

Изложение не претендует на полноту, сейчас я хочу показать лишь некоторые возможности SSIS, чтобы вы могли приблизительность оценить целесообразность его использования. А уже в случае такого выбора - изучать предмет более досконально.

1. Базы данных для примера

В примерах используется база данных AdventureWorks в качестве источника и простая база, которую легко можно сделать самостоятельно в качестве приёмника данных. Важно понимать, что примеры приводятся искусственные, в реальной жизни всё может быть несколько сложнее.

Итак, перед началом работы нам понадобится экземпляр MS SQL Server 2012 с установленной на нём БД AdventureWorks и созданной тестовой БД, назовём её TestSSIS. Для обучения проще держать MS SQL на том же компьютере, на котором будем работать с проектом Integration Services.

Не забудьте также для MS SQL Server установить Integration Services и SQL Server Data Tools:

2. Создание пакета

Пакет - это набор действий (выполняемых с помощью компонентов SSIS), который мы можем запускать отдельно или развернуть его для запуска на сервере. Пока мы считаем что у нас этап разработки и будем запускать его из SQL Server Data Tools.

Для того, чтобы начать работать с SSIS, необходимо запустить SQL Server Data Tools (по сути, это Microsoft Visual Studio 2010 с дополнительными шаблонами и компонентами для Business Intelligence) и создать проект "Integration Services Project":

Создав проект, мы получаем пустой пакет для SSIS (Package.dtsx), с которым и будем работать. Поскольку это студия, мы можем в Solution Explorer добавлять, удалять и переименовывать пакеты.

Перед тем как уже делать что-то на SSIS, добавим строки соединения до наших БД (в реальном проекте таких строк может быть много, также может потребоваться заменять их при развёртывании на реальном сервере).

Самый быстрый способ сделать это - нажать правой кнопкой мыши на панели Connection Managers (по умолчанию при редактировании пакета располагается внизу). В нашем случае добавляем ADO.NET Connection:

Диалог подключения к БД стандартный, приводить его смысла не имеет. Создаём две строки подключения - AdventureWorks2012 и TestSSIS.

3. Скрипты на C#

С моей точки зрения, возможность использования скриптов в SSIS заслуживает того, чтобы поговорить о ней сразу, чтобы понимать, что делать в случае, если не хватает стандартных компонентов SSIS.

С помощью скриптов можно выполнять разнообразные задачи. Разве что не стоит увлекаться и писать скрипты для задач, которые вполне хорошо решаются стандартными средствами. В документации есть набор примеров, я остановлюсь на возможности манипуляции переменными, как наиболее простой.

Допустим нам нужно получить дату вчерашнего дня (и мы не хотим это делать на стороне MSSQL). Для этого сначала добавим переменную. Важно: фокус должен быть на вкладке "Control Flow", тогда в верхнем меню появится пункт "SSIS / Variables". Выбрав его, получим окно Variables (по умолчанию - панель внизу).

Нажав на первую иконку, получим новую строку в списке переменных. У вас может возникнуть мысль - "вот же кнопка для Expression, давайте попробуем без скриптов". Однако, с помощью выражений легко получить только текущую дату со временем, с помощью функции GETDATE(). А чтобы получить только дату - придётся пройти круг-другой ада (если соотнести кажущуюся простоту задачи и сложность реализации таким способом).

Поэтому просто добавляем переменную:

А затем перетаскиваем на наш Control Flow "Script Task":

Привыкайте сразу переименовывать действия (это можно сделать по одинарному щелчку на названии), чтобы в будущем пакет было легко поддерживать. Для нашего случая я предлагаю использовать Initialize Variables (потому что в будущем переменных может стать больше).

Теперь щёлкаем дважды по действию и получаем его окно настроек. Там сразу стоит добавить нашу переменную к списку "ReadWriteVariables", иначе мы не сможем присвоить значение:

Далее нажимаем кнопку Edit Script и запускается... ещё один экземпляр Visual Studio для редактирования скрипта. В первый раз это может шокировать, потом привыкаешь. Тем более, что такой вариант позволяет отлаживать скрипты, просто поставив breakpoint и закрыв студию.

Код мы пишем в Main, обращаться к переменным довольно просто:

Закрываем студию и нажимаем "OK" в диалоге настроек (тем, у кого правильные рефлексы, напоминать о полезности сохранении пакета по "Ctrl+S" не буду). Значение в переменную мы записали, а использовать его пока не будем.

В принципе, можно сразу на всякий случай проверить, правильно ли мы всё написали, нажав F5. В случае успешного выполнения вы получите что-то похожее на это:

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

4. Постановка задачи

Для демонстрационных целей возьмём простую синтетическую задачу (не ищите в ней смысла), состоящую из следующих блоков:

1. Импортировать три справочника (AddressType, ContactType, PhoneNumberType) в таблицы со структурой (Id, Name).
2. Если запись с идентификатором уже есть, обновлять, только если поменялось название.

5. Импорт простых справочников

Давайте напишем самый простой пример импорта. Выберем таблицу AddressType и скопируем данные в другую БД. Добавим только одно небольшое усложнение - будем копировать не все столбцы и один переименуем.

При написании импорта важно понимать, что все данные в одной цепочке как бы перетекают от начала в конец (возможно, "обрастая" по пути новыми столбцами и данными).

Сначала создадим таблицу в тестовой БД. Это можно сделать в SQL Management Studio, можно и в SSIS, но это будет нелогично, потому что обычно таблицы в приёмнике созданы до импорта да и пример усложнится. Создаём таблицу с первичным ключом:

create table AddressType(Id int not null, Name nvarchar(50) not null,
constraint PK_AddressType primary key(Id))

Теперь добавим в наш пакет действие по импорту. Сначала добавляем и сразу переименовываем компонент Data Flow, который будет содержать этот блок импорта:

Дважды щёлкнув по компоненту, открываем вкладку "Data Flow". Затем добавляем компонент ADO.NET Data Source, обратите внимание, что SSIS предлагает две стрелки с вариантами дальнейшего выполнения процесса (для обычных данных и в случае ошибки) и показывает иконкой ошибки (в данном случае не задан ConnectionManager):

Теперь добавим соединение и напишем запрос, получающий данные. Для этого дважды щёлкнем на компонент и зададим нужные значения (чтобы проверить себя можно нажать Preview):

В данном случае я ограничил и переименовал столбцы с помощью запроса. Можно было выбрать режим Table or view вместо SQL Command, но лично я обычно выбираю такой вариант, только если нужны все столбцы. Что касается переименования столбца, можно было этого не делать и просто позже по-другому связать столбцы (позже вы увидите, почему я предпочитаю делать так).

Теперь осталось добавить таблицу-приёмник. Если бы типы данных не совпадали, пришлось бы сделать промежуточный шаг с компонентом Data Conversion (он интуитивно понятный, поэтому сейчас его не рассматриваем). Перетаскиваем компонент ADO.NET Destination и цепляем к нему стрелку от источника:

Затем тоже щёлкаем дважды по компоненту и выбираем недавно созданную таблицу в тестовой базе:

Затем выбираем пункт Mappings, и студия делает его автоматически, потому что названия столбцов совпадают:

Осталось сохранить параметры и запустить наш импорт (F5):

Как видите, всё прошло успешно, и мы импортировали 6 строк в таблицу AddressType. Однако если вы закроете отладку и нажмёте F5 снова, вы увидите менее радостную картину:

О том что случилось и как этого избежать - далее.

6. Выбор вставки или обновления данных

Помните, как мы добавили первичный ключ по Id? Он и сработал. На самом деле очень удачно сработал, потому что иначе мы бы импортировали каждый следующий раз дубликаты.

Напрашиваются два решения - вставлять данные, только если они отсутствуют в таблице-приёмнике или удалять данные перед вставкой. В принципе, оба подхода имеют право на жизнь, но второй тривиален, поэтому остановимся на первом. И вспомним заодно, что Name в исходной базе могут поменять и это тоже необходимо учесть.

Для начала добавим OLE DB Connection "OleDb.TestSSIS" (для некоторых компонентов нельзя использовать ADO.NET) и перетащим в дизайнер компонент "Lookup":

Дважды щёлкнув по нему, оставляем на первой странице значения по умолчанию, кроме перенаправления вывода в случае отсутствия совпадений:

Затем выбираем соединение и таблицу в пункте "Connection" (это мы уже проходили). Идём в пункт Columns, перетаскиваем мышкой Id на Id (этим задаётся связующий столбец) и щёлкаем по флажку у Name, потому что нам пригодится значение этого столбца, чтобы понять, изменилось ли оно (задаём ему алиас, чтобы было проще потом писать формулы):

Сохраняем изменения и добавляем компонент Conditional Split, чтобы обновлять только те строки, у которых действительно изменилось Name. После чего привязываем два выхода к соответствующим компонентам (в первый раз при перетаскивании попросит выбрать выход):

В Split нужно добавить условие, делаем это с помощью двойного щелчка и редактирования формул (в редакторе можно перетаскивать мышкой названия столбцов, переменные, функции):

Мы назвали нужный выход UpdatedRows, теперь присоединим к нему компонент OLE DB Command, который будет отвечать за обновление:

Осталось совсем немного - написать команду для обновления. Заходим по двойному щелчку в редактор настроек компонента, на первой вкладке выбираем соединение, а во второй - задаём команду обновления:

На следующей вкладке задаём параметры (связывая с доступными столбцами):

Таким образом, мы записываем новые название для тех записей, у которых не совпадают названия в источнике и приёмнике.

Убедимся, что эта схема работает, для этого в тестовой базе удалим одну запись и поменяем другую:

update AddressType set Name = '!' where Id = 1
delete AddressType where Id = 2

Теперь запустим импорт и получим следующий результат:

Как видите, одна запись добавилась в таблицу и одна обновилась. Это можно проверить, запустив запрос:

select * from AddressType

7. Reuse с помощью Expressions

А теперь давайте подумаем, как мы будем импортировать остальные справочники? Можно просто выстроить рядом ещё две аналогичные цепочки, которые будут отличаться названиями - тогда SSIS выполнит их параллельно. Однако, надеюсь, вы не любите дублировать код, как и я.

К счастью, в SSIS можно задавать выражения (expressions) практически для каждого значимого свойства у компонента, а это, вкупе с компонентом Foreach Loop Container позволит нам сделать цикл с импортом однотипных справочников. Не скажу, что это будет элементарно и удобно, но вполне реализуемо.

С другой стороны нужно понимать, что цикл будет выполняться последовательно, а параллельное выполнение могло бы быть быстрее. В нашем случае (небольшие справочники) это не является проблемой.

Начнём с создания ещё двух таблиц в тестовой БД:

create table ContactType(Id int not null, Name nvarchar(50) not null,
    constraint PK_ContactType primary key(Id))
create table PhoneNumberType(Id int not null, Name nvarchar(50) not null,
    constraint PK_PhoneNumberType primary key(Id))

Теперь переименуем компоненты, чтобы последовательность оставалась наглядной:

Теперь вернёмся на дизайнер Control Flow (первая вкладка), добавим компонент Foreach Loop Container, отвяжем Import Dictionaries от Initialize Variables (в данном случае дата нам не нужна и мы можем выполнять эти задачи параллельно), и перетащим его в контейнер:

Теперь подумаем, что нам нужно параметризовать. Попробуем обойтись названием таблицы (его же используем как префикс для Id). Сначала добавляем переменную, в которую будем сохранять название справочника:

Теперь дважды щёлкаем на контейнере и задаём коллекцию (выбираем тип "Foreach Item Enumerator", добавляем один столбец, а потом вводим строки):

После этого соотносим столбец и переменную на следующей странице:

 

Теперь нужно понять, какие свойства компонентов параметризовать. К сожалению, если мы попробуем задать параметры прямо в свойствах компонентов внутри Data Flow, у нас это не получится. Так что придётся копировать текущие значения свойств в какой-нибудь текстовый редактор, возвращаться на Control Flow и задавать выражения.

После небольшой ревизии я нашёл следующие места:

• Dictionary.TableOrViewName = "dbo"."AddressType"
• DictionarySource.SqlCommand = select AddressTypeID AS Id, Name from Person.AddressType
• [Update Dictionary].SqlCommand = UPDATE "AddressType" SET Name = ? WHERE Id = ?
• Lookup.SqlCommand = select * from [dbo].[AddressType]
• Lookup.SqlCommandParam = select * from (select * from [dbo].[AddressType]) [refTable] where [refTable].[Id] = ?

Теперь выберем Import Dictionaries, найдём в его свойствах (F4) свойство Expressions и нажмём на кнопку:

Появляется окно редактирования выражений:

Туда мы и вводим список, что записали выше, вставляя переменную вместо названия таблицы:

• Dictionary.TableOrViewName = "\"dbo\".\"" + @[User::DictionaryName] + "\""
• DictionarySource.SqlCommand = "select " + @[User::DictionaryName]  + "ID AS Id, Name from Person." + @[User::DictionaryName]
• [Update Dictionary].SqlCommand = "UPDATE \"" + @[User::DictionaryName]  + "\" SET Name = ? WHERE Id = ?"
• Lookup.SqlCommand = "select * from [dbo].[" + @[User::DictionaryName] + "]"
• Lookup.SqlCommandParam = "select * from (select * from [dbo].[" + @[User::DictionaryName]  + "]) [refTable] where [refTable].[Id] = ?"

Проверяем то, что получилось:

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

На этом я заканчиваю рассказ о применении SSIS. Дальнейшую информацию о нём можно узнать по ссылкам ниже.

8. Дополнительная информация

• Developer's Guide (Integration Services)
• Блог Мэтта Массона
• The Data Loading Performance Guide - крайне рекомендую, если будете заниматься импортом нормальных объёмов данных.

Со мной вы можете связаться через twitter или блог.