Пошаговое руководство. Публикация пакета служб SSIS в представлении SQL

Статья
03/29/2023

Применяется к:sql Server SSIS Integration Runtime в Фабрика данных Azure

В этом пошаговом руководстве приводятся подробные инструкции для публикации пакета служб SSIS в качестве представления SQL в базе данных SQL Server.

Необходимые компоненты

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

SQL Server с SQL Server Integration Services.
SQL Server Data Tools.

Шаг 1. Построение и развертывание проекта служб SSIS в каталоге служб SSIS

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

Запустите SQL Server Data Tools. В меню Пуск последовательно укажите пункты Все программы, Microsoft SQL Serverи выберите SQL Server Data Tools.
Создание проекта служб Integration Services.
1. В строке меню щелкните Файл , выберите Создать, а затем нажмите Проект.
2. Разверните узел Business Intelligence на левой панели и нажмите Службы Integration Services в древовидной структуре.
3. Выберите Проект служб Integration Services , если это еще не сделано.
4. Укажите SSISPackagePublishing в поле Имя проекта.
5. Укажите расположение для проекта.
6. Нажмите кнопку ОК , чтобы закрыть диалоговое окно Новый проект .
Перетащите компонент Поток данных из области элементов служб SSIS в конструктор на вкладке Поток управления .
Дважды щелкните компонент Поток данных в потоке управления , чтобы открыть Конструктор потоков данных.
Перетащите исходный компонент из области элементов в Конструктор потоков данных и настройте его на извлечение данных из источника данных.
1. Для пошагового руководства создайте тестовую базу данных TestDB с таблицей Сотрудник. Создайте таблицу с тремя столбцами: Идентификатор, FirstName и LastName.
2. Задайте Идентификатор в качестве первичного ключа.
3. Вставьте две записи со следующими данными.
  
  Идентификатор FIRSTNAME LASTNAME
  
  1 Джон Доу
  
  2 Джейн Доу
4. Перетащите компонент Источник OLE DB из области элементов служб SSIS в Конструктор потоков данных.
5. Настройте компонент на извлечение данных из таблицы Сотрудник в базе данных TestDB . Выберите (local).TestDB для параметра Диспетчер соединений OLE DB, Таблица или представление для параметра Режим доступа к данными [dbo].[Сотрудник] для параметра Имя таблицы или представления.
Теперь перетащите Назначение потоковой передачи данных из области элементов в поток данных. Этот компонент необходимо найти в разделе "Общие" области элементов.
Соедините компонент Источник OLE DB в потоке данных с компонентом Назначение потоковой передачи данных .
Построение и развертывание проекта служб SSIS в каталоге служб SSIS
1. В строке меню щелкните Проект и нажмите кнопку Развернуть.
2. В соответствии с инструкциями мастера разверните проект в каталоге служб SSIS на локальном сервере базы данных. В следующем примере используется Power BI в качестве имени папки и SSISPackagePublishing в качестве имени проекта в каталоге служб SSIS.

Идентификатор	FIRSTNAME	LASTNAME
1	Джон	Доу
2	Джейн	Доу

Шаг 2. Использование мастера публикации веб-канала данных служб SSIS для публикации пакета служб SSIS в виде представления SQL

На этом шаге используется мастер публикации веб-канала данных служб SQL Server Integration Services (SSIS) для публикации пакета служб SSIS в виде представления в базе данных SQL Server. Выходные данные пакета могут быть востребованы посредством запроса этого представления.

Мастер публикации веб-канала данных служб SSIS создает связанный сервер с помощью поставщика OLE DB для служб SSIS (SSISOLEDB), а затем создает представление SQL, состоящее из запроса на связанном сервере. Этот запрос содержит имя папки, имя проекта и имя пакета в каталоге служб SSIS.

Во время выполнения представление отправляет запрос поставщику OLE DB относительно служб SSIS через созданный связанный сервер. Поставщик OLE DB для служб SSIS выполняет пакет, указанный в запросе, и возвращает табличный результирующий набор в ответ на запрос.

Откройте Мастер публикации веб-канала данных служб SSIS , запустив файл ISDataFeedPublishingWizard.exe из папки C:\Program Files\Microsoft SQL Server\130\DTS\Binn или щелкнув Microsoft SQL Server 2016\SQL Server 2016 Data Feed Publishing Wizard в меню "Пуск\Все программы".
На странице Вводная щелкните Далее .
На странице Настройки пакета выполните следующие задачи.
1. Введите имя экземпляра SQL Server, содержащее каталог служб SSIS, или щелкните Обзор , чтобы выбрать сервер.
2. Щелкните Обзор рядом с полем "Путь", перейдите в каталог служб SSIS, выберите пакет служб SSIS, который требуется опубликовать (например: SSISDB->SSISPackagePublishing->Package.dtsx), и нажмите кнопку ОК.
3. На вкладках "Параметры пакета", "Параметры проекта" и "Диспетчеры соединений" в нижней части страницы введите значения для соответствующих параметров. Кроме того, можно указать ссылку на среду, которую следует использовать для выполнения пакета, и привязать параметры проекта и пакета к переменным среды.
  
  К переменным среды рекомендуется привязывать конфиденциальные параметры. В данном случае значение конфиденциального параметра гарантированно не будет храниться в обычном текстовом формате в представлении SQL, которое было создано мастером.
4. Щелкните Далее , чтобы перейти на страницу Параметры публикации .
На странице Параметры публикации выполните следующие задачи.
1. Выберите базу данных для создаваемого представления.
2. Введите имя для представления. Кроме того, можно выбрать существующее представление в раскрывающемся списке.
3. В списке Параметры укажите имясвязанного сервера , который следует связать с представлением. Если связанный сервер еще не существует, мастер его создаст перед созданием представления. Кроме того, здесь можно задать значения для User32BitRuntime и времени ожидания .
4. Нажмите кнопку Дополнительно . Должно появиться диалоговое окно Дополнительные параметры .
5. В диалоговом окне Дополнительные параметры выполните следующие действия.
  1. Укажите схему базы данных, в которой нужно создать представление (поле "Схема").
  2. Укажите, необходимо ли шифрование данных перед их отправкой по сети (поле "Шифрование"). Дополнительные сведения о данном параметре и параметре TrustServerCertificate см. в разделе Использование шифрования без проверки .
  3. Укажите, может ли использоваться самозаверяющий сертификат сервера, если включен параметр шифрования (полеTrustServerCertificate ).
  4. Нажмите кнопку ОК , чтобы закрыть диалоговое окно Дополнительные параметры .
6. Нажмите кнопку Далее , чтобы перейти к странице Проверка .
На странице Проверка просмотрите результаты проверки значений для всех параметров. В следующем примере отображается предупреждение о наличии связанного сервера, так как такого сервера не существует на выбранном экземпляре SQL Server. Если вы увидите ошибку в качестве результата, наведите указатель мыши на ошибку , чтобы получить сведения о ней. Например, если параметр "Допускать в ходе процесса" для поставщика SSISOLEDB не включен, при выполнении действия "Настройка связанного сервера" появится сообщение об ошибке.
Нажмите кнопку "Сохранить отчет", чтобы сохранить отчет в виде XML-файла.
Нажмите кнопку Далее на странице Проверка , чтобы перейти на страницу Сводка .
Просмотрите выбранные параметры на странице Сводка и нажмите кнопку Публикация , чтобы начать процесс публикации, который создаст связанный сервер, если он еще не существует на сервере, а затем представления с помощью связанного сервера.

Теперь выходные данные пакета можно запрашивать путем выполнения следующей инструкции SQL для базы данных SELECT * FROM [SSISPackageView].
Нажмите кнопку Сохранить отчет, чтобы сохранить этот отчет в виде XML-файла.
Просмотрите результаты процесса публикации и нажмите кнопку Готово для завершения работы мастера.

Примечание.

Не поддерживаются следующие типы данных: text, ntext, image, nvarchar(max), varchar(max) и varbinary(max).

Шаг 3. Тестирование представления SQL

На этом шаге выполняется запуск представления SQL, которое было создано мастером публикации веб-канала данных служб SSIS.

Запустите SQL Server Management Studio.
Разверните узел <Имя компьютера>, Базы данных, <База данных, выбранная в мастере>и Представления.
Щелкните правой кнопкой мыши <представление, созданное мастером> и нажмите кнопку Выбрать первые 1000 строк.
Убедитесь, что это результаты из пакета служб SSIS.

Шаг 4. Проверка выполнение пакета служб SSIS

На этом шаге проверяется выполнение пакета служб SSIS.

В SQL Server Management Studio разверните Каталоги служб Integration Services, SSISDB, далее разверните папку , в которой находится проект служб SSIS, разверните Проекты, узел проекта, а затем Пакеты.
Щелкните правой кнопкой мыши пакет служб SSIS, выберите пункты Отчеты, Стандартные отчетыи нажмите кнопку Все выполнения.
В отчете должно быть отражено выполнение пакета служб SSIS.

Примечание.

На компьютере с ОС Windows Vista с пакетом обновления 2 (SP2) в отчете будут отражены два сеанса выполнения пакета служб SSIS (один выполнен успешно, а второй с ошибкой). Не обращайте внимания на сбой, так как он вызван известной проблемой в этой версии.

Дополнительная информация

Мастер публикации веб-канала данных выполняет следующие важные операции.

Создает связанный сервер и настраивает его на использование поставщика OLE DB для служб SSIS.
Создает представление SQL в указанной базе данных, которое запрашивает выбранный пакет на связанном сервере, где содержатся данные о каталоге.

В этом разделе описываются процедуры создания связанного сервера и представления SQL без использования мастера публикации веб-канала данных. Кроме того, в нем содержатся дополнительные сведения об использовании функции OPENQUERY с поставщиком OLE DB для служб SSIS.

Создание связанного сервера с помощью поставщика OLE DB для служб SSIS

Создайте связанный сервер с помощью поставщика OLE DB для служб SSIS (SSISOLEDB), выполнив следующий запрос в SQL Server Management Studio.

  
USE [master]  
GO  
  
EXEC sp_addlinkedserver  
@server = N'SSISFeedServer',  
@srvproduct = N'Microsoft',  
@provider = N'SSISOLEDB',  
@datasrc = N'.'  
GO

Создание представления с помощью связанного сервера и данных о каталоге служб SSIS

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

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

Перед созданием представления введите и выполните следующий запрос в новом окне запроса. OPENQUERY является функцией, возвращающей набор строк, которая поддерживается сервером SQL Server. Она выполняет указанный запрос к заданному связанному серверу с помощью поставщика OLE DB, связанного со связанным сервером. Из предложения FROM запроса можно ссылаться на функцию OPENQUERY как на имя таблицы. Дополнительные сведения см. в разделе Документация по OPENQUERY в библиотеке MSDN .
```
SELECT * FROM OPENQUERY(SSISFeedServer,N'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
GO  
```
Важно!

При необходимости обновите имя папки, имя проекта и имя пакета. Если функция OPENQUERY завершается сбоем, в СРЕДЕ SQL Server Management Studio разверните серверные объекты, разверните связанные серверы, разверните поставщики и дважды щелкните поставщик SSISOLEDB и убедитесь, что параметр Allow inprocess включен.

Создайте представление в базе данных TestDB в данном пошаговом руководстве, выполнив следующий запрос:


USE [TestDB]   
GO   

CREATE VIEW SSISPackageView AS   
SELECT * FROM OPENQUERY(SSISFeedServer, 'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
GO

Протестируйте представление, выполнив следующий запрос.
```
SELECT * FROM SSISPackageView  
```

Функция OPENQUERY

Для функции OPENQUERY используется следующий синтаксис:

SELECT * FROM OPENQUERY(<LinkedServer Name>, N'Folder=<Folder Name from SSIS Catalog>; Project=<SSIS Project Name>; Package=<SSIS Package Name>; Use32BitRuntime=[True | False];Parameters="<parameter_name_1>=<value1>; parameter_name_2=<value2>";Timeout=<Number of Seconds>;')

Параметры папки, проекта и пакета являются обязательными. Use32BitRuntime, время ожидания и Параметры являются необязательными.

Use32BitRuntime может иметь только значение 0, 1, True или False. Данный параметр указывает, следует ли пакету использовать 32-разрядную среду выполнения (1 или true), если платформа SQL Server является 64-разрядной.

Время ожидания указывает число секунд, в течение которых поставщик OLE DB для служб SSIS может ожидать поступления новых данных из пакета служб SSIS. По умолчанию это время составляет 60 секунд. Для времени ожидания можно указать целочисленное значение в диапазоне от 20 до 32 000.

Параметры содержат значения параметров пакета и параметров проекта. Правила для параметров идентичны параметрам в DTExec.

В следующем списке указаны допустимые специальные символы в предложении запроса.

Одинарная кавычка (') — этот символ поддерживается стандартной функцией OPENQUERY. Если в предложении запроса нужно использовать одинарную кавычку, используйте две одинарные кавычки ('').
Двойная кавычка (") — в двойные кавычки заключается часть параметров запроса. Если двойные кавычки содержатся в самом значении параметра, используйте escape-символ. Например, ".
Левая и правая квадратные скобки ([ и ]) — эти символы используются для указания начальных и конечных пробелов. Например, "[ несколько пробелов ]" представляет собой строку " несколько пробелов " с одним начальным и одним конечным пробелами. Если сами эти символы используются в предложении запроса, их необходимо экранировать. Например: \[ и \].
Косая черта (\) — для каждого символа "\", применяемого в запросе предложения, нужно использовать escape-символ. Например, \\ вычисляется как \в предложении запроса.

См. также

Назначение потоковой передачи данных
Настройка назначения потоковой передачи данных