Ms ssis: SQL Server Integration Services (SSIS) для начинающих

Содержание

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

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

Службы SQL Server Integration Services — SQL Server Integration Services (SSIS)

000Z» data-article-date-source=»ms.date»>07/06/2018

Чтение занимает 2 мин

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) Azure-SSIS Integration Runtime в Фабрике данных Azure

SQL Server Службы Integration Services — это платформа для построения решений по интеграции и преобразованию данных уровня предприятия. Службы Integration Services можно использовать при решении сложных бизнес-задач путем копирования и загрузки файлов, загрузки хранилищ данных, очистки и интеллектуального анализа данных, а также управления объектами и данными SQL Server.

Службы Integration Services могут извлекать и преобразовывать данные из ряда таких источников, как файлы XML-данных, неструктурированные файлы и источники реляционных данных, и затем загружать эти данные в один или несколько реляционных объектов.

Службы Integration Services содержат в себе широкий набор встроенных задач и преобразований, а также графические средства для создания пакетов. Кроме того, Службы Integration Services включают базу данных каталога для хранения, выполнения и администрирования пакетов.

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

Получение MSSQL Integration Services

См. дополнительные сведения об установке MSSQL Integration Services с помощью SQL Server, а также о дополнительных скачиваемых компонентах, которые могут вам понадобиться.

Ресурсы

Служба Microsoft SQL Server 2008 R2 Integration Services SSIS

1 Общие сведения о службе Integration Services

Службы Microsoft SQL Server 2008 R2 Integration Services (SSIS) – это платформа для создания решений по интеграции данных, включая пакеты, обеспечивающие извлечение, преобразование и загрузку для хранения данных.

Службы Integration Services включают в себя: графические инструменты и мастеры для построения и отладки пакетов; задачи для выполнения функций потока операций, например операций с FTP, для выполнения инструкций SQL, для отсылки электронных сообщений; источники данных и назначения данных для извлечения и загрузки данных; преобразования для очистки, статистической обработки, слияния и копирования данных; службы управления (службы Integration Services) для администрирования Integration Services; а также API-интерфейс для программирования объектной модели служб Integration Services.

Службы Integration Services заменили службы DTS, которые были первоначально представлены как компоненты SQL Server 7.0.

Пакет – это организованная коллекция подключений, элементов потока управления, элементов потока данных, обработчиков событий, переменных и конфигураций, которая собирается либо с помощью графических средств конструирования, предоставляемых SQL Server 2008 R2 Integration Services (SSIS), либо программно. Затем собранный пакет сохраняется в SQL Server 2008 R2, в хранилище пакетов SSIS или в файловой системе. Пакет – это рабочий блок, который можно получить, выполнить и сохранить.

Пакет сразу после создания – это пустой объект, который ничего не содержит. Чтобы расширить функциональные возможности пакета, в него добавляется поток управления и, при необходимости, один или несколько потоков данных. После создания основного пакета в него добавляются дополнительные функции, такие как ведение журнала, и переменные, расширяющие функциональные возможности пакета. Дополнительные сведения приведены в разделе, посвященном объектам, расширяющим функциональность пакетов.

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

Пакеты часто используются как шаблоны, из которых формируются пакеты, обладающие базовой функциональностью. Можно создать базовый пакет и скопировать его или же обозначить пакет как шаблон. Например, пакет, загружающий и копирующий файлы, а затем извлекающий данные, может содержать задачи «FTP» и «Файловая система» в контейнере «цикл по каждому элементу», который перечисляет файлы в папке. Он также может включать диспетчеры соединений с плоскими файлами для доступа к данным и источники плоских файлов для уточнения данных. Назначение данных изменяется и добавляется к каждому новому пакету после того, как он копируется из базового пакета. Можно создавать пакеты, а затем использовать их как шаблоны для новых пакетов, добавляемых в проект служб Integration Services.

2 Создание пакета выполнения задачи из sql-файла

