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

   object rawConnection = Dts.Connections["output"].AcquireConnection(Dts.Transaction);             DataSet CoursesFile = new DataSet();            string filePath = (string)rawConnection;            object rawConnection2 = Dts.Connections["output2"].AcquireConnection(Dts.Transaction);            string filePath_Out = (string)rawConnection2;             object rawConnection3 = Dts.Connections["output.xsd"].AcquireConnection(Dts.Transaction);            string filePath_Schema = (string)rawConnection3;             CoursesFile.ReadXml(@filePath);             CoursesFile.AcceptChanges();             CoursesFile.WriteXmlSchema(@filePath_Schema);             CoursesFile.WriteXml(@filePath2); 

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

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

Reconsidering the Beginning

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

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

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

-- Delete time dimension if it already exists. IF Exists(Select Name from sysobjects where name = 'Dim_Time') BEGIN     Drop Table Dim_Time END GO   -- Standard options for creating tables SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO   -- Create your dimension table -- Adjust to your own needs Create Table dbo.Dim_Time (     Dateid int IDENTITY (1,1) PRIMARY KEY CLUSTERED,     Date date,     DateString varchar(10),     Day int,     DayofYear int,     DayofWeek int,     DayofWeekName varchar(10),     Week int,     Month int,     MonthName varchar(10),     Quarter int,     Year int,     IsWeekend bit,     IsLeapYear bit )   -- Declare and set variables for loop Declare @StartDate datetime, @EndDate datetime, @Date datetime   Set @StartDate = '2000/01/01' Set @EndDate = '2020/12/31' Set @Date = @StartDate   -- Loop through dates WHILE @Date <=@EndDate BEGIN     -- Check for leap year     DECLARE @IsLeapYear BIT     IF ((Year(@Date) % 4 = 0) AND (Year(@Date) % 100 != 0 OR Year(@Date) % 400 = 0))     BEGIN         SELECT @IsLeapYear = 1     END     ELSE     BEGIN         SELECT @IsLeapYear = 0     END       -- Check for weekend     DECLARE @IsWeekend BIT     IF (DATEPART(dw, @Date) = 1 OR DATEPART(dw, @Date) = 7)     BEGIN         SELECT @IsWeekend = 1     END     ELSE     BEGIN         SELECT @IsWeekend = 0     END       -- Insert record in dimension table     INSERT Into Dim_Time     (     [Date],     [DateString],     [Day],     [DayofYear],     [DayofWeek],     [Dayofweekname],     [Week],     [Month],     [MonthName],     [Quarter],     [Year],     [IsWeekend],     [IsLeapYear]     )     Values     (     @Date,     CONVERT(varchar(10), @Date, 105), -- See links for 105 explanation     Day(@Date),     DATEPART(dy, @Date),     DATEPART(dw, @Date),     DATENAME(dw, @Date),     DATEPART(wk, @Date),     DATEPART(mm, @Date),     DATENAME(mm, @Date),     DATENAME(qq, @Date),     Year(@Date),     @IsWeekend,     @IsLeapYear     )       -- Goto next day     Set @Date = @Date + 1 END GO  

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

Базу 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 — для записи в базу.

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