SQL Server Integration Services (SSIS) для начинающих – часть 2 / Хабр

Задачи[]

Задачи состоят из трёх основных параметров: название, тип и отображаемое название. Счёт в задаче — целое число от -2 147 483 648 до 2 147 483 647.

Название используется внутри команд в качестве ссылки. Должно быть уникальным и содержать только определённые символы.Отображаемое название используется при отображении на экране. Может быть не уникальным и состоять из различных символов.Тип определяет, что отслеживает задача.

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

Параметр селектораscores={задача=диапазон} позволяет произвести поиск сущностей с счётом определённого диапазона в указанной задаче.

Проблемы[]

Отчёты об ошибках, связанных с «Система счёта игровых событий», поддерживаются в системе отслеживания ошибок Mojira. Сообщайте о найденных ошибках там (на английском языке).

Продолжим знакомство с ssis

Создадим в трех демонстрационных базах новую таблицу

ProductResidues

, которая будет содержать информацию об остатках на каждый день:

USE DemoSSIS_SourceA
GO
CREATE TABLE ProductResidues( ResidueDate date NOT NULL, ProductID int NOT NULL, ResidueAmount decimal(10,2) NOT NULL,
CONSTRAINT PK_ProductResidues PRIMARY KEY(ResidueDate,ProductID),
CONSTRAINT FK_ProductResidues_ProductID FOREIGN KEY(ProductID) REFERENCES Products(ID)
)
GO
USE DemoSSIS_SourceB
GO
CREATE TABLE ProductResidues( ResidueDate date NOT NULL, ProductID int NOT NULL, ResidueAmount decimal(10,2) NOT NULL,
CONSTRAINT PK_ProductResidues PRIMARY KEY(ResidueDate,ProductID),
CONSTRAINT FK_ProductResidues_ProductID FOREIGN KEY(ProductID) REFERENCES Products(ID)
)
GO
USE DemoSSIS_Target
GO
CREATE TABLE ProductResidues( ResidueDate date NOT NULL, ProductID int NOT NULL, ResidueAmount decimal(10,2) NOT NULL,
CONSTRAINT PK_ProductResidues PRIMARY KEY(ResidueDate,ProductID),
CONSTRAINT FK_ProductResidues_ProductID FOREIGN KEY(ProductID) REFERENCES Products(ID)
)
GO

И наполним таблицы в источниках тестовыми данными, поочередно выполнив на базах DemoSSIS_SourceA и DemoSSIS_SourceB следующий скрипт:

USE DemoSSIS_SourceA
--USE DemoSSIS_SourceB
GO
DECLARE @MinDate date=DATEADD(MONTH,-2,GETDATE())
DECLARE @MaxDate date=GETDATE()
;WITH dayCTE AS( SELECT CAST(@MinDate AS date) ResidueDate,10000 ResidueAmount UNION ALL SELECT DATEADD(DAY,1,ResidueDate),ResidueAmount-1 FROM dayCTE WHERE ResidueDate<@MaxDate
)
INSERT ProductResidues(ResidueDate,ProductID,ResidueAmount)
SELECT d.ResidueDate, p.ID, d.ResidueAmount
FROM dayCTE d
CROSS JOIN Products p
OPTION(MAXRECURSION 0)

Допустим, что в таблице ProductResidues будет очень много строк, и чтобы каждый раз не перезагружать всю информацию и упростить процедуру интеграции, логику загрузки в таблицу ProductResidues в базе DemoSSIS_Target реализуем следующую:

1. Если в принимающей БД еще нет записей, то загрузим все данные;
2. Если в принимающей БД есть данные, то будем удалять данные за неделю (последние 7 дней) от последней загруженной даты и загружать данные из источника начиная от этой даты снова.

Неудобство интеграции таблиц типа ProductResidues в том, что в ней нет полей, по которым можно было бы однозначно определить, когда появилась запись, в ней нет никакого идентификатора типа ID, нет ни поля типа UpdatedOn, которое содержало бы дату/время последнего обновления записи (т.е. зацепиться особо не за что), иначе бы мы, например, могли вычислить для каждого источника, по данным базы DemoSSIS_Target, последний ID или последнюю UpdatedOn и уже загружать данные из источников начиная от этих стартовых значений.

В нашем же случае еще допустим, что пользователи могут менять данные задним числом и могут вообще удалить некоторые ранее загруженные в базу DemoSSIS_Target строки из источников. Поэтому здесь обновление делается как-бы внахлёст, данные последней недели полностью перезаписываются.

Здесь неделя берется условно, об этом минимальном сроке, например, мы могли бы условиться с заказчиком (он мог подтвердить, что данные обычно меняются максимум в течении недели). Конечно это не самый надежный способ, и иногда могут возникнуть расхождения, например, в том случае, когда пользователь поменял данные месячной давности и здесь стоит предусмотреть возможность перезагрузки данных начиная с более ранней даты, мы сделаем это при помощи параметра, в котором будем указывать нужное нам количество дней назад.

Создадим новый SSIS-пакет и назовем его «LoadResidues.dtsx».

При помощи контекстного меню, отобразим область ввода переменных пакета (это можно сделать также при помощи меню «SSIS → Variables»):

В данном пакете, нажав на кнопку «Add Variable», создадим переменную LoadFromDate типа DateTime:

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

Так же стоит обратить внимание на Expression – если прописать в этом поле выражение, то переменная будет работать как формула и мы не сможем изменять ее значение при помощи присваивания. При каждом обращении к такой переменной ее значение будет рассчитываться согласно указанному выражению.

Значения переменных можно задавать при помощи компонента «Expression Task». Давайте рассмотрим, как это делается. Создадим элемент «Expression Task»:

Двойным щелчком откроем редактор данного элемента:

Пропишем следующее выражение:

Слоты отображения[]

С помощью команды /scoreboard objectives setdisplay, счёт различных сущностей в указанной задаче может быть отображён в определённом слоте отображения. Слоты отображения способны отображать только одну задачу.

Файл scoreboard.dat, находящийся в папка_мираdata, хранит данные о ССИС данного мира. Является сжатым GZip-файлом.

Тип[]

Составные типы — типы, разделяемые точками. Счёт всех составных типов может быть изменён командами. В начале и после двоеточия у составных типов, использующих систему статистики, может указываться пространство имён; если оно не указано, будет использовано пространство имён minecraft. Например, custom:jump соответствует minecraft.custom:minecraft.jump.

Список составных типов:

Заключение по второй части

В этой части мы рассмотрели каким образом можно делать синхронизацию небольших справочников. Здесь конечно не учитывается тот момент, что данные в источниках могут еще удаляться, но при необходимости вы сможете попробовать сделать это самостоятельно, т.к. при удалении иногда нужно учитывать дополнительные факторы, например, на удаляемую запись могут быть ссылки из других таблиц (в следующей части планируется это сделать).

Чтобы не нарушать ссылочную целостность, иногда запись в принимающей таблице удаляется логически, для этого, например, можно в эту таблицу добавить поле Deleted типа bit (флаг логического удаления) или DeletedOn типа datetime (дата/время логического удаления).Порой на сервере, на котором располагается база Target делается вспомогательная промежуточная база (обычно ее называют Staging) и первым делом «сырые» данные из Source загружаются в нее. Так как теперь Target и Staging находятся на одном сервере, то вторым шагом мы можем легко написать SQL-запрос (например, используя SQL-конструкцию MERGE или запрос с применение конструкции JOIN), который оперирует с наборами обеих этих баз.

SSIS достаточно интересный инструмент, который на мой взгляд не помешает иметь в своем арсенале, так как в некоторых случаях он может сильно упростить процесс интеграции. Но конечно бывают ситуации, когда все взвесив, разумнее написать интеграцию прибегая к другим способам, например, использовать Linked Servers и писать процедуры на чистом TSQL или писать свою утилиту на каком-то другом языке программирования с применением всей мощи ООП и т.п.

Изучая материал проявляйте больше любопытства, например, щелкайте по вкладкам, которые я не показал, смотрите и анализируйте информацию на них, щелкайте по стрелкам, у них тоже есть свои свойства и настройки. Экспериментируйте, со всем что вам покажется интересным, не ленитесь делать свои небольшие тестовые примеры.

Спасибо за внимание! Удачи!

→ Часть 3

Заключение по третьей части

Уважаемые читатели, эта часть будет заключительной.

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

Думаю, освоив это, далее вы уже без особого труда сможете освоить работу с остальными компонентами SSIS. В данных статьях я рассмотрел только самые важные компоненты (наиболее часто применяемые на моей практике), но зная только это вы уже можно сделать очень многое. По мере надобности изучайте самостоятельно другие компоненты, в первую очередь порекомендовал бы посмотреть следующее: