SQL Server Integration Services (SSIS) для начинающих – часть 1

в 17:30, , рубрики: etl, integration services, Microsoft SQL Server, sql, sql server, SSIS

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 1

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:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 2

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 3

Для работы SSIS достаточно будет выбрать следующие компоненты:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 4
Т.к. мне в дальнейшем понадобится Analysis Services (SSAS), то я отметил и его, если он вам не нужен вы можете не выбирать данный компонент.

У меня нет других установленных SQL Server, и я сделаю этот экземпляр используемым по умолчанию:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 5

Сделаю, чтобы SQL Agent запускался автоматически:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 6

При необходимости можно изменить Collation, который будет использоваться по умолчанию:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 7

Установлю смешанный режим аутентификации, указав свой пароль для пользователя sa:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 8

Т.к. я еще выбрал Analysis Services, то делаю настройки для него:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 9

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

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

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

Запускаем «SSDTBI_VS2012_x86_ENU.exe», и добравшись до следующего шага выбираем следующий пункт:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 10

Нажимая 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 и выберем один из видов предлагаемой нам настройки среды, так здесь же я откажусь от локальной документации:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 11

Создадим новый проект (File -> New -> Project…):
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 12

Для последующего облегчения развертывания зайдем в свойства проекта и изменим опцию ProtectionLevel на DontSaveSensitive:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 13

То же самое сделаем в свойствах пакета, который создался по умолчанию:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 14

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

Создадим соединения:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 15

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 16

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 17

Заполняем параметры соединение с БД:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 18
Боевые параметры соединения в дальнейшем можно будет настроить при создании задачи SQL Server Agent.

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 19

Для удобства я переименую название соединения на SourceA:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 20

Таким же образом создадим и переименуем соединения для баз DemoSSIS_SourceB и DemoSSIS_Target:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 21

Переименуем пакет, созданный по умолчанию, в «LoadProducts.dtsx»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 22

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

Для очистки воспользуемся компонентом «Execute SQL Task», который мы при помощи мыши создадим в области «Control Flow»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 23

Для наглядности можно переименовать название компонент. Зададим ему имя «Delete All Products From Target»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 24
Для этой цели используется свойство Name.

Дважды щелкнем на этом элементе и пропишем следующие свойства:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 25

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

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

Теперь скинем в область «Control Flow» компонент «Data Flow Task» и переименуем его в «Load Products From Source A», а также протянем к этому компоненту зеленную стрелку от «Delete All Products From Target»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 26

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

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

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

Скинем в эту область компонент «Source Assistant»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 27

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 28

Этот компонент отвечает за получение данных из источника. Дважды щелкнув по нему, мы сможем настроить его:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 29
Пока воспользуемся режимом «Data access mode» равным «Table or view». Это приведет к получению всех строк из таблицы Products. Посмотреть данные можно нажав на «Preview…».

На закладке Columns мы можем выбрать только необходимые нам колонки и при необходимости переименовать их прописав новое имя в колонке «Output Columns»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 30

Для получателя нужна еще одна дополнительная колонка SourceID, добавим ее к выходному набору при помощи компонента «Derived Column», который переименуем в «Add SourceID», так же протянем синюю стрелку к данному элементу от «OLE DB Source»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 31

Дважды щелкнем по элементу «Add SourceID» и пропишем значение «A» в виде константы:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 32
Здесь я воспользовался функцией преобразования типа (DT_STR,1,1251) для того чтобы превратить Unicode строку в ANSI.

Теперь создадим компонент «Destination Assistant»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 33

Направим в него поток от «Add SourceID»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 34

Дважды щелкнем по «OLE DB Destination» и произведем настройки:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 35
Здесь мы показываем в какую таблицу будет записываться полученный набор.

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

Перейдя на закладку Mappings осуществим привязку полей источника с полями получателя:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 36
Так как у нас поля источника и приемника именуются одинаково, то привязка осуществилась автоматически.

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

Запускаем пакет на выполнение из Visual Studio нажав Start или клавишу F5:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 37

Так же пакет можно выполнить, воспользовавшись командой из контекстного меню:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 38

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

Какой пакет будет запускаться при нажатии на Start (F5) можно переопределить в свойствах проекта:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 39
По умолчанию будет запускаться пакет открытый в текущий момент, об этом говорит значение StartObjectID равное <Active Package>.

Запустив проект мы должны увидеть следующую картину:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 40
Пакет выполнился без ошибок, о чем говорит зеленый значок и текст в нижней части.

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

Нажмем на ссылку «Package execution completed…» или на кнопку «Stop Debugging» расположенную на панели инструментов для остановки выполнения пакета.
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 41

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

USE DemoSSIS_Target
GO

SELECT *
FROM Products

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

Перейдем в область «Control Flow» и создадим еще один компонент «Data Task Flow», который назовем «Load Products From Source B», протянем на него зеленную стрелку от «Load Products From Source A»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 42

Двойным щелчком зайдем в область «Data Flow» этого элемента и создадим «Source Assistant»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 43

Дважды щелкнув на этом элементе, настроим его по-другому:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 44
Выберем режим «SQL command» и пропишем следующий запрос:

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

Дальше сразу создадим компонент «Destination Assistant» и протянем на него синюю стрелку от «OLE DB Source»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 45

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 46

Двойным щелчком зайдем в редуктор этого элемента и настроим его:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 47

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 48

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

USE DemoSSIS_Target
GO

SELECT *
FROM Products

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 49

Дополнительно в контекстном меню стрелки можно активизировать «Data Viewer»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 50

Теперь при запуске пакета на выполнение в этой точке будет сделана остановка и нам будут показаны данные этого потока:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 51
Для продолжения выполнения пакета нужно нажать на кнопку со стрелкой или просто закрыть окно просмотра данных.

Для отключения этой функции в контекстном меню стрелки выбираем «Disable Date Viewer»:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 52

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

Создадим сборку:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 53

В результате мы получим файл «C:SSISSSISDemoProjectbinDevelopmentSSISDemoProject.ispac».

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

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

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

Создание каталога SSISDB:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 54
Здесь вводим любой пароль.

Теперь создаем папку, в которой будет располагаться наш проект:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 55

Разворачиваем сам проект:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 56

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 57

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 58

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 59

В завершении мы должны увидеть следующую картину:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 60

После обновления (F5) мы увидим наш проект:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 61

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

Создадим задачу в SQL Agent, для выполнения пакета по расписанию:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 62

Создаем новый шаг:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 63

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

На вкладке «Configuration -> Connection Manager» мы можем изменить параметры подключения для каждого соединения, которое мы создали в проекте:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 64

На закладке Advanced можно изменить логику, которая будет использоваться при успешном или неуспешном завершении шага:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 65

Шаг создан:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 66

Осталось создать расписание для данной задачи:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 67

Расписание можно задать разнообразным образом. Думаю, здесь все должно быть интуитивно понятно:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 68

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

Делаем тестовый запуск:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 69
Так как шаг у нас всего один, то задача запустится сразу, иначе нужно было бы указать с какого шага нужно начать выполнение.

Результат выполнения задачи можно увидеть в следующем журнале:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 70
В данном журнале можно увидеть успешность завершения каждого шага, а также время выполнения и прочие параметры.

Более подробный отчет о выполнении пакета можно посмотреть при помощи следующего отчета:
SQL Server Integration Services (SSIS) для начинающих – часть 1 - 71

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 72

SQL Server Integration Services (SSIS) для начинающих – часть 1 - 73

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

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

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

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

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

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

Продолжение следует…

Автор: Сергей Меньшов

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js