Отладка потока данных

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

Службы Microsoft Integration Services и конструктор служб SSIS включают функции и средства, которые можно использовать для устранения неполадок потоков данных в пакете служб Integration Services.

  • Конструктор служб SSIS предоставляет средства просмотра данных.

  • Конструктор SSIS и преобразования Integration Services позволяют получать число строк.

  • Конструктор SSIS отображает сведения о ходе выполнения во время выполнения.

Средства просмотра данных

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

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

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

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

При добавлении средства просмотра данных в путь Конструктор SSIS добавляет значок средства просмотра данных на поверхность конструктора вкладки Поток данных рядом с путем. Преобразования с несколькими выходами (например, преобразование «Условное разбиение») могут иметь средство просмотра данных для каждого из путей.

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

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

Добавление средства просмотра данных

Количество строк

Количество строк, прошедших через путь, отображается на панели конструктора вкладки Поток данных в конструкторе служб SSIS рядом с путьом. Количество периодически обновляется по мере перемещения данных по пути.

Вы также можете добавить в поток данных преобразование «Подсчет строк», чтобы сохранить итоговое число строк в переменной. Дополнительные сведения см. в разделе Row Count Transformation.

Отчет о ходе выполнения

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

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

Цвет Описание
Бесцветный Ожидание вызова подсистемой обработки потока данных.
Желтый Выполнение преобразования, извлечения данных или загрузки данных.
Зеленый Успешно выполнено.
красный Выполнено с ошибками.

Анализ потока данных

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

Примечание.

Чтобы фиксировать сведения в представлении catalog.execution_data_statistics, уровень ведения журнала должен быть установлен на Verbose.

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

use SSISDB  
select package_name, task_name, source_component_name, destination_component_name, rows_sent  
from catalog.execution_data_statistics  
where execution_id = 132  
order by source_component_name, destination_component_name

В следующем примере вычисляется количество строк в миллисекунду, отправляемых каждым компонентом при конкретном выполнении. Вычисляются следующие значения:

  • total_rows — сумма всех строк, отправленных компонентом

  • wall_clock_time_ms — общее время выполнения каждого компонента, в миллисекундах

  • num_rows_per_millisecond — количество строк, отправляемых каждым компонентом в миллисекунду

Предложение HAVING используется для предотвращения возникновения в вычислениях ошибки деления на ноль.

use SSISDB  
select source_component_name, destination_component_name,  
    sum(rows_sent) as total_rows,  
    DATEDIFF(ms,min(created_time),max(created_time)) as wall_clock_time_ms,  
    ((0.0+sum(rows_sent)) / (datediff(ms,min(created_time),max(created_time)))) as [num_rows_per_millisecond]  
from [catalog].[execution_data_statistics]  
where execution_id = 132  
group by source_component_name, destination_component_name  
having (datediff(ms,min(created_time),max(created_time))) > 0  
order by source_component_name desc

Настройка вывода ошибок в компоненте потока данных

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

Настройка вывода ошибок

Настройку вывода ошибок можно производить двумя способами.

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

  • Использовать диалоговое окно редактора этого компонента. Некоторые компоненты позволяют настраивать вывод ошибок непосредственно через диалоговое окно редактора этого компонента. Однако в диалоговом окне редактора нельзя настроить выходы ошибок для источника ADO NET, ни для преобразования "Импорт столбцов", ни для преобразования "Команда OLE DB", ни для назначения SQL Server Compact.

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

Настройка вывода ошибок с помощью диалогового окна «Настроить вывод ошибок»

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. Чтобы открыть пакет, дважды щелкните его в обозревателе решений.

  3. В конструкторе служб SSIS щелкните вкладку Поток данных.

  4. Перетащите вывод ошибок, представленный красной стрелкой, из компонента источника ошибок в другой компонент потока данных.

  5. В диалоговом окне Настройка вывода ошибок выберите действие в столбцах Ошибка и Усечение для каждого столбца на входе компонента.

  6. Чтобы сохранить измененный пакет, в меню Файл выберите команду Сохранить выбранные элементы.

Добавление вывода ошибок с помощью диалогового окна редактора компонента

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. Чтобы открыть пакет, дважды щелкните его в обозревателе решений.

  3. В конструкторе служб SSIS щелкните вкладку Поток данных.

  4. Дважды щелкните компоненты потока данных, для которых необходимо настроить вывод ошибок, и в зависимости от компонента выполните следующее.

    • Щелкните Настроить вывод ошибок.

    • Щелкните Вывод ошибок.

  5. Установите параметр Ошибка для каждого столбца.

  6. Установите параметр Усечение для каждого столбца.

  7. Щелкните OK.

  8. Чтобы сохранить измененный пакет, в меню Файл выберите команду Сохранить выбранные элементы.

Настройка столбцов вывода ошибок

Для настройки столбцов вывода ошибок используется вкладка Свойства входов и выходов диалогового окна расширенного редактора .

Настройка столбцов вывода ошибок

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. Чтобы открыть пакет, дважды щелкните его в обозревателе решений.

  3. В конструкторе служб SSIS щелкните вкладку Поток данных.

  4. Щелкните правой кнопкой мыши компонент, столбцы вывода ошибок которого необходимо настроить, и выберите пункт Показать расширенный редактор.

  5. Щелкните вкладку Свойства входов и выходов и разверните <имя компонента> Вывод ошибок, а затем разверните Столбцы вывода.

  6. Щелкните столбец и обновите его свойства.

    Примечание.

    Список столбцов содержит столбцы входа компонента, столбцы ErrorCode и ErrorColumn , добавленные предшествующим выводом ошибок, и столбцы ErrorCode и ErrorColumn , добавленные данным компонентом.

  7. Щелкните OK.

  8. Чтобы сохранить измененный пакет, в меню Файл выберите команду Сохранить выбранные элементы.

Добавление средства просмотра данных к потоку данных

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

Путь связывает компоненты в потоке данных при помощи соединения выхода одного компонента потока данных с входом другого.

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

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

Добавление средства просмотра данных к потоку данных

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. Чтобы открыть пакет, дважды щелкните его в обозревателе решений.

  3. Перейдите на вкладку Поток управления , если она еще не активирована.

  4. Щелкните задачу потока данных, к потоку данных которой нужно подключить средство просмотра данных, затем перейдите на вкладку Поток данных .

  5. Щелкните правой кнопкой мыши путь между двумя компонентами потока данных, затем выберите Изменить.

  6. На странице Общие можно просматривать и изменять свойства путей. Например, в раскрывающемся списке PathAnnotation можно выбрать заметку, отображаемую рядом с путем.

  7. На странице Метаданные можно просмотреть метаданные столбца и скопировать их в буфер обмена.

  8. На странице Средство просмотра данных щелкните Включить средство просмотра данных.

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

    Примечание.

    Значения типа данных DT_DATE, DT_DBTIME2, DT_FILETIME, DT_DBTIMESTAMP, DT_DBTIMESTAMP2 и DT_DBTIMESTAMPOFFSET отображаются в сетке как строки, форматированные по стандарту ISO 8601, а разделитель T заменяется пробелом. Значения, представляющие типы данных DT_DATE и DT_FILETIME, включают семь цифр дробной части секунды. Поскольку тип данных DT_FILETIME хранит только три цифры дробной части секунды, сетка отображает нули в остальных четырех разрядах. Значения, представляющие тип данных DT_DBTIMESTAMP, содержат три цифры для дробной части секунды. У типов данных DT_DBTIME2, DT_DBTIMESTAMP2 и DT_DBTIMESTAMPOFFSET число разрядов для долей секунды соответствует масштабу, указанному для типа данных столбца. Дополнительные сведения о форматах ISO 8601 см. в разделе Date and Time Formats. Дополнительные сведения о типах данных см. в разделе Integration Services Data Types.

  10. Щелкните OK.

Точки подключения потока данных

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

  1. Создайте экземпляр выполнения пакета с помощью хранимой процедуры catalog.create_execution (база данных SSISDB).

  2. Добавьте точку отбора данных с помощью одной из хранимых процедур: catalog.add_data_tap или catalog.add_data_tap_by_guid.

  3. Запустите выполнение пакета с помощью хранимой процедуры catalog.start_execution (база данных SSISDB).

Пример SQL-скрипта, реализующий шаги, описанные выше.

Declare @execid bigint  
EXEC [SSISDB].[catalog].[create_execution] @folder_name=N'ETL Folder', @project_name=N'ETL Project', @package_name=N'Package.dtsx', @execution_id=@execid OUTPUT  
EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt'  
EXEC [SSISDB].[catalog].[start_execution] @execid

Параметры имен папки, проекта и пакета хранимой процедуры create_execution соответствуют именам папки, проекта и пакета в каталоге служб Integration Services. Имена папки, проекта и пакета, используемые при вызове create_execution, можно получить в SQL Server Management Studio, как показано на следующем рисунке. Если вашего проекта служб SSIS здесь нет, возможно, вы еще не развернули проект на сервер служб SSIS. Щелкните правой кнопкой мыши по проекту служб SSIS в Visual Studio и выберите команду «Развернуть», чтобы развернуть проект на нужный сервер служб SSIS.

Вместо ввода инструкций SQL можно сформировать скрипт выполнения пакета, выполнив следующие действия.

  1. Щелкните правой кнопкой мыши Package.dtsx и выберите команду Выполнить.

  2. Нажмите кнопку Скрипт на панели инструментов, чтобы сформировать скрипт.

  3. Теперь добавьте инструкцию add_data_tap до вызова start_execution.

Параметр task_package_path хранимой процедуры add_data_tap соответствует свойству PackagePath задачи потока данных в Visual Studio. В Visual Studio щелкните правой кнопкой мыши Задача потока данных, а затем выберите Свойства . Откроется окно свойств. Запишите значение свойства PackagePath, чтобы использовать его в качестве значения параметра task_package_path для вызова хранимой процедуры add_data_tap.

Параметр dataflow_path_id_string хранимой процедуры add_data_tap соответствует свойству IdentificationString пути потока данных, к которому требуется добавить перехват данных. Чтобы получить dataflow_path_id_string, щелкните по пути потока данных (стрелка между задачами в потоке данных) и отметьте значение свойства IdentificationString в окне свойств.

При выполнении скрипта выходной файл сохраняется в папке <Program Files>\Microsoft SQL Server\110\DTS\DataDumps. Если файл с таким именем уже существует, будет создан новый файл с суффиксом (например, output[1].txt).

Как уже упоминалось ранее, вы также можете использовать хранимую процедуру catalog.add_data_tap_by_guid вместо того, чтобы использовать хранимую процедуру add_data_tap. Эта хранимая процедура принимает в качестве параметра идентификатор задачи потока данных вместо task_package_path. Идентификатор этой задачи потока данных можно узнать в окне «Свойства» среды Visual Studio.

Удаление отвода данных

Отвод данных можно удалить до начала выполнения, воспользовавшись хранимой процедурой catalog.remove_data_tap . Эта хранимая процедура принимает в качестве параметра идентификатор data tap, который можно получить в качестве выходного значения хранимой процедуры add_data_tap.

DECLARE @tap_id bigint  
EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt' @data_tap_id=@tap_id OUTPUT  
EXEC [SSISDB].[catalog].remove_data_tap @tap_id

Список всех точек доступа к данным

Можно также получить список всех «data taps» с помощью представления catalog.execution_data_taps. В следующем примере извлекаются точки доступа к данным для экземпляра выполнения спецификации (ID: 54).

select * from [SSISDB].[catalog].execution_data_taps where execution_id=@execid

Вопросы производительности

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

Видео

Этот видеоролик на TechNet показывает, как добавлять и использовать точки перехвата данных в каталоге SSISDB SQL Server 2012, которые помогают программно отлаживать пакеты и захватывать частичные результаты в процессе выполнения. В статье также рассматривается, как просматривать список и удалять точки перехвата данных, а также приводятся рекомендации по их использованию в пакетах SSIS.

Связанные материалы

  • Last updated on