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

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

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers. Five Filters recommends:

• Massacres That Matter - Part 1 - 'Responsibility To Protect' In Egypt, Libya And Syria


• Massacres That Matter - Part 2 - The Media Response On Egypt, Libya And Syria


• National demonstration: No attack on Syria - Saturday 31 August, 12 noon, Temple Place, London, UK