Запасаем впрок
Иногда в процессе работы бывают нужны данные из веб-сервисов, тем более 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 — для записи в базу.
Вот такой результат получается
Автор: Alex509
