→ Часть 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