> Часть 2
> Часть 3

SSIS – это инструмент, который позволяет в удобном виде реализовать интеграцию, т.е. реализовать процесс переноса данных из одного источника в другой. Этот процесс иногда называют ETL (от англ. Extract, Transform, Load – дословно «извлечение, преобразование, загрузка»).

Думаю, данный практический курс будет полезен тем, кто хочет изучить SSIS и не знает с чего начать. Здесь в режиме Step By Step мы начнем с самого начала, т.е. установки всего необходимого.

Дальше будет очень много картинок!

Необходимые инструменты для изучения SSIS

В данной статье SSIS будет рассматриваться на примере SQL Server 2014 Developer Edition. Службы Integration Services доступны в SQL Server 2014 начиная с редакции Standard.

Дополнительно необходимо будет скачать и установить инструмент разработчика SQL Server Data Tools (SSDT).

SSDT – это расширение для Visual Studio, которое позволит создавать проекты необходимого нам типа.

Для облегчения процесса установки, я воспользуюсь SSDT для Visual Studio 2012 (VS2012), его можно скачать по ссылке (файл «SSDTBI_VS2012_x86_ENU.exe»):
www.microsoft.com/en-US/download/details.aspx?id=36843

По описанию, данная версия SSDT поддерживает следующие версии SQL Server: SQL Server 2014, SQL Server 2012, SQL Server 2008 и 2008 R2.

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

Установка SQL Server и SSDT

Первым делом установим SQL Server со всеми необходимыми компонентами.

Я все устанавливал на чистую Windows 7 SP 1 (x64), ничего дополнительного кроме указанного ниже устанавливать не придется.

Т.к. курс предназначен для начинающих, то распишу весь процесс установки подробно.

Запускаем установочный файл SQL Server 2014:





Для работы SSIS достаточно будет выбрать следующие компоненты:


Т.к. мне в дальнейшем понадобится Analysis Services (SSAS), то я отметил и его, если он вам не нужен вы можете не выбирать данный компонент.

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



Сделаю, чтобы SQL Agent запускался автоматически:



При необходимости можно изменить Collation, который будет использоваться по умолчанию:



Установлю смешанный режим аутентификации, указав свой пароль для пользователя sa:



Т.к. я еще выбрал Analysis Services, то делаю настройки для него:



Нажимая Next и Install запускаем установку SQL Server и его компонент.

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

Следующим шагом установим SSDT – это расширение для Visual Studio, которое даст нам возможность создавать проекты SSIS. Установщик SSDT ставит минимальную версию оболочки VS, поэтому предварительно устанавливать VS отдельно нет надобности.

Запускаем «SSDTBI_VS2012_x86_ENU.exe», и добравшись до следующего шага выбираем следующий пункт:



Нажимая Next запускаем установку.

После завершения установки на всякий случай перезагружаем компьютер.

Это все, что нам понадобится для изучения SSIS.

Создание демонстрационных баз данных

Запустим SQL Server Management Studio (SSMS) и при помощи скрипта создадим 3 базы данных – первые две (DemoSSIS_SourceA и DemoSSIS_SourceB) будут выступать в роли источников данных, а третья (DemoSSIS_Target) в роли получателя данных:

-- первая БД выступающая в роли источника данных
CREATE DATABASE DemoSSIS_SourceA
GO

ALTER DATABASE DemoSSIS_SourceA SET RECOVERY SIMPLE 
GO

-- вторая БД выступающая в роли источника данных
CREATE DATABASE DemoSSIS_SourceB
GO

ALTER DATABASE DemoSSIS_SourceB SET RECOVERY SIMPLE 
GO

-- БД выступающая в роли получателя данных
CREATE DATABASE DemoSSIS_Target
GO

ALTER DATABASE DemoSSIS_Target SET RECOVERY SIMPLE 
GO

В базах источниках создадим тестовые таблицы и наполним их тестовыми данными:

USE DemoSSIS_SourceA
GO

-- продукты из источника A
CREATE TABLE Products(
  ID int NOT NULL IDENTITY,
  Title nvarchar(50) NOT NULL,
  Price money,
CONSTRAINT PK_Products PRIMARY KEY(ID)
)
GO

-- наполняем таблицу тестовыми данными
SET IDENTITY_INSERT Products ON

INSERT Products(ID,Title,Price)VALUES
(1,N'Клей',20),
(2,N'Корректор',NULL),
(3,N'Скотч',100),
(4,N'Стикеры',80),
(5,N'Скрепки',25)

SET IDENTITY_INSERT Products OFF
GO

USE DemoSSIS_SourceB
GO

-- продукты из источника B
CREATE TABLE Products(
  ID int NOT NULL IDENTITY,
  Title nvarchar(50) NOT NULL,
  Price money,
CONSTRAINT PK_Products PRIMARY KEY(ID)
)
GO

-- наполняем таблицу тестовыми данными
SET IDENTITY_INSERT Products ON

INSERT Products(ID,Title,Price)VALUES
(1,N'Ножницы',200),
(2,N'Нож канцелярский',70),
(3,N'Дырокол',220),
(4,N'Степлер',150),
(5,N'Шариковая ручка',15)

SET IDENTITY_INSERT Products OFF
GO

Создадим таблицу в принимающей базе:

USE DemoSSIS_Target
GO

-- принимающая таблица
CREATE TABLE Products(
  ID int NOT NULL IDENTITY,
  Title nvarchar(50) NOT NULL,
  Price money,
  SourceID char(1) NOT NULL, -- используется для идентификации источника
  SourceProductID int NOT NULL, -- ID в источнике
CONSTRAINT PK_Products PRIMARY KEY(ID),
CONSTRAINT UK_Products UNIQUE(SourceID,SourceProductID),
CONSTRAINT CK_Products_SourceID CHECK(SourceID IN('A','B'))
)
GO

Создание SSIS проекта

Запустим Visual Studio 2012 и выберем один из видов предлагаемой нам настройки среды, так здесь же я откажусь от локальной документации:



Создадим новый проект (File -> New -> Project…):



Для последующего облегчения развертывания зайдем в свойства проекта и изменим опцию ProtectionLevel на DontSaveSensitive:



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



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

Создадим соединения:







Заполняем параметры соединение с БД:



Боевые параметры соединения в дальнейшем можно будет настроить при создании задачи SQL Server Agent.



Для удобства я переименую название соединения на SourceA:



Таким же образом создадим и переименуем соединения для баз DemoSSIS_SourceB и DemoSSIS_Target:



Переименуем пакет, созданный по умолчанию, в «LoadProducts.dtsx»:



Сначала напишем простую логику, которая будет полностью очищать таблицу Products в базе DemoSSIS_Target и снова загружать в нее данные из двух баз данных DemoSSIS_SourceA и DemoSSIS_SourceB.

Для очистки воспользуемся компонентом «Execute SQL Task», который мы при помощи мыши создадим в области «Control Flow»:



Для наглядности можно переименовать название компонент. Зададим ему имя «Delete All Products From Target»:



Для этой цели используется свойство Name.

Дважды щелкнем на этом элементе и пропишем следующие свойства:



Т.к. TSQL команда «TRUNCATE TABLE Products» ничего не возвращает оставим свойства ResultSet равным None.

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

Теперь скинем в область «Control Flow» компонент «Data Flow Task» и переименуем его в «Load Products From Source A», а также протянем к этому компоненту зеленную стрелку от «Delete All Products From Target»:



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

Щелкнув дважды на «Load Products From Source A» мы попадаем в область «Data Flow» этого элемента.

Data Flow Task – это сложный компонент, который имеет свою область, в которой создаются вложенные элементы для работы с потоком данных.

Скинем в эту область компонент «Source Assistant»:





Этот компонент отвечает за получение данных из источника. Дважды щелкнув по нему, мы сможем настроить его:



Пока воспользуемся режимом «Data access mode» равным «Table or view». Это приведет к получению всех строк из таблицы Products. Посмотреть данные можно нажав на «Preview…».

На закладке Columns мы можем выбрать только необходимые нам колонки и при необходимости переименовать их прописав новое имя в колонке «Output Columns»:



Для получателя нужна еще одна дополнительная колонка SourceID, добавим ее к выходному набору при помощи компонента «Derived Column», который переименуем в «Add SourceID», так же протянем синюю стрелку к данному элементу от «OLE DB Source»:



Дважды щелкнем по элементу «Add SourceID» и пропишем значение «A» в виде константы:



Здесь я воспользовался функцией преобразования типа (DT_STR,1,1251) для того чтобы превратить Unicode строку в ANSI.

Теперь создадим компонент «Destination Assistant»:



Направим в него поток от «Add SourceID»:



Дважды щелкнем по «OLE DB Destination» и произведем настройки:



Здесь мы показываем в какую таблицу будет записываться полученный набор.

«Keep identity» используется в случае если в принимающей таблице есть поле с флагом IDENTITY и мы хотим, чтобы значения в него тоже записывались из источника (это аналогично включению опции SET IDENTITY_INSERT Products ON).

Перейдя на закладку Mappings осуществим привязку полей источника с полями получателя:



Так как у нас поля источника и приемника именуются одинаково, то привязка осуществилась автоматически.

Можем протестировать работу пакета и убедиться, что данные залились в таблицу Products базы DemoSSIS_Target.

Запускаем пакет на выполнение из Visual Studio нажав Start или клавишу F5:



Так же пакет можно выполнить, воспользовавшись командой из контекстного меню:



При помощи «Set as StartUp Object» можно задать пакет, который будет запускаться по нажатию на Start (F5).

Какой пакет будет запускаться при нажатии на Start (F5) можно переопределить в свойствах проекта:



По умолчанию будет запускаться пакет открытый в текущий момент, об этом говорит значение StartObjectID равное <Active Package>.

Запустив проект мы должны увидеть следующую картину:



Пакет выполнился без ошибок, о чем говорит зеленый значок и текст в нижней части.

В случае наличия ошибок их можно будет увидеть вкладке Progress.

Нажмем на ссылку «Package execution completed…» или на кнопку «Stop Debugging» расположенную на панели инструментов для остановки выполнения пакета.



Выполним запрос:

USE DemoSSIS_Target
GO

SELECT *
FROM Products

И убедимся, что данные были записаны в принимающую таблицу.

Перейдем в область «Control Flow» и создадим еще один компонент «Data Task Flow», который назовем «Load Products From Source B», протянем на него зеленную стрелку от «Load Products From Source A»:



Двойным щелчком зайдем в область «Data Flow» этого элемента и создадим «Source Assistant»:



Дважды щелкнув на этом элементе, настроим его по-другому:



Выберем режим «SQL command» и пропишем следующий запрос:

SELECT
  ID SourceProductID,
  'B' SourceID,
  Title,
  Price
FROM Products

Дальше сразу создадим компонент «Destination Assistant» и протянем на него синюю стрелку от «OLE DB Source»:





Двойным щелчком зайдем в редуктор этого элемента и настроим его:





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

USE DemoSSIS_Target
GO

SELECT *
FROM Products

Дополнительно в контекстном меню стрелки можно активизировать «Data Viewer»:



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



Для продолжения выполнения пакета нужно нажать на кнопку со стрелкой или просто закрыть окно просмотра данных.

Для отключения этой функции в контекстном меню стрелки выбираем «Disable Date Viewer»:



Для первой части думаю этого будет достаточно.

Создадим сборку:



В результате мы получим файл «C:\SSIS\SSISDemoProject\bin\Development\SSISDemoProject.ispac».

Рассмотрим каким образом делается развертывание этого проекта на SQL Server.

Развертывание SSIS

Все последующие действия будем делать в SSMS.

Создание каталога SSISDB:



Здесь вводим любой пароль.

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



Разворачиваем сам проект:









В завершении мы должны увидеть следующую картину:



После обновления (F5) мы увидим наш проект:

Создание задачи в SQL Server Agent

Создадим задачу в SQL Agent, для выполнения пакета по расписанию:



Создаем новый шаг:



На вкладке «Configuration > Parameters» можно задать параметры пакета (их рассмотрим в следующих частях).

На вкладке «Configuration > Connection Manager» мы можем изменить параметры подключения для каждого соединения, которое мы создали в проекте:



На закладке Advanced можно изменить логику, которая будет использоваться при успешном или неуспешном завершении шага:



Шаг создан:



Осталось создать расписание для данной задачи:



Расписание можно задать разнообразным образом. Думаю, здесь все должно быть интуитивно понятно:



Все, задача создана.

Делаем тестовый запуск:


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

Результат выполнения задачи можно увидеть в следующем журнале:


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

Более подробный отчет о выполнении пакета можно посмотреть при помощи следующего отчета:

Заключение по первой части

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

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

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

Надеюсь данный материал поможет многим сделать первые шаги в изучении данного инструмента и в дальнейшем применить свои знания в работе.

Хороших выходных! Удачи!

> Часть 2