В среде Business Intelligence Development Studio надо создать новый пустой пакет с использованием шаблона пакета, включающего в себя Integration Services пользовательского пакета, назначенного шаблоном, или скопировать существующий пакет. Вызов среды осуществляется из меню «Пуск Программы MS SQL Server 2008 R2 SQL Server Business Intelligence Development Studio». Тип проекта Integration Services Project (рис. 2.1).

Стратегия развертывания SSIS для SQL Server 2012 | Windows IT Pro/RE

SQL Server Integration Services (SSIS) – решение для извлечения, преобразования и загрузки данных, наиболее эффективное для многих организаций. Хотя это замечательное средство для решения таких задач, как перемещение данных между различными источниками и применения преобразований, существуют некоторые трудности, связанные с развертыванием и настройкой пакетов. Поскольку пакеты SSIS на самом деле представляют собой файлы XML, многие организации избрали подход к развертыванию по принципу «копируй и настраивай». В этом случае пакеты, как правило, копируются вручную или с помощью сценариев в общие места хранения или базу данных msdb, где они выполняются. Информация о конфигурации затем хранится в файлах настройки или специальных таблицах базы данных, предназначенных исключительно для хранения значений настроек SSIS.

Подход «копируй и настраивай» может вызвать проблемы. Однажды я работал над проектом, где все параметры конфигурации SSIS, включая строки соединения, хранились в таблице базы данных. В результате всякий раз, когда мы восстанавливаем копию производственной базы данных в тестовой среде, все пакеты SSIS в тестовом окружении указывают на производственную базу данных, а это не самый лучший вариант.

Пытаясь разрешить подобные проблемы, SSIS 2012 предусматривает новые свойства развертывания пакетов, включая новый каталог и базу данных SSISDB. SSIS 2012 хранит все пакеты, проекты, параметры, разрешения, свойства сервера и историю операций в базе данных SSISDB, объединяя все «подвижные элементы» для любого развертывания SSIS. База данных SSISDB доступна в SQL Server Management Studio (SSMS), если развернуть узел Databases в панели Object Explorer.

Каталог SSISDB дает список проектов, папок, пакетов и информацию о настройках на одном экране. Он находится в отдельной папке под названием Integration Services Catalogs, которая доступна в Object Explorer в SSMS.

Я покажу, как создать и установить каталог SSISDB и базу данных, но сперва вам необходимо узнать о других важных изменениях в SSIS. Начиная с SQL Server 2012, служба SSIS доступна как сервер Integration Services, который является экземпляром механизма базы данных для управления хранилищем пакетов, выполнением и т.д. Служба Integration Services доступна только для обратной совместимости. Рекомендуется использовать сервер Integration Services, чтобы управлять выполнением пакета.

Создание каталога и базы данных SSISDB

Первая задача – создать каталог и базу данных SSISDB. Для начала откройте SSMS и укажите путь к папке Integration Services Catalogs в панели инструментов Object Explorer. Если папка Integration Services Catalogs пуста, потребуется создать каталог и базу данных SSISDB. Для этого правой кнопкой мыши щелкните на папке Integration Services Catalogs и выберите Create Catalog.

Появится диалоговое окно Create Catalog, показывающее, что название базы данных будет SSISDB. Вы можете его изменить. В диалоговом окне укажите пароль, который будет применяться механизмом шифрования для этой базы данных, и выберите флажок Enable CLR Integration. По желанию, вы также можете выбрать Enable automatic execution («Разрешить автоматическое выполнение») хранимых процедур Integration Services в SQL at Server startup check box. Щелкнув OK, вы увидите, что каталог SSISDB теперь доступен в папке Integration Services Catalogs в панели Object Explorer.

Каталог SSISDB имеет специфическую структуру для организации пакетов. Вы группируете отдельные пакеты SSIS в проекты, и помещаете группу с соответствующими проектами в папку. Папка будет находиться точно под каталогом SSISDB. На приведенном рисунке показан принцип структурирования объектов.

Рисунок. Структура каталога SSISDB

Для наглядности давайте создадим папку. Правой кнопкой мыши щелкните SSISDB и выберите Create Folder. Назовите папку MyTest и добавьте значимое описание, если считаете нужным.

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

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

Запустите SQL Server Data Tools. Это инструмент, заменяющий Business Intelligence Development Studio (BIDS) в SQL Server 2012. Из меню File выберите New и затем Project. Выделите Integration Services Project и назовите его Test_Project. Так вы создадите новый проект SSIS, уже содержащий пакет под названием Package.dtsx.
Если пакет Package.dtsx еще не открыт, откройте его. Найдите область Connection Managers внизу дизайнера пакетов. Правой кнопкой мыши щелкните на области Connection Managers и выберите New OLE DB Connection. В появившемся окне нажмите New, чтобы запустить диалоговое окно Connection Manager.
В окне Connection Manager, показанном на экране 1, введите имя своего экземпляра базы данных SQL Server в поле Server name. Выберите способ подключения пакета к экземпляру базы данных SQL Server. Вы можете использовать Windows Authentication или SQL Server Authentication, в этом случае необходимо предоставить информацию о регистрации SQL Server. В разделе Connect to a database выберите главную базу данных (master database). Щелкните Test Connection, чтобы убедиться, что все настроено правильно.
После того, как соединение создано, вернитесь к области Connection Managers в SQL Server Data Tools. Правой кнопкой мыши щелкните на только что созданном соединении, выберите Rename и введите новое имя MyTest_CM.

Экран 1. Указание информации о соединении

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

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

1. Откройте инструмент SSIS в SQL Server Data Tools, выбрав SSIS, затем щелкните на SSIS Toolbox.

2. В SSIS Toolbox найдите значок Data Flow Task и перетащите его в область конструктора пакетов. Откройте задачу Data Flow, дважды щелкнув по значку, чтобы открылось представление Data Flow.

3. Перетащите значок OLE DB Source из панели инструментов SSIS Toolbox в область конструктора пакетов. Правой кнопкой мыши щелкните на OLE DB Source и выберите пункт меню Edit.

4. В OLE DB Source Editor выберите MyTest_CM в раскрывающемся списке OLE DB connection manager. В списке режима доступа к данным Data access mode выберите SQL command. В поле SQL command text введите запрос

SELECT table_name FROM information_schema.tables

Этот запрос возвращает список всех таблиц в главной базе данных. Нажмите Preview, чтобы удостовериться, что запрос работает. Если это так, нажмите OK, чтобы закрыть OLE DB Source Editor.

5. Перетащите значок OLE DB Destination из SSIS Toolbox в область конструктора пакетов. Подключите вывод данных компонента OLE DB Source к компоненту OLE DB Destination.

6. Дважды щелкните на компоненте OLE DB Destination и выберите Edit. В OLE DB Destination Editor выберите MyTest_CM в раскрывающемся списке OLE DB connection manager. В списке Data access mode выберите Table or view — fast load. Нажмите кнопку New рядом с раскрывающимся списком Name of the table or the view. Появится диалоговое окно Create Table. Оно будет содержать код для создания таблицы OLE DB Destination на вашем экземпляре SQL Server. Нажмите OK.

7. Щелкните OK, чтобы закрыть OLE DB Source Editor и создайте таблицу в основной базе данных. На экране 2 показано, как выглядит завершенная задача Data Flow.

Экран 2. Просмотр созданной задачи Data Flow

8. Убедитесь, что таблица OLE DB Destination создана, выполнив следующий запрос в SSMS:

SELECT * FROM [OLE DB Destination]

Создание параметров

