Включение задачи «Профилирование данных» в рабочий процесс пакета

Статья
05/08/2013

Профилирование и очистка данных на ранних стадиях не подходят для автоматизации. В службах SQL Server Службы Integration Services выходные данные задачи профилирования данных обычно требуют визуального анализа и вмешательства человека, чтобы определить, являются ли зафиксированные нарушения реальными. Для очистки данных, даже после обнаружения проблем с их качеством, нужен хорошо продуманный план.

Однако после выработки критериев качества данных можно автоматизировать регулярный анализ и очистку источника данных. Рассмотрим следующие ситуации.

Проверка качества данных перед добавочной загрузкой. Используйте задачу «Профилирование данных» для вычисления профиля соотношения значений NULL в столбцах новых данных, предназначенных для столбца CustomerName в таблице Customers. Если процент значений NULL превышает 20%, необходимо отправить оператору сообщение электронной почты с профилем выходных данных и завершить работу пакета. В противном случае можно продолжить добавочную загрузку.
Автоматизация очистки данных при выполнении определенных условий. Используйте задачу «Профилирование данных» для вычисления профиля включения значений столбца «Штат» из уточняющей таблицы штатов и почтового индекса из уточняющей таблицы почтовых индексов. Если интенсивность включения значений штата менее 80%, но интенсивность включения значений почтового индекса превышает 99%, это означает две вещи. Во-первых, качество данных со штатами плохое. Во-вторых, качество данных с почтовыми индексами хорошее. Запустите задачу потока данных для очистки данных со штатами, которая выполнит уточняющий запрос, определяющий правильное значение штата по почтовому индексу.

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

Определение основного рабочего процесса для задачи потока данных

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

Программное использование выхода задачи профилирования данных в пакете

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

При интеграции задачи «Профилирование данных» в рабочем процессе пакета нужно помнить о следующих двух характеристиках задачи.

Выходные данные задачи. Задача «Профилирование данных» выводит информацию в файл или переменную пакета в формате XML в соответствии со схемой DataProfile.xsd. Поэтому, если результаты профиля нужно использовать в логике рабочего процесса пакета, необходимо создать запросы к выходным данным в формате XML. Для этого удобно воспользоваться языком запросов Xpath. Для изучения структуры выхода в формате XML можно открыть образец выходного файла или саму схему. Открыть выходной файл или схему можно в среде Microsoft Visual Studio, другом редакторе XML или в текстовом редакторе (например, в Блокноте).

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

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

Входные данные задачи. Задача «Профилирование данных» считывает свои входные данные из таблиц SQL Server. Поэтому, если нужно профилировать данные, уже загруженные и преобразованные в поток данных, то данные, хранящиеся в памяти, нужно сохранить в промежуточных таблицах.

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

Соединение задачи «Профилирование данных» непосредственно с внешним источником данных

Задача «Профилирование данных» может профилировать данные, поступающие непосредственно из внешнего источника данных. Следующий пример иллюстрирует эту возможность. Задача «Профилирование данных» применяется для вычисления профиля соотношения значений NULL в столбцах таблицы Person.Address базы данных AdventureWorks2012. Затем в примере используется задача «Скрипт» для получения результатов из выходного файла и присвоения значений переменным пакета, которые можно использовать для управления рабочим процессом.

Примечание
Для этого простого примера был выбран столбец AddressLine2, поскольку он содержит высокий процент значений NULL.

Пример состоит из следующих шагов:

настройка диспетчеров соединений для подключения к внешнему источнику данных и выходному файлу, в который будут помещены результаты профилирования;
настройка переменных пакета, в которых будут храниться значения, необходимые задаче профилирования данных;
настройка задачи «Профилирование данных» для вычисления профиля соотношения значений NULL в столбце;
настройка задачи «Скрипт» для работы с выходными данными задачи «Профилирование данных» в формате XML;
настройка элементов управления очередностью, определяющих выбор компонентов нисходящего рабочего процесса на основании результатов задачи «Профилирование данных».

Настройка диспетчеров соединений

В этом примере используется два диспетчера соединений:

диспетчер соединений ADO.NET, который подключается к исходной базе данных AdventureWorks2012;
диспетчер соединения файлов, создающий выходной файл для хранения результатов задачи «Профилирование данных».

Настройка диспетчеров соединений

В среде SQL Server Data Tools (SSDT) создайте новый пакет служб Службы Integration Services.
Добавьте к пакету диспетчер соединений ADO.NET. Настройте этот диспетчер соединений для использования поставщика данных .NET для SQL Server (SqlClient) и для соединения с доступным экземпляром базы данных AdventureWorks2012.

По умолчанию диспетчеру соединений присваивается следующее имя: <имя_сервера>.AdventureWorks1.
Добавьте к пакету диспетчер соединения файлов. Настройте этот диспетчер соединений, чтобы он создавал выходной файл для задачи «Профилирование данных».

В этом примере используется файл с именем DataProfile1.xml. По умолчанию имя диспетчера соединений совпадает с именем файла.

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

В этом примере используются две переменные пакета:

переменная ProfileConnectionName передает имя диспетчера соединения файлов задаче «Скрипт»;
переменная AddressLine2NullRatio передает вычисленное соотношение значений NULL в столбце из задачи «Скрипт» в пакет.

Настройка переменных пакета, в которых будут содержаться результаты профилирования

В окне Переменные создайте и настройте следующие две переменные пакета.
- Введите имя одной из переменных, ProfileConnectionName, и назначьте ей тип String.
- Введите имя второй переменной, AddressLine2NullRatio, и назначьте ей тип Double.

Настройка задачи «Профилирование данных»

Задачу «Профилирование данных» нужно настроить следующим образом:

для использования данных, которые диспетчер соединений ADO.NET поставляет в качестве входных;
для вычисления профиля соотношения значений NULL на входных данных;
для сохранения результатов профилирования в файле, связанном с диспетчером соединения файла.

Настройка задачи «Профилирование данных»

На вкладке «Поток управления» добавьте задачу «Профилирование данных».
Для настройки задачи откройте Редактор задачи «Профилирование данных».
На странице Общие редактора в поле Назначение выберите имя предварительно настроенного диспетчера соединения файлов.
На странице редактора Запросы профиля создайте новый профиль «Соотношение значений NULL в столбце».
На панели Свойства запроса выберите в качестве значения параметра ConnectionManager предварительно настроенный диспетчер соединений ADO.NET. Затем в поле TableOrView выберите Person.Address.
Закройте редактор задачи «Профилирование данных».

Настройка задачи «Скрипт»

Задачу «Скрипт» нужно настроить таким образом, чтобы она получала результаты из выходного файла и присваивала их значения предварительно созданным переменным пакета.

Настройка задачи «Скрипт»

На вкладке «Поток управления» добавьте задачу «Скрипт».
Соедините задачу «Скрипт» с задачей «Профилирование данных».
Для настройки задачи откройте Редактор задачи «Скрипт».
На странице Скрипт выберите нужный язык программирования. Затем сделайте две переменных пакета доступными для скрипта.
1. В списке ReadOnlyVariables выберите ProfileConnectionName.
2. В списке ReadWriteVariables выберите AddressLine2NullRatio.
Выберите Изменить скрипт, чтобы открыть среду разработки скрипта.
Добавьте ссылку на пространство имен System.Xml.

Введите образец кода, соответствующий выбранному языку программирования.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml

Public Class ScriptMain

  Private FILENAME As String = "C:\ TEMP\DataProfile1.xml"
  Private PROFILE_NAMESPACE_URI As String = "https://schemas.microsoft.com/DataDebugger/"
  Private NULLCOUNT_XPATH As String = _
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()"
  Private TABLE_XPATH As String = _
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table"

  Public Sub Main()

    Dim profileConnectionName As String
    Dim profilePath As String
    Dim profileOutput As New XmlDocument
    Dim profileNSM As XmlNamespaceManager
    Dim nullCountNode As XmlNode
    Dim nullCount As Integer
    Dim tableNode As XmlNode
    Dim rowCount As Integer
    Dim nullRatio As Double

    ' Open output file.
    profileConnectionName = Dts.Variables("ProfileConnectionName").Value.ToString()
    profilePath = Dts.Connections(profileConnectionName).ConnectionString
    profileOutput.Load(profilePath)
    profileNSM = New XmlNamespaceManager(profileOutput.NameTable)
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI)

    ' Get null count for column.
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM)
    nullCount = CType(nullCountNode.Value, Integer)

    ' Get row count for table.
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM)
    rowCount = CType(tableNode.Attributes("RowCount").Value, Integer)

    ' Compute and return null ratio.
    nullRatio = nullCount / rowCount
    Dts.Variables("AddressLine2NullRatio").Value = nullRatio

    Dts.TaskResult = Dts.Results.Success

  End Sub

End Class

using System;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;

public class ScriptMain
{

  private string FILENAME = "C:\\ TEMP\\DataProfile1.xml";
  private string PROFILE_NAMESPACE_URI = "https://schemas.microsoft.com/DataDebugger/";
  private string NULLCOUNT_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()";
  private string TABLE_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table";

  public void Main()
  {

    string profileConnectionName;
    string profilePath;
    XmlDocument profileOutput = new XmlDocument();
    XmlNamespaceManager profileNSM;
    XmlNode nullCountNode;
    int nullCount;
    XmlNode tableNode;
    int rowCount;
    double nullRatio;

    // Open output file.
    profileConnectionName = Dts.Variables["ProfileConnectionName"].Value.ToString();
    profilePath = Dts.Connections[profileConnectionName].ConnectionString;
    profileOutput.Load(profilePath);
    profileNSM = new XmlNamespaceManager(profileOutput.NameTable);
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI);

    // Get null count for column.
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM);
    nullCount = (int)nullCountNode.Value;

    // Get row count for table.
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM);
    rowCount = (int)tableNode.Attributes["RowCount"].Value;

    // Compute and return null ratio.
    nullRatio = nullCount / rowCount;
    Dts.Variables["AddressLine2NullRatio"].Value = nullRatio;

    Dts.TaskResult = Dts.Results.Success;

  }

}

Примечание
Образец кода, использованный в данной процедуре, демонстрирует загрузку выхода задачи «Профилирование данных» из файла. Чтобы вместо этого загрузить выход задачи «Профилирование данных» из пакетной переменной, ознакомьтесь с альтернативным образцом программного кода, расположенным после этой процедуры.

Образец кода, использованный в данной процедуре, демонстрирует загрузку выхода задачи «Профилирование данных» из файла. Чтобы вместо этого загрузить выход задачи «Профилирование данных» из пакетной переменной, ознакомьтесь с альтернативным образцом программного кода, расположенным после этой процедуры.

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

Альтернативный образец программного кода — чтение выхода задачи профилирования из переменной

В предыдущей процедуре описана загрузка выхода задачи «Профилирование данных» из файла. Другой вариант — загрузить эти выходные данные из переменной пакета. Чтобы загрузить эти выходные данные из переменной пакета, нужно внести в образец кода следующие изменения.

Вызовите метод LoadXml класса XmlDocument вместо метода Load.
В редакторе задачи «Скрипт» добавьте имя переменной пакета, содержащей выход профиля, в список ReadOnlyVariables задачи.
Передайте строковое значение переменной методу LoadXML, как показано в следующем образце программного кода. (В данном примере в качестве имени переменной пакета, содержащей выход задачи профилирования, используется «ProfileOutput».)
```
Dim outputString As String
outputString = Dts.Variables("ProfileOutput").Value.ToString()
...
profileOutput.LoadXml(outputString)
```
```
string outputString;
outputString = Dts.Variables["ProfileOutput"].Value.ToString();
...
profileOutput.LoadXml(outputString);
```

Настройка элементов управления очередностью

Необходимо настроить элементы управления очередностью, которые определяют выбор компонентов нисходящего рабочего процесса на основании результатов задачи «Профилирование данных».

Настройка элементов управления очередностью

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

Например, можно задать для параметра Вычислительная операция управления очередностью значение Выражение и ограничение. Затем можно использовать @AddressLine2NullRatio < .90 в качестве значения этого выражения. Тогда рабочий процесс выберет этот путь, если предыдущие задачи завершатся успешно, а также в случае если процент значений NULL в выбранном столбце меньше 90%.

Соединение задачи «Профилирование данных» с преобразованными данными из потока данных

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

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

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

Сохранив данные в промежуточной таблице, можно:

использовать задачу «Профилирование данных» для профилирования данных;
использовать задачу «Скрипт» для чтения результатов, как описано выше в данном разделе;
использовать полученные результаты для управления последующим рабочим процессом пакета.

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

Использование задачи «Профилирование данных» в потоке данных

В среде SQL Server Data Tools (SSDT) создайте новый пакет.
На вкладке «Поток данных» добавьте, настройте и подключите соответствующие источники данных и преобразования.
На вкладке «Поток данных» добавьте, настройте и подключите целевой компонент, сохраняющий преобразованные данные в промежуточную таблицу.
На вкладке «Поток управления» добавьте и настройте задачу «Профилирование данных», которая вычисляет нужные профили для преобразованных данных из промежуточной таблицы. Подключите задачу «Профилирование данных» к задаче потока данных.
Настройте переменные пакета, указав в них значения, которые нужно получить из результатов профиля.
Добавьте и настройте задачу «Скрипт». Соедините задачу «Скрипт» с задачей «Профилирование данных». В задаче «Скрипт» напишите программный код, считывающий нужные значения из выходных данных задачи «Профилирование данных» и присваивающий значения переменным пакета.
В объектах управления очередностью, соединяющих задачу «Скрипт» с нисходящими компонентами в рабочем процессе, напишите выражения направления рабочего процесса, использующие значения переменных.

Будьте в курсе новых возможностей cлужб Integration Services

Чтобы получить новейшую документацию, статьи, образцы и видеоматериалы корпорации Майкрософт, а также лучшие решения участников сообщества, посетите страницу служб Службы Integration Services на сайте MSDN:

Посетить страницу «Службы Integration Services» на сайте MSDN

Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

См. также

Основные понятия

Установка задачи «Профилирование данных»

Средство просмотра профиля данных

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

Включение задачи «Профилирование данных» в рабочий процесс пакета

Определение основного рабочего процесса для задачи потока данных

Программное использование выхода задачи профилирования данных в пакете

Соединение задачи «Профилирование данных» непосредственно с внешним источником данных

Настройка диспетчеров соединений

Настройка диспетчеров соединений

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

Настройка переменных пакета, в которых будут содержаться результаты профилирования

Настройка задачи «Профилирование данных»

Настройка задачи «Профилирование данных»

Настройка задачи «Скрипт»

Настройка задачи «Скрипт»

Альтернативный образец программного кода — чтение выхода задачи профилирования из переменной

Настройка элементов управления очередностью

Настройка элементов управления очередностью

Соединение задачи «Профилирование данных» с преобразованными данными из потока данных

Использование задачи «Профилирование данных» в потоке данных

См. также

Основные понятия

Дополнительные ресурсы