ssis пакеты что это
Создание SSIS пакетов для новичков
Для реализации простенького пакета необходимо установить надстройку SSDT (Sql server data tools) для вашей Visual Studio.
Допустим, вам необходимо перенести данные с одного сервера на другой. Первыми мыслями о решении данной задачи, как правило, являются следующие варианты: выгрузить данные с одного сервера в excel и воспользоваться мастером импорта или воспользоваться линком между серверами, если он, конечно, есть.
Однако линка может и не быть, а задача по переносу данных может оказаться периодичной. Тогда вам каждый раз придется повторять выгрузку данных и их импорт. Это неудобно.
В таких случаях на помощь как раз приходит SSIS. В других случаях, кстати тоже.
Что же представляет из себя SSIS? Это набор контейнеров, соединяемых между собой. Программа состоит из потока управления (Control Flow), который может включать в себя поток данных (Data Flow). Вот так выглядит стартовое окно проекта в Visual Studio 2017.
Итак, нам необходимо перенести данные таблицы с одного сервера на другой. Для начала нам необходимо создать соединения к этим серверам. Ниже представлен процесс создания:
Из списка выбираем необходимый нам тип соединения. Мы рассмотрим создание соединение OLEDB.
После того, как мы добавили необходимые соединения наступило время для создания в нашем Control Flow задачи потока данных. Для того в SSIS toolbox необходимо выбрать контейнер Data Flow Task (Задача потока данных).
Далее необходимо перейти в контейнер, который мы только что создали и выбрать Источник данных (Source) и назначение данных(Destination).
Перейдем к настройке источника данных. Здесь нам необходимо выбрать соединение, где находится таблица, из которой мы хотим перенести данные. Далее выбираем таблицу. Стоит отметить, что также необходимые данные можно выбрать через Sql запрос (для этого необходимо выбрать Sql command при выборе data access mode).
После настройки источника необходимо настроить назначение данных. Для этого выбираем соединение и таблицу, в которую будем переливать данные. После чего переходим на вкладку Mappings, для того чтобы сопоставить столбцы. Очень важно, чтобы у вас совпадали типы данных, потому что иначе пакет не отработает корректно.
Управление пакетами (службы SSIS)
Управление пакетами включает в себя операции мониторинга, управления, импорта и экспорта пакетов.
Хранилище пакетов
Службы Службы Integration Services предоставляют две папки верхнего уровня для доступа к пакетам служб:
Пакеты, сохраняемые в базе данных msdb, хранятся в таблице с именем sysssispackages. При сохранении пакетов в базе данных msdb их можно сгруппировать в логические папки. Использование логических папок помогает организовывать пакеты по назначению или отфильтровывать пакеты в таблице sysssispackages. Логические папки можно создавать с помощью SQL Server Management Studio. По умолчанию все логические папки, добавляемые в базу данных msdb, автоматически включаются в хранилище пакетов.
Создаваемые логические папки представлены как строки в таблице sysssispackagefolders базы данных msdb. Столбцы folderid и parentfolderid в таблице sysssispackagefolders определяют иерархию папок. Корневые логические папки в базе данных msdb представлены строками таблицы sysssispackagefolders, которые содержат значение NULL в столбце parentfolderid. Дополнительные сведения см. в разделах sysssispackages (Transact-SQL) и sysssispackagefolders (Transact-SQL).
В папке Выполняемые пакеты нет вложенных папок, и она не может быть расширена.
Для просмотра списка пакетов в хранилище пакетов следует открыть среду SQL Server Management Studio и подключиться к службам Службы Integration Services.
Мониторинг выполняемых пакетов
Можно остановить выполнение пакета в папке Выполняемые пакеты, щелкнув правой кнопкой мыши пакет и выбрав Остановить.
Просмотр пакетов в SSMS
Подключение к службам Integration Services
Нажмите кнопку Пуск, укажите пункт Все программы, пункт Microsoft SQL Server, а затем выберите команду Среда SQL Server Management Studio.
Просмотр пакетов, управляемых службой служб Integration Services
В обозревателе объектов разверните папку «Сохраненные пакеты».
Разверните вложенные папки в папке «Сохраненные пакеты», чтобы показать пакеты.
Импорт и экспорт пакетов
Можно выполнять импорт и экспорт пакетов между следующими типами хранилищ:
Папки файловой системы в любом месте этой файловой системы.
Папки в хранилище пакетов служб SSIS. Две папки по умолчанию с именами File System и MSDB.
Службы Integration Services предоставляют возможность для импорта и экспорта пакетов и посредством этого изменения формата и места хранения пакетов. С помощью функций импорта и экспорта можно добавлять пакеты в файловую систему, хранилище пакетов или базу данных msdb, а также копировать пакеты из одного формата хранения в другой. Например, пакеты, сохраненные в msdb, можно скопировать в файловую систему и наоборот.
Можно также скопировать пакет в другой формат с помощью программы командной строки dtutil (dtutil.exe). Дополнительные сведения см. в статье dtutil Utility.
Можно выполнять импорт и экспорт пакетов служб Службы Integration Services между следующими типами хранилищ.
Однако существует ряд ограничений на импорт и экспорт пакетов между разными версиями SQL Server:
С экземпляра SQL Server 2008можно импортировать пакеты из экземпляра SQL Server 2005 (9.x), но нельзя экспортировать их в экземпляр SQL Server 2005 (9.x).
С экземпляра SQL Server 2005 (9.x)нельзя импортировать пакеты из экземпляра SQL Server 2008или экспортировать их туда.
Импорт пакета с помощью среды SQL Server Management Studio
Нажмите кнопку Пуск, укажите пункт Microsoft SQL Server и выберите пункт SQL Server Management Studio.
В диалоговом окне Соединение с сервером установите следующие параметры.
В поле Тип сервера выберите Службы Integration Services.
В поле Имя сервера введите имя сервера или щелкните
и найдите нужный сервер.
Если обозреватель объектов не открыт, в меню Вид выберите пункт Обозреватель объектов.
Разверните вложенные папки и найдите папку, в которую нужно выполнить импорт пакета.
Щелкните папку правой кнопкой мыши и выберите пункт Импорт пакета. А затем выполните одно из следующих действий:
Чтобы выполнить импорт из экземпляра SQL Server, выберите параметр SQL Server и укажите сервер и метод проверки подлинности. При выборе проверки подлинности SQL Server укажите имя пользователя и пароль.
При необходимости обновите название пакета.
Чтобы обновить уровень защиты пакета, нажмите кнопку обзора (…) и выберите иной уровень защиты с помощью диалогового окна Уровень защиты пакета. При выборе параметра Шифровать конфиденциальные данные паролем или Шифровать все данные паролем введите и подтвердите пароль.
Экспорт пакета с помощью среды SQL Server Management Studio
Нажмите кнопку Пуск, укажите пункт Microsoft SQL Server и выберите пункт SQL Server Management Studio.
В диалоговом окне Соединение с сервером установите следующие параметры.
В поле Тип сервера выберите Службы Integration Services.
В поле Имя сервера введите имя сервера или щелкните
и найдите нужный сервер.
Если обозреватель объектов не открыт, в меню Вид выберите пункт Обозреватель объектов.
Разверните вложенные папки и выберите пакет для экспорта.
Щелкните правой кнопкой мыши пакет, выберите пункт Экспорт и выполните одно из следующих действий:
Нажмите кнопку обзора (…) и разверните папку Пакеты служб SSIS, чтобы выбрать папку, в которую нужно сохранить пакет. При необходимости измените имя пакета по умолчанию и нажмите кнопку ОК.
Чтобы обновить уровень защиты пакета, нажмите кнопку обзора (…) и выберите иной уровень защиты с помощью диалогового окна Уровень защиты пакета. При выборе параметра Шифровать конфиденциальные данные паролем или Шифровать все данные паролем введите и подтвердите пароль.
Диалоговое окно «Импорт пакета» справочника по пользовательскому интерфейсу
Параметры
Размещение пакета
Выберите тип места хранения, в которое импортировать пакет. Доступны следующие варианты:
SQL Server
Файловая система
Хранилище пакетов служб SSIS
Server
Введите имя сервера или выберите его из списка.
При возможности используйте проверку подлинности Windows.
Тип проверки подлинности
Выберите тип проверки подлинности.
User name
При использовании проверки подлинности SQL Server укажите имя пользователя.
Пароль
При использовании проверки подлинности SQL Server укажите пароль.
Путь пакета
Введите путь к пакету или нажмите кнопку просмотра (…) и определите местоположение пакета.
Имя пакета
При необходимости переименуйте пакет. По умолчанию это имя импортируемого пакета.
Уровень защиты
Щелкните кнопку просмотра (…) и измените уровень защиты в диалоговом окне Уровень защиты пакета. Дополнительные сведения см. в разделе Диалоговое окно уровня защиты пакета и проекта.
Диалоговое окно «Экспорт пакета» справочника по пользовательскому интерфейсу
Параметры
Размещение пакета
Выберите тип хранилища для экспорта пакета. Доступны следующие варианты:
SQL Server
Файловая система
Хранилище пакетов служб SSIS
Server
Введите имя сервера или выберите его из списка.
При возможности используйте проверку подлинности Windows.
Тип проверки подлинности
Выберите тип проверки подлинности.
User name
При использовании проверки подлинности SQL Server укажите имя пользователя.
Пароль
При использовании проверки подлинности SQL Server укажите пароль.
Путь пакета
Введите путь модуля или нажмите кнопку обзора (. ) и выберите папку, в которой должен быть сохранен пакет.
Уровень защиты
Нажмите кнопку обзора (. ) и обновите уровень защиты в диалоговом окне Уровень защиты пакета. Дополнительные сведения см. в разделе Диалоговое окно уровня защиты пакета и проекта.
Резервное копирование и восстановление пакетов
Дополнительные сведения о резервном копировании и восстановлении базы данных msdb см. в следующих разделах:
Службы Integration Services включают в себя программу командной строки dtutil (dtutil.exec), которая может использоваться для управления пакетами. Дополнительные сведения см. в статье dtutil Utility.
Файлы конфигурации
Файлы конфигурации, содержащиеся в пакетах, сохраняются в файловой системе. Эти файлы не копируются при создании резервной копии базы данных msdb, поэтому необходимо регулярно выполнять резервное копирование файлов конфигурации в рамках плана защиты пакетов, сохраняемых в msdb. Чтобы включить конфигурации в резервную копию базы данных msdb, следует рассмотреть использование типа конфигурации SQL Server вместо файлов конфигурации.
SQL Server Integration Services (SSIS) для начинающих – часть 2
В этой части изменим логику загрузки справочника Products:
Итого в этой части мы познакомимся с четырьмя новыми компонентами: Union All, Lookup, OLE DB Command и Multicast.
Дальше так же будет очень много картинок.
Продолжим знакомство с SSIS
Создадим новый пакет:
И переименуем его в «LoadProducts_ver2.dtsx»:
В области «Control Flow» создадим «Data Flow Task»:
Двойным щелчком по элементу «Data Flow Task» зайдем в его область «Data Flow». Создадим два элемента «Source Assistant» для соединений SourceA и SourceB. Переименуем эти элементы в «Source A» и «Source B» соответственно:
«Source A» настроим следующим образом:
В целях демонстрации больших возможностей за раз, здесь я намеренно отпустил SourceID.
«Source B» настроим следующим образом:
Текст запроса:
В результате набор A у нас будет иметь 3 колонки [SourceProductID, Title, Price], а набор B будет иметь 4 колонки [SourceProductID, SourceID, Title, Price].
Воспользуемся элементом «Union All», чтобы объединить данные из 2-х наборов в один. Направим в него синие стрелки из «Source A» и «Source B»:
Каким образом делается сопоставление колонок двух входящих наборов, можно увидеть дважды щелкнув на элементе «Union All»:
Как мы видим, здесь сделалось автоматическое сопоставление колонок имена которых совпадают. При необходимости мы можем сделать свое сопоставление, для примера добавим колонку SourceID из второго набора:
В данном случае значения SourceID набора «Source A» будут равны NULL.
Объединение двух наборов в данном случае делается на стороне SSIS. Здесь стоит обратить внимание на то, что базы источники и принимающая база могут располагаться на разных серверах/экземплярах SQL Server, по этой причине мы не всегда сможем так просто написать SQL запрос используя в нем таблицы из разных баз с применением SQL-операции UNION или JOIN (который можно было использовать вместо Lookup описанного ниже).
Для того чтобы заменить NULL значения на «A» воспользуемся компонентом «Derived Column» в который направим поток из «Union All»:
Двойным щелчком зайдем в редактор «Derived Column» и настроим его следующим образом:
Проделаем следующее (мышь в помощь):
Теперь при запуске пакета на выполнение вы сможете увидеть набор, который получился в результате:
Здесь видно, что на этом этапе (до Derived Column) в колонке SourceID для строк первого набора стоят значения NULL.
Для того чтобы определить была ли добавлена ранее запись в базу DemoSSIS_Target воспользуемся компонентом Lookup:
Дважды щелкнув по нему настроим данный элемент:
Здесь мы скажем, что те строки, для которых не найдено соответствие, мы будем перенаправлять в поток «no match output». В этом случае на выходе мы получим 2 набора «Lookup Match Output» и «Lookup No Match Output».
Например, если выставить значение «Ignore failure», то в строках, для которых не нашлось сопоставления в поле TargetID (см. ниже) будет записано значение NULL и все строки будут возвращены через один набор «Lookup Match Output».
«Full cache» говорит о том, что набор, который будет использоваться в качестве справочника одним SQL запросом (см.на следующей вкладке) будет полностью загружен в память и строки будут сопоставляться уже с кэша без повторных обращений к SQL Server.
Если же выбрать «Partial cache» или «No cache», то на вкладке Advanced можно будет прописать запрос с параметрами, который будет выполняться для сопоставления каждой строки входящего набора. Для интереса можно поиграться с этим свойством и через SQL Server Profiler посмотреть какие будут формироваться запросы при выполнении пакета.
На следующей вкладке нам нужно определить набор, который будет выступать в роли справочника:
Я прописал здесь запрос:
На следующей вкладке нужно указать по каким полям делается поиск строки в справочнике и какие колонки из справочника нужно добавить в выходной набор (если это нужно):
Для определение связи нужно при помощи мыши перетащить поле SourceProductID на SourceProductID и поле SourceID на SourceID.
Добавим компонент «Destination Assistant» для вставки записей с потока «Lookup No Match Output»:
Перетащим синюю стрелку с «Lookup» на «OLE DB Destination» и в диалоговом окне выберем поток «Lookup No Match Output»:
В итоге мы получим следующее:
Дважды щелкнув по «OLE DB Destination» настроим его:
Обработку вставки новых записей мы сделали.
Теперь для обновления ранее вставленных записей воспользуемся компонентом «OLE DB Command» и перенесем на него синюю стрелку от Lookup:
В этот компонент автоматически будет направлен поток «Lookup Match Output», т.к. поток «Lookup No Match Output» мы уже выбрали ранее:
Дважды щелкнем на «OLE DB Command» и настроим его:
Пропишем следующий запрос на обновление:
На следующей вкладке укажем каким образом будут задаваться параметры на основании данных строк входящего набора «Lookup Match Output»:
Через SSMS добавим новых продуктов в базу DemoSSIS_SourceB:
Для того чтобы отследить как менялись данные, вы можете, перед запуском пакета на выполнение, в необходимых местах сделать «Enable Data Viewer»:
Запустим пакет на выполнение:
В итоге мы должны увидеть, что 3 строки было вставлено при помощи компонента «OLE DB Destination» и 10 строк обновлено при помощи компонента «OLE DB Command».
Запрос прописанный в «OLE DB Command» выполнился для каждой строки входящего набора, т.е. в данном примере 10 раз.
В «OLE DB Command» можно прописать более сложную логику на TSQL, например, сделать проверку, были ли изменены Title или Price, и делать обновление соответствующей строки только если какое-то из значений отличается.
Для наглядности добавим новую колонку в таблицу Products в базе DemoSSIS_Target:
Давайте теперь пропишем следующую команду:
После чего переопределим привязку параметров согласно их очередности в тексте команды:
Сделаем в базе DemoSSIS_SourceA обновление:
И снова запустим проект на выполнение. В результате после очередного запуска пакета на выполнение, UPDATE должен будет выполниться только 1 раз, только для этой записи.
После выполнения пакета проверим это при помощи запроса:
В рамках данной части рассмотрим еще компонент «Multicast». Данный компонент позволяет получить из одного потока несколько. Это может быть полезно, когда одни и те же данные необходимо записать в два или более разных мест – т.е. входит один набор, а выходит столько его копий сколько нам нужно, и с каждой копией этого набора мы можем делать что захотим.
Для примера создадим в базе DemoSSIS_Target еще одну таблицу LastAddedProducts:
Для очистки этой таблицы добавим в область «Control Flow» компонент «Execute SQL Task» и пропишем в нем команду «TRUNCATE TABLE LastAddedProducts»:
Перейдем в область «Data Flow» компонента «Data Flow Task» и добавим компонент следующим образом:
Обратите внимание на желтый восклицательный знак – это произошло из-за того, что мы добавили колонку UpdatedOn и не привязали ее. Зайдем в элемент «OLE DB Destination», перейдем на вкладку Mappings оставим для колонки UpdatedOn в качестве входящего поля Ignore и нажмем OK:
Создадим еще один элемент «OLE DB Destination» и перетащим на него вторую синюю стрелку от элемента Multicast:
Переименуем для наглядности:
Настроим «To LastAddedProducts»:
Удалим через SSMS три последние вставленные записи:
И запустим пакет на выполнение:
В итоге добавление произошло в 2 таблицы – Products и LastAddedProducts.
Заключение по второй части
Чтобы не нарушать ссылочную целостность, иногда запись в принимающей таблице удаляется логически, для этого, например, можно в эту таблицу добавить поле Deleted типа bit (флаг логического удаления) или DeletedOn типа datetime (дата/время логического удаления).
Порой на сервере, на котором располагается база Target делается вспомогательная промежуточная база (обычно ее называют Staging) и первым делом «сырые» данные из Source загружаются в нее. Так как теперь Target и Staging находятся на одном сервере, то вторым шагом мы можем легко написать SQL-запрос (например, используя SQL-конструкцию MERGE или запрос с применение конструкции JOIN), который оперирует с наборами обеих этих баз.
SSIS достаточно интересный инструмент, который на мой взгляд не помешает иметь в своем арсенале, так как в некоторых случаях он может сильно упростить процесс интеграции. Но конечно бывают ситуации, когда все взвесив, разумнее написать интеграцию прибегая к другим способам, например, использовать Linked Servers и писать процедуры на чистом TSQL или писать свою утилиту на каком-то другом языке программирования с применением всей мощи ООП и т.п.
Изучая материал проявляйте больше любопытства, например, щелкайте по вкладкам, которые я не показал, смотрите и анализируйте информацию на них, щелкайте по стрелкам, у них тоже есть свои свойства и настройки. Экспериментируйте, со всем что вам покажется интересным, не ленитесь делать свои небольшие тестовые примеры. Меняйте схему, так чтобы это приводило к исключениям, выбирайте более подходящие параметры у компонент пытаясь найти наиболее подходящий выход из сложившейся ситуации.