Пакет SSIS, который вы только что создали, готов для тестирования. Менеджер соединений пакета теперь указывает на экземпляр базы данных разработчика, но вам нужно протестировать пакет на тестовом экземпляре базы данных. Один из способов изменить строку подключения – открыть менеджер соединений и изменить строку соединения, чтобы она указывала на экземпляр тестовой базы данных. Однако изменять пакет, чтобы изменить строку подключения – дело не только утомительное, но и требующее повторного развертывания пакета в тестовой среде. Вот здесь нам и пригодятся параметры.

SSIS позволяет создавать параметры, с помощью которых можно задать временные значения рабочего процесса. Создавать параметры можно на уровне пакета или проекта в SSIS. Типичный случай, когда вы хотите использовать общую переменную в многочисленных запросах в проекте. Например, вы можете создать параметр для переменной и значения конечной даты последнего квартала. Когда меняется квартал, вы обновляете значение переменной; все запросы, которые должны применять этот параметр, начнут использовать новую дату. Другой пример – когда вы хотите сделать строки подключения базы данных настраиваемыми. В этом случае вам нужно связать значения строки подключения с параметрами, которые вы можете изменять в зависимости от того, в какой среде находитесь – разработки, тестовой или операционной.

Чтобы узнать, как это работает, давайте создадим параметр для менеджера соединений MyTest_CM в тестовом пакете. В области Connection Managers в SQL Server Data Tools правой кнопкой мыши щелкните на менеджере соединений MyTest_CM и выберите Parameterize. Появится диалоговое окно, показанное на экране 3.

Экран 3. Создание параметра в свойстве ConnectionString в Connection Manager

Давайте рассмотрим различные параметры в диалоговом окне.

Property. Вы можете использовать эту область, чтобы выбрать свойство менеджера подключений, которое хотите параметризовать. Для этого выберите строку ConnectionString, чтобы заменить строку подключения менеджера соединений любым значением, заданным в параметре. Помимо строк соединений, вы можете параметризовать и другие свойства, такие как имя сервера, имя пользователя и пароль. Обратите внимание, что свойства, которые могут быть параметризованы, будут изменяться в зависимости от компонента SSIS. Так, свойства, которые вы можете параметризовать для менеджера подключений, будут отличаться от свойств для задачи Data Flow.
Do not use parameter. Если вы выберете этот вариант, менеджер подключений будет игнорировать любые параметры и вместо них использовать значения, которые были заданы, когда был создан пакет.
Use existing parameter. Если вы выберете этот вариант, менеджер подключений будет использовать указанные существующие параметры.
Create new parameter. Этот вариант понадобится, если вы захотите создать новый параметр. Вы можете задать имя параметра и диапазон. Хотя это и необязательно, вы можете уточнить описание и значение. В данном случае имя параметра – MyTest_CM_ConnectionString, поскольку менеджер соединения называется MyTest_CM. Поле Value будет автоматически заполнено по умолчанию строкой подключения для MyTest_CM, ее можно изменить позже. Вы можете добавить параметр к пакету или проекту, в зависимости от выбранной области. В основном, если вы устанавливаете опцию Scope к Project, параметр будет доступен во всем проекте, так что другие пакеты в проекте тоже могут его использовать. Если вы устанавливаете переключатель Scope в Package, параметр будет доступен только внутри пакета и не на уровне проекта. В данном случае установите переключатель Scope в режим Project.
Sensitive. Если вы установите этот флажок, важные значения параметра будут шифроваться в каталоге и выводиться как значение NULL в SSMS или результатах запроса T-SQL. Используйте эту функцию, параметризируя пароль. В данном случае флажок Sensitive устанавливать не нужно.
Required. Если вы установите этот флажок, значение параметра должно быть задано прежде, чем пакет будет выполнен. В нашем случае флажок Required устанавливать не нужно.

После внесения информации в диалоговом окне Parameterize, нажмите OK. Проверьте файл Project.params в своем решении, чтобы убедиться, что ваши параметры успешно созданы, как показано на экране 4.

Экран 4. Проверка созданных параметров в файле Project.params

Файл Project.params будет содержать все параметры уровня проекта. Для просмотра содержимого данного файла XML правой кнопкой мыши щелкните на нем в панели Solution Explorer и выберите View Code.

На этой стадии вы можете нажать F5, чтобы построить и протестировать пакет. Затем проверьте таблицу OLE DB Destination в своей главной базе данных, чтобы убедиться в получении ожидаемых результатов.

Развертывание проекта

SSIS 2012 предусматривает два варианта развертывания пакета: режим развертывания пакета для отдельных пакетов и режим развертывания проекта для того, чтобы развернуть весь проект целиком. Для пакетов, созданных с помощью SQL Server Data Tools, установка по умолчанию – режим развертывания проекта, который вам и предстоит использовать. Возможно развертывание пакета из SQL Server Data Tools или из командной строки. Обратите внимание, что SSIS 2012 не поддерживает MSBuild, поэтому вы не можете его применять для построения или управления своими проектами SSIS 2012.

Развертывание из SQL Server Data Tools. В панели Solution Explorer правой кнопкой мыши щелкните на своем проекте и выберите Deploy, чтобы вызвать мастер Integration Services Deployment Wizard. Нажмите Next на странице Introduction. На странице Select Destination введите имя экземпляра SQL Server в текстовом окне Server name. В текстовом окне Path укажите место, где вы хотите развернуть пакет. В данном примере вы хотите развернуть его в папке MyTest, созданной в разделе «Создание каталога и базы данных SSISDB». Как показано на экране 5, вы можете указать имя проекта (Test_Project) как часть пути. Нажмите Next.

Экран 5. Указание места назначения для проекта SSIS

На странице Review просмотрите выбранные элементы и нажмите Deploy. После того, как мастер завершит развертывание, вернитесь к Object Explorer в SSMS и удостоверьтесь, что ваш пакет развернут в папке MyTest в каталоге SSISDB.

Развертывание из командной строки. В большинстве крупных организаций развертывание выполняет администратор базы данных или системный администратор. В подобных средах принято использовать сценарии развертывания, насколько это возможно, для того, чтобы сделать развертывание более управляемым, когда количество пакетов SSIS увеличивается. В SQL Server 2012 возможно развертывание пакетов SSIS с помощью инструмента командной строки ISDeploymentWizard.exe. Этот инструмент работает с файлом. ispac, который создается в каталоге проекта BIN, когда вы разрабатываете проект в SQL Server Data Tools. В файле. ispac проекта обычно содержится следующее:

манифест проекта;
все пакеты, принадлежащие проекту;
файл параметров Project.params.

Если вы хотите просмотреть содержимое файла, вы можете изменить расширение с .ispac на .zip и после этого открыть его в Windows Explorer.

Чтобы развернуть проект Test_Project из командной строки, в первую очередь необходимо скопировать файл. ispac в общую папку. Затем в окне командной строки Windows пройдите к папке, содержащей файл. ispac и выполните команду ISDeploymentWizard:

«%ProgramFiles%\Microsoft SQL Server\110\DTS\Binn
\isdeploymentwizard.exe» /S /ST:File
/SP:Test_Project.ispac /DS:
/DP:/SSISDB/MyTest/Test_Project

В приведенной таблице описан каждый параметр команды ISDeploymentWizard.

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

Создание переменных окружения и среды

В каталоге SSISDB каждая папка SSIS может иметь одно или более серверных окружений. Окружение есть не что иное, как коллекция переменных среды, специфичных для папки, где находится окружение.

Важно понимать различие между параметрами и переменными в данном контексте. Вы устанавливаете параметры на уровне пакета или проекта, когда создаете пакет или проект в SQL Server Data Tools. Переменные являются частью среды в SSISDB. Параметры находятся в вашем проекте SQL Server Data Tools, тогда как переменные находятся в SSISDB.

Давайте создадим среду для тестового проекта. В панели Object Explorer в SSMS пройдите к папке MyTest каталога SSISDB и раскройте ее. Правой кнопкой мыши щелкните на папке Environments и выберите Create Environment, как показано на экране 6. Назовите среду MyTest.

Экран 6. Создание окружения

Теперь, когда окружение создано, вы создаете переменные среды. Для этого дважды щелкните на окружении MyTest, чтобы раскрыть диалоговое окно Environment Properties. На панели слева выберите страницу Variables. Создайте новую переменную под названием Environment_Dev с типом данных String, как показано на экране 7. Оставьте поле Description пустым. В поле Value введите строку подключения для экземпляра разработчика SQL Server.

Экран 7. Создание переменной окружения

На экране 7 обратите внимание на ссылку на страницу Permissions. С ее помощью вы можете предоставлять права Read или Modify учетным записям или ролям SQL Server в окружении. Таким образом, вы можете защитить строки подключения, пароли и другие элементы в своем окружении. Для этого вам не требуется устанавливать других прав.

Сопоставление параметров проекта с переменными среды

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

Далее показано, как сопоставить параметры проекта с переменными среды для проекта MyTest.

В панели Object Explorer в SSMS правой кнопкой мыши щелкните на проекте MyTest и выберите Configure, чтобы открыть окно Project Configuration.
На панели слева выберите References. На странице References щелкните Add. В появившемся диалоговом окне Browse Environments выберите окружение MyTest, как показано на экране 8. Нажмите OK.
На панели слева выберите Parameters. На странице Parameters обратите внимание, что появился параметр пакета MyTest_CM_ConnectionString. Нажмите на знак многоточия (…) рядом с параметром, чтобы вызвать диалоговое окно Set Parameter Value.
В разделе Value диалогового окна Set Parameter Value выберите вариант Use environment variable и выберите Environment_Dev из раскрывающегося списка доступных переменных окружения, как показано на экране 9. Нажмите OK.

Экран 8. Выбор окружения при сопоставлении параметра проекта переменной окружения

Экран 9. Выбор переменной среды для сопоставления

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

Несколько советов

Я показал вам шаги, которые необходимо выполнить, реализуя решение SSISDB. Вот еще несколько советов:

Используйте параметры уровня проекта для всех строк подключения и других объектов конфигурации в вашем проекте.
Развертывайте проект SSIS в отдельной папке каждого экземпляра SSISDB, затем создавайте раздельные окружения (и переменные в этих окружениях). С данной установкой вы можете сопоставить параметры проекта с различными переменными окружения, чтобы настроить выполнение пакета. Защитите свои окружения, предоставив права Modify исключительно администраторам базы данных (DBA) или системным администраторам. Предоставьте разработчикам права Read.
Убедитесь, что вы сопоставили переменные среды с параметрами проекта. Если вы забудете это сделать, ваши пакеты будут выполняться с теми значениями, которые были определены при развертывании пакета.

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

Таблица. Параметры команды ISDeploymentWizard

Опция Описание

«%ProgramFiles%\Microsoft SQL Server\110\DTS\Binn\isdeploymentwizard.exe» Определяет путь к ISDeploymentWizard.exe. Если вы установили SQL Server в местоположении не по умолчанию, соответственно, вам нужно изменить путь

/S Указывает на автоматическую установку (т.е. без вывода диалогового окна)

/ST:File Указывает, что источник пакета развертывание проекта — файл. Источником также может быть экземпляр SQL Server, и в этом случае вы используете /ST:Server

/SP:Test_Project.ispac Определяет файл развертывания. ispac.

/DS: Определяет сервер назначения, где развертываете проект. Вам нужно изменить на имя экземпляра SQL Server

/DP:/SSISDB/MyTest/Test_Project Определяет путь назначения, где развертывается проект

Поделитесь материалом с коллегами и друзьями

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