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

  • Tutorial

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


Иногда в процессе работы бывают нужны данные из веб-сервисов, тем более 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

image

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

image

Запустим процесс — дело пошло. Файл создается — курсы валют выгялядят как курсы валют.
Формат странноват: 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



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

image

image

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

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

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

Переменные


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

image

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

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

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

image

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

image

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

Вот такой результат получается
image
  • +4
  • 13,7k
  • 3
Поделиться публикацией

Комментарии 3

    0
    Спасибо. А с 2008 R2 все так же получится? И есть ли еще подобные доступные веб-сервисы? Списки банков и т.д.
      0
      Да, 2008R2 все так же должно быть. Списки банков, информация по кредитным организациям — www.cbr.ru/scripts/root.asp
      Думаю нагуглить можно немало.
        0
        Еще не успели проапгрейдиться до 2012 — проблема с типом лицензий. Поэтому обходимся средствами 2008R2. В этой версии уже есть веб сервисы, но они подходят не для всех целей, например — собирать данные с NASDAQ и PSE. С насдаком проблем нет — yahoo предоставляет api результатом запроса к которому является csv, который без проблем обрабатывается bulk insert а потом sql. А вот с PSE пришлось повозиться. Данные предоставляются либо бесплатно в супер-неудобном формате, либо в любом формте по требованию, но платно.
        Решением было влезть в источники дынных графика с сайта, собраным под винду wget'ом стянуть эти данные в текст и потом парсить sql'ом.
        ИМХО — ssis не всегда самый удобный тул для ETL'ок.
        А за статью спасибо новичкам подойдет, написано понятным языком, с инструкциями и картинками.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое