Использование веб-служб в Microsoft SQL Server Integration Services на примере получения курсов валют с сайта ЦБ РФ

Сайт ЦБ РФ предоставляет в виде веб-служб различную полезную информацию, которая может пригодиться в ваших информационных системах. Список веб-служб можно найти по адресу http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx.

В этой статье мы рассмотрим использование служб интеграции Microsoft SQL Server для периодического получения курсов валют.

Для начала, откройте Microsoft SQL Server Management Studio и создайте базу данных и таблицу, в которую будут помещаться курсы валют:

create database Currencies

go

use Currencies

go

create table dbo.currency_rate     (

            rate_date datetime not null

      ,     rate money not null

      ,     currency_code char(3) not null

      ,     constraint pk_currency_rate primary key clustered

            (

                        rate_date asc

                  ,     currency_code asc

            )          

)

go

[/code]
Далее, откройте Microsoft Business Intelligence Development Studio и создайте новый проект Integration Services:

 
Далее необходимо создать и настроить задачу веб-службы. Но сначала давайте создадим переменную, которая будет содержать дату, на которую необходимо получить курсы валют. Для этого откройте окно Variables (View- >Other Windows- >Variable) и создайте переменную с названием ImportStartDate. Измените ее тип на DateTime и введите дату, на которую требуется получить курс в формате месяц/день/год (для получения курсов на первое января 2009 года введите 01/30/2009).

Теперь давайте создадим задачу веб-службы. Вернитесь на вкладку Control Flow и перетащите с панели инструментов задачу под названием Web Service Task. Далее нажмите на задаче правую кнопку и выберите Edit… из появившегося меню:

 
На этом этапе необходимо настроить несколько свойств задачи – соединение (HttpConnection) и файл описания веб-службы (WSDLFile). Выберите свойство HttpConnection и создайте новое Http-соединение (New connection… >). В появившемся окне введите адрес службы – http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL. Если в вашей сети для доступа в Интернет используется proxy-сервер, заполните информацию о нем на вкладке Proxy.

 
Теперь необходимо настроить файл описания веб-службы (WSDL File). В поле WSDLFile введите путь, куда будет сохранен файл, и нажмите конпку Download WSDL:

Дождитесь окончания загрузки и перейдите на вкладку Input. На этой вкладке в свойстве Service выберите DailyInfo, в свойстве Method – GetCursOnDateXML. Для параметра On_date метода веб-службы установите флажок Variable и в поле Value выберите переменную ImportStartDate.

Далее перейдите на вкладку Output и настройте вывод информации, получаемой от веб-службы. Результаты работы веб-службы можно направить в файл или в переменную. Мы будем использовать файл, так что оставьте свойство OutputType равным File Connection, щелкните на поле File и создайте новое соединение. В появившемся диалоговом окне измените значение Usage type на Create file (таким образом файл будет пересоздаваться при каждой загрузке), и в поле File укажите путь к файлу с результатами работы службы.

Итак, служба настроена, давайте проверим ее работоспособность и сгенерируем XML-файл с курсами валют. Для этого нажмите правую кнопку на значке службы и выберите Execute Task из появившегося меню. Если все было сделано правильно, по указанному вами пути будет создан файл с курсами валют. Прежде чем продолжить, убедитесь, что он создался.

Теперь необходимо загрузить результаты ее работы (XML-файл) в базу данных. Для этого перетащите с панели инструментов в область задач задачу Data Flow Task и соедините задачу Web Service с этой задачей (для этого щелкните на значке задачи и перетащите зеленую стрелку).

После этого перейдите на вкладку Data Flow. Так как результаты работы веб-службы представлены в виде XML-файла, источником данных будет XML Source. Перетащите этот элемент с панели инструментом в область задачи, нажмите правую кнопку на значке задачи и выберите Edit… из появившегося меню. В поле XML Location введите путь к файлу, созданному задачей веб-службы. Так как схемы документа у нас нет, нажмите кнопку Generate XSD…, чтобы сгенерировать ее:

Перейдите на вкладку Columns, чтобы убедиться, что данные были правильно распознаны. Из этого источника данных нам требуется всего два столбца – Vcurs (курс валюты) и VchCode (код валюты). Отметьте только два эти столбца.

Следующая задача – преобразовать типы данных столбцов из XML-файл к типам данных столбцов в базе данных. Для этого используется задача Data Conversion. Перетащите ее на поверхность Data Flow и соедините зеленой стрелкой с источником XML Source:

Нажмите правую кнопку на задаче Data Conversion и выберите Edit… В появившемся окне отметьте оба столбца и измените их определения как показано на следующем рисунке (не забудьте изменить кодовую страницу для столбца VchCode на 1251):

Теперь к получившемуся набору данных требуется добавить еще один столбец – дата курса. Для этого используется задача Derived Column, перетащите ее на поверхность Data Flow и соедините с задачей Data Conversion. Нажмите правую кнопку на задаче Data Conversion и выберите Edit… Настройте столбцы, как показано на следующем рисунке:

Здесь мы добавили новый столбец rate_date, значение которого получается из переменной ImportStartDate, остальные столбцы просто переписываются без изменений.

Теперь можно направить результаты в базу данных SQL Server. Для этого поместите приемник SQL Server Destination и соедините его с задачей Derived Column. Нажмите правую кнопку на SQL Server Destination и выберите Edit… В появившемся окне необходимо настроить соединение с SQL Server, для этого нажмите кнопку New… рядом с полем OLE DB connection manager. Создайте новое соединение (снова нажмите кнопку New…). После создания соединения в поле Use a table or view выберите таблицу dbo.currency_rate:

Перейдите на вкладку Mappings для создания сопоставления столбцов задачи и столбцов таблицы на сервере. Нажмите OK. Получившийся поток данных должен выглядеть как показано на следующем рисунке:

Теперь можно запустить пакет на выполнение (Debug- >Start Debugging или нажав F5), и после завершения выполнения в таблице dbo.currency_rate должны появиться курсы валют на указанную вами дату. Откройте SQL Server Management Studio, найдите таблицу dbo.currency_rate, и убедитесь, что данные были получены.

Но это еще не все – теперь у нас есть пакет, получающий курсы на определенную дату. Но мы ведь не хотим вручную каждый день исправлять значение даты? Давайте посмотрим, как получить данные за произвольный период и как настроить пакет на ежедневное получение курсов.

Для этого, во-первых, нам потребуется еще одна таблица в базе данных Currencies, которая будет содержать дату последнего импорта. Создайте таблицу, используя следующий сценарий:

USE Currencies

go

create table dbo.config(

            param_name nvarchar (64) not null

      ,     param_value sql_variant not null,

            constraint pk_config primary key clustered

            (

                  param_name asc

            )

)

go

insert into dbo.config  (

            param_name

      ,     param_value

)

values      (

            'last_import_date'

      ,     '20061231'

)

go

[/code]
В данном случае мы импортируем данные с 1 января 2007 года по сегодняшнюю дату.

Вернитесь в Business Intelligence Development Studio и переключитесь на вкладку Control Flow. Следующая наша задача – получить из базы данных значение даты последнего импорта. Для этого используется задача Execute SQL Task. Перетащите ее на поверхность пакета, нажмите правую кнопку и выберите Edit… Измените свойство Name на Get last import date, ResultSet на Single row, в свойстве Connection выберите соединение с БД Currencies, и в SQLStatement введите следующий код:

select cast(param_value as datetime) as param_value

from dbo.config

where param_name = 'last_import_date'

[/code]
Перейдите на вкладку Result Set, нажмите кнопку Add, и измените название столбца результата на param_value:

Следующая задача – организовать цикл с последней даты импорта до текущей даты. Для этого в SSIS используется контейнер For Loop. Перетащите его в область задач. Одновременно выделите задачи Web Service Task и Data Flow Task, и переместите их в контейнер For Loop. Соедините задачу Get last import date и цикл.

Теперь необходимо настроить параметры цикла. Нажмите правую кнопку на контейнере и выберите Edit…

В этом окне вы можете видеть несколько параметров, вероятно, знакомых вам по циклу for в различных языкам программирования – C/C++, C#, Java.

Свойство InitExpression содержит выражение для инициализации, это выражение будет вычислено при старте цикла. Введите в это поле следующий код:

@ImportStartDate = DateAdd("day", 1, @ImportStartDate)

Здесь мы просто добавляем один день к дате последнего импорта.

Выражение EvalExpression управляет выполнением цикла, пока это выражение имеет значение true, цикл будет выполняться. В это поле введите следующий код:

@ImportStartDate

Это длинное выражение необходимо для сравнения текущей даты без временной составляющей и даты импорта.

Выражение AssignExpression изменяет управляющую переменную цикла, в это поле введите следующий код:

@ImportStartDate = DateAdd("day", 1, @ImportStartDate)

Итак, при первом запуске этот цикл получит данные, начиная с LastImportDate до текущей даты.

Теперь нам осталось изменить значение даты последнего импорта в базе данных. Для этого мы снова используем Execute SQL Task. Добавьте эту задачу в цикл и соедините ее с Data Flow Task. Нажмите правую кнопку на задаче и выберите Edit… Переименуйте задачу в Change last import date. В поле Connection выберите подключение к базе данных Currencies. В поле SQLStatement введите следующий код:

UPDATE    config

SET              param_value = ?

WHERE     (param_name = 'last_import_date')

[/code]
Перейдите на закладку Parameter Mapping и добавьте соответствие значения параметра и переменной LastImportDate – в поле Variable Name выберите User::ImportStartDate, измените тип данных на DATE, в поле Parameter Name введите 0 – это номер параметра в операторе UPDATE.

Запустите пакет на выполнение. Если все было сделано правильно, в таблице dbo.currency_rate должны начать появляться данные.

И последняя задача – автоматизировать запуск пакета. Для этого можно использовать задания SQL Server Agent. Запустите Microsoft SQL Server Management Studio, раскройте узел сервера, найдите узел SQL Server Agent. Далее нажмите правую кнопку на узле Jobs и выберите New job… На первой странице введите название задачи – Import currencies и перейдите на страницу Steps. Нажмите кнопку New… внизу страницы, чтобы создать новый шаг. Введите название шага – Import и в списке Type выберите SQL Server Integration Services Package. Выберите расположение пакета – в поле Package source укажите File system и в поле Package внизу страницы введите путь к пакету:

Нажмите OK и перейдите на страницу Schedules. Здесь вы можете настроить расписание запуска пакета. Вот теперь – все, можете расслабиться и наслаждаться плодами своего труда.

PS Ах да, не забудьте настроить обработку ошибок.

Влад А. Щербинин,

29 марта 2009 г., Йола, Нигерия