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

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

  • Статья

Применяется к:sql Server SSIS Integration Runtime в Фабрика данных Azure

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

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

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

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

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

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

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

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

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

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

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

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

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

Счетчики строк

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

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

Отчет о состоянии

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

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

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

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

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

Примечание.

Чтобы получать необходимые сведения с помощью представления catalog.execution_data_statistics, уровнем ведения журнала должен быть Подробно .

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

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 . Эта хранимая процедура принимает в качестве параметра идентификатор отвода данных, который можно получить после вызова хранимой процедуры 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

Перечисление всех отводов данных

С помощью представления add_data_tap можно перечислить все отводы данных. В следующем примере показано, как извлечь отводы данных для экземпляра выполнения спецификации (ID: 54).

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

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

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

Видео

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

См. также

Обработка ошибок в данных