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

Статья
01/02/2025

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

Профилирование и очистка данных на ранних стадиях не подходят для автоматизации. В службах 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.
Входные данные задачи. Задача профилирования данных считывает входные данные из таблиц SQL Server. Поэтому, если нужно профилировать данные, уже загруженные и преобразованные в поток данных, то данные, хранящиеся в памяти, нужно сохранить в промежуточных таблицах.

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На вкладке «Поток управления» добавьте задачу «Скрипт».
Соедините задачу «Скрипт» с задачей «Профилирование данных».
Для настройки задачи откройте Редактор задачи «Скрипт» .
На странице Скрипт выберите нужный язык программирования. Затем сделайте две переменных пакета доступными для скрипта.
1. Выберите значение ProfileConnectionNameдля параметра ReadOnlyVariables.
2. Выберите значение AddressLine2NullRatioдля параметра ReadWriteVariables.
Выберите Изменить скрипт , чтобы открыть среду разработки скрипта.
Добавьте ссылку на пространство имен 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) создайте пакет.
На вкладке «Поток данных» добавьте, настройте и подключите соответствующие источники данных и преобразования.
На вкладке «Поток данных» добавьте, настройте и подключите целевой компонент, сохраняющий преобразованные данные в промежуточную таблицу.
На вкладке «Поток управления» добавьте и настройте задачу «Профилирование данных», которая вычисляет нужные профили для преобразованных данных из промежуточной таблицы. Подключите задачу «Профилирование данных» к задаче потока данных.
Настройте переменные пакета, указав в них значения, которые нужно получить из результатов профиля.
Добавьте и настройте задачу «Скрипт». Соедините задачу «Скрипт» с задачей «Профилирование данных». В задаче «Скрипт» напишите программный код, считывающий нужные значения из выходных данных задачи «Профилирование данных» и присваивающий значения переменным пакета.
В объектах управления очередностью, соединяющих задачу «Скрипт» с нисходящими компонентами в рабочем процессе, напишите выражения направления рабочего процесса, использующие значения переменных.

См. также

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

См. также

Обратная связь

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