2.2.3 Data Driven Query Task

Data Driven Query Task в DTS (Data Transformation Services) в SQL Server 2000, изменение данных в зависимости от условия, таблица привязок (binding table)

Это - наиболее сложный вид преобразования, который можно использовать как для вставки данных (точно так же, как и Transform Data Tasks), так и для изменения и удаления данных в зависимости от какого-либо условия на источнике.

Общие принципы работы DDQ:

1) DDQ извлекает данные из источника (при этом источником может быть, как обычно, файл, таблица, представление или запрос)

2) производит над этими данные необходимые преобразования: в качестве преобразований могут использоваться простые операции с форматами данных или скрипты ActiveX

3) передает данные запросу назначения. При этом у нас используется binding table - очень специфическая таблица, которая представляет собой набор столбцов, в которые ничего не вставляется. Собственно говоря, это просто набор столбцов (без значений), подставляемый в запросы. Зачем нам это нужно: столбы в binding table - это набор параметров, которые подставляются в запросы.

Кроме того, поскольку в DDQ используется стандартный объект Data Pump (как в Transform Data Task), мы можем использовать все его свойства, доступные программным путем.

В рамках DDQ Task (свойства DDQ Task, вкладка Queries) вы можете настроить до четырех запросов к таблице назначения. Исходно они поделены на следующие типы: Insert, Update, Delete и Select. Однако вы можете использовать на самом деле четыре запроса любого типа, поскольку деление условное.

Настройка DDQ:

представим следующую ситуацию: у нас есть таблица inventory_fact с остатками на складах:

CREATE TABLE [dbo].[inventory_fact] (

     [inventory_fact_key] [int] IDENTITY (1, 1) NOT NULL,

     [inventory_date_key] [int] NOT NULL ,

     [product_key] [int] NOT NULL ,

     [warehouse_key] [int] NOT NULL ,

     [quantity_on_hand] [int] NULL ,

     [low_quantity_flag] [char] (1))

Каждый вечер мы запускаем пакет DTS, чтобы проверить соответствие остатка уровню reorder_level из таблицы product_dim (в нашей ситуации - в той же базе данных, на практике может быть в другой базе). Если остаток (quantity_on_hand) меньше, чем reorder_level, то ставим low_quantity_lag в Y, в противном случае - он должен стоять в Null.

Поскольку наша задача - обновить таблицу источника, можно использовать только Data Driven Query Task (на практике в такой можно использовать и обычный update, и курсор, но DTS с Data Driven Query Tasks - практически единственная возможность, если данные о складах и продуктах - на разных источниках).

Алгоритм решения такой:

1) определяем destination query - запрос, который будет обновлять источник:

UPDATE inventory_fact 

SET low_quantity_flag = 'Y'

WHERE product_key = ?

AND warehouse_key = ?

AND inventory_date_key = ?

Все, что обозначено знаком ? - это параметры, которые будут передаваться запросу в момент выполнения, соответственно, параметр 1, 2 и 3. Почему такой набор параметров - чтобы однозначно идентифицировать каждую запись на источнике.

2) далее определяем binding table - таблицу привязок. В эту таблицу ничего не вставляется - это просто набор столбцов, который необходим Data Driven Query Task. Количество столбцов и типы данных в binding table должны соответствовать параметрам, передаваемым destination query, поэтому таблица привязок будет выглядеть так:

     CREATE TABLE [dbo].[binding] (

     [product_key] [int] NOT NULL ,

     [warehouse_key] [int] NOT NULL ,

     [inventory_date_key] [int] NOT NULL)

Ее можно создавать вручную, а можно использовать Execute SQL Task.

3) далее определяем source query (source data extract) - запрос, при помощи которого из таблицы источника будут извлекаться те данные, для которых low_quantity_flag нужно установить в Y. Запрос обязательно должен возвращать набор столбцов, соответствующий набору параметров, требуемых destination query (а значит, и binding table):

SELECT inventory_fact.product_key,

       inventory_fact.warehouse_key, 

       inventory_fact.inventory_date_key

FROM inventory_fact 

INNER JOIN product_dim ON inventory_fact.product_key = product_dim.product_dim_key 

AND  product_dim.reorder_level > inventory_fact.quantity_on_hand

Все необходимые данные мы определили, теперь создаем Data Driven Query Task:

1) создаем 2 соединения с базой данных (например, Polaris из примеров к курсу 2093) - Data Driven Query Task нужны минимум два соединения - одно для destination query, другое для source query. Можно обращаться к одной и той же базе данных;

2) создаем Data Driven Query Task;

3) на вкладке Source свойств Data Driven Query Task прописываем наш source query в окне SQL query;

4) на вкладке Bindings нажимаем на кнопку Create, изменяем имя таблицы (например, на Bind1) и соглашаемся с той структурой, которая автоматически определена SQL Server на основе Source Query;

5) на вкладке Transformation оставляем сгенерированное автоматически ActiveX Script преобразование по умолчанию;

6) на вкладке Queries выбираем тип запроса Insert (можно любой, но если мы используем не Insert, то нужно поменять строку с InsertQuery в преобразовании ActiveX Script, например на DTSTransformStat_UpdateQuery) и в окно ниже прописываем наш destination query. Затем нажимаем на кнопку Parse/Show Parameters, чтобы заполнить нижнюю табличку.

Нажимаем на кнопку OK - пакет готов. Можно проверить на базе данных Polaris, предварительно вставив в таблицу inventory_fact несколько строк (удобнее всего это делать при помощи диаграммы Inventory Start Schema).

Получить учебные материалы по этому курсу