ETL-процесс с использованием веб-сервисов в Integration Services 2012

Запасаем впрок

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

ETL-процесс (Extract - Transform - Load) это термин из Business Intelligence и описывает процесс сбора и трансформации данных для создания аналитической базы данных (например хранилища данных).

SOAP протокол обмена данных и веб-сервисы описываемые WSDL - распространенные окна в мир практически всех ERP систем, многих онлайн порталов и финансовых организаций.

Попробую описать пошагово ETL процесс с помощью одного из мощнейших инструментов в классе - MS Integration Services.

Итак, рассмотрим тестовую задачу.

Задача

Необходимо собрать данные о курсах валют по отношению к рублю на каждую дату прошлого года и загрузить их в таблицу для последующего анализа. Центробанк России предоставляет историчекие данные - в виде веб сервисов с неплохим описанием.
Похоже это и есть решение.

Создаем проект

Для начала создадим проект в Business Intelligence Development Studio (А с недавних пор - SQL Server Data Tools)

Во тулбоксе есть элемент Web-service-task, его мы и перенесем на рабочую область.

В строке HttpConnection создадим новое соединение - выбрав NewConnection
и поставим URL http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx
К слову, можете зайти туда - не стесняйтесь.

WSDL - файл доступен там-же, вот он www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL
Скачайте и выберете его в диалоге настройки.

Все, переходим во вкладку Input

Тут можно выбрать среди сервисов и их методов - поставляемых провайдером.
Нам нужен GetCourseOnDate - выдающий DataSet на запрашиваемую дату.
Проставим значение даты вручную - в поле Value, чуть позже вернемся и автоматизируем это.
Output cделаем просто в файл создав новое соединение output - в файл output.xml

Запустим процесс - дело пошло. Файл создается - курсы валют выгялядят как курсы валют.
Формат странноват: diffgr аттрибуты, инлайн-схема. Это XML выдача объекта Dataset.

В BI-студии есть XML-Source коннектор - он выдает нулевой результат при попытке натравить его на такой файл. (Схему считывает).

Поставим после Web Service Task - Skript Task.

Workaround

Скрипт на C#, можно использовать весь инструментарий .net платформы и 2012 VisualStudio.
Я добавил в начало using System.Xml;
Обращаемся к коллекции соединений - я создал еще 2, для сохранения схемы и для вывода.

На выходе чистый XML - благодаря acceptChanges();

Reconsidering the Beginning

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

Для начала создадим временное измерение, оно пригодится и еще не раз - в каждом хранилище данных.

Я создаю своей вариацией подсмотенного в сети скрипта, вот он.

Перенесем на рабочую область новый элемент

Базу TEST и OLE DB соединение localhost.TEST создали попутно.

Как заставить процесс брать каждую дату из результата и передавать ее на WebServiceTask.

С помощью переменных.

Переменные

Правый щелчок на рабочей области - Variables. Или меню View - Other Windows - Variables. Это очень мощный инструмент, но мы упомянем его лишь вскользь, без него SSIS не могут и половины заявленного.
Создим переменную UDate типа System.Object, т.к. будем выводить результат запроса.
Соединим ее с нашим SQL Task

Теперь добавим последовательно к SQL-Task Foreach Loop Container.
Это контейнер (туда можно засунуть любую последовательность действий), он выполняет foreach рутину.
В нашем случае он будет выполнять соединение с веб-сервисами и запись курсов валют для каждой даты из набора UDate.

Созданая нами переменная Date типа Date - будет доступна внутри контейнера - так мы передадим актуальную для записи дату.

Вот что мы положим в контейнер

Посмотрим что за DataFlow следует за нашим скриптом - там все просто. Читаем XML с помощью XML - Source,
добавляем колонку Date - записываем в таблицу.
И так для каждой даты.

В элементе Derived Column - вставляем нашу переменную Date - для записи в базу.

Вот такой результат получается

Ссылки по теме

• Microsoft: Серверные программы (SQL Server)