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

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

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

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

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

 

Просмотр созданной задачи 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.

 

Создание параметра в свойстве ConnectionString в Connection Manager
Экран 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.

 

Проверка созданных параметров в файле Project.params
Экран 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.

 

Указание места назначения для проекта SSIS
Экран 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.

 

Параметры команды 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.

  1. В панели Object Explorer в SSMS правой кнопкой мыши щелкните на проекте MyTest и выберите Configure, чтобы открыть окно Project Configuration.
  2. На панели слева выберите References. На странице References щелкните Add. В появившемся диалоговом окне Browse Environments выберите окружение MyTest, как показано на экране 8. Нажмите OK.
  3. На панели слева выберите Parameters. На странице Parameters обратите внимание, что появился параметр пакета MyTest_CM_ConnectionString. Нажмите на знак многоточия (…) рядом с параметром, чтобы вызвать диалоговое окно Set Parameter Value.
  4. В разделе 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 Определяет путь назначения, где развертывается проект