Поделиться через

Подготовка к запросу информации об изменениях данных

  • Статья

Область применения: среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure

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

Примечание.

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

Вопросы проектирования

Чтобы получить измененные данные, нужно вызвать возвращающую табличное значение функцию Transact-SQL, которая принимает конечные точки интервала в качестве входных параметров и возвращает измененные данные за указанный период. Эту функцию вызывает исходный компонент в потоке данных. Сведения об этом исходном компоненте см. в разделе Получение и интерпретация измененных данных.

Чаще всего используемые исходные компоненты служб Integration Services, включая источник OLE DB, источник ADO и источник ADO NET, не могут получить сведения о параметрах для функции с табличным значением. Следовательно, в большинстве своем источники не могут вызывать параметризованные функции напрямую.

Существует два конструкторских решения проблемы передачи входных параметров для такой функции.

  • Сборка параметризированного запроса в виде строки. Чтобы собрать динамическую строку SQL со значениями параметров, жестко запрограммированными в эту строку, можно использовать задачу «Скрипт» или «Выполнение SQL». Затем эту строку можно сохранить в переменной пакета и использовать для задания исходному компоненту свойства SqlCommand. Этот подход приводит к успеху, так как исходный компонент более не требует сведений о параметрах.

    Примечание.

    Выполнение предварительно скомпилированных скриптов требует меньше ресурсов, чем использование задачи «Выполнение SQL».

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

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

Подготовка запроса

Чтобы получить возможность объединения входных параметров в единую строку запроса, пользователь должен настроить переменные пакета, необходимые для этого запроса.

Настройка переменных пакета

  • В SQL Server Data Tools (SSDT) откройте окно Переменные и создайте переменную со строковым типом данных, в которую будет сохранена полученная от задачи "Выполнение SQL" строка запроса.

    В этом примере используется имя переменной SqlDataQuery.

После создания переменной пакета можно объединять значения входных параметров с помощью задачи «Скрипт» или «Выполнение SQL». В следующих двух процедурах описывается настройка этих компонентов.

Использование задачи «Скрипт» для объединения строки запроса

  1. На вкладке Поток управления добавьте к пакету задачу «Скрипт» после контейнера «цикл по элементам» и соедините контейнер с этой задачей.

    Примечание.

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

  2. В окне Редактор задачи «Скрипт»на странице Скрипт выберите следующие параметры.

    1. Для свойства ReadOnlyVariablesвыберите из списка значения User::DataReady, User::ExtractStartTimeи User::ExtractEndTime .

    2. Для свойства ReadWriteVariablesвыберите из списка значение User::SqlDataQuery.

  3. В окне Редактор задачи «Скрипт»на странице Скрипт нажмите кнопку Изменить скрипт , чтобы открыть среду разработки скриптов.

  4. В главной процедуре введите один из следующих сегментов кода.

    • При программировании на языке C# введите следующие строки кода:

      int dataReady;  
System.DateTime extractStartTime;  
System.DateTime extractEndTime;  
string sqlDataQuery;  

dataReady = (int)Dts.Variables["DataReady"].Value;  
extractStartTime = (System.DateTime)Dts.Variables["ExtractStartTime"].Value;  
extractEndTime = (System.DateTime)Dts.Variables["ExtractEndTime"].Value;  

if (dataReady == 2)  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) + "', '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  
else  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" + ", '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  

Dts.Variables["SqlDataQuery"].Value = sqlDataQuery;

      - или -

    • Если вы используете язык Visual Basic, введите следующие строки кода:

      Dim dataReady As Integer  
Dim extractStartTime As Date  
Dim extractEndTime As Date  
Dim sqlDataQuery As String  

dataReady = CType(Dts.Variables("DataReady").Value, Integer)  
extractStartTime = CType(Dts.Variables("ExtractStartTime").Value, Date)  
extractEndTime = CType(Dts.Variables("ExtractEndTime").Value, Date)  

If dataReady = 2 Then  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) & _  
      "', '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
Else  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" & _  
      ", '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
End If  

Dts.Variables("SqlDataQuery").Value = sqlDataQuery

  5. Оставьте без изменений создаваемую по умолчанию строку кода, которая возвращает DtsExecResult.Success в результате выполнения скрипта.

  6. Закройте среду разработки скриптов и Редактор задачи «Скрипт».

Использование задачи «Выполнение SQL» для объединения строки запроса

  1. На вкладке Поток управления добавьте к пакету задачу «Выполнение SQL» после контейнера «цикл по элементам» и соедините контейнер с этой задачей.

    Примечание.

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

  2. В окне Редактор задачи «Выполнение SQL»на странице Общие выберите следующие параметры:

    1. Для параметра ResultSetвыберите значение Одна строка.

    2. Настройте допустимое соединение с базой данных-источником.

    3. Для параметра SQLSourceTypeвыберите значение Прямой ввод.

    4. В поле SQLStatementвведите приведенную ниже инструкцию SQL:

      declare @ExtractStartTime datetime,  
@ExtractEndTime datetime,   
@DataReady int  

select @DataReady = ?,   
@ExtractStartTime = ?,   
@ExtractEndTime = ?  

if @DataReady = 2  
select N'select * from CDCSample.uf_Customer'  
+ N'('''+ convert(nvarchar(30),@ExtractStartTime,120)  
+ ''', '''  
+ convert(nvarchar(30),@ExtractEndTime,120) + ''') '   
as SqlDataQuery  
else  
select N'select * from CDCSample.uf_Customer'  
+ N'(null, '''  
+ convert(nvarchar(30),@ExtractEndTime,120)  
+ ''') '  
as SqlDataQuery

      Примечание.

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

  3. На странице Сопоставление параметров средства Редактор задачи «Выполнение SQL»выполните следующее сопоставление.

    1. Сопоставьте переменную DataReady с параметром 0.

    2. Сопоставьте переменную ExtractStartTime с параметром 1.

    3. Сопоставьте переменную ExtractEndTime с параметром 2.

  4. На странице Результирующий набор средства Редактор задачи «Выполнение SQL»сопоставьте столбец «Имя результата» с переменной SqlDataQuery.

    Имя результата — это имя единственного возвращаемого столбца SqlDataQuery.

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

select * from CDCSample. uf_Customer('2007-06-11 14:21:58', '2007-06-12 14:21:58')

Добавление задачи потока данных

Последний этап процесса разработки потока управления для пакета — добавление задачи потока данных.

Добавление задачи потока данных и завершение формирования потока управления

  • На вкладке Поток управления добавьте задачу потока данных и соедините с задачей, которая объединяла строку запроса.

Следующий шаг

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

Следующий раздел: создание функции для получения измененных данных