偵錯資料流程Debugging Data Flow

MicrosoftMicrosoft Integration ServicesIntegration Services 和「 SSISSSIS 設計師」提供許多功能和工具,讓您用來疑難排解 Integration ServicesIntegration Services 封裝中的資料流程。 Integration ServicesIntegration Services and the SSISSSIS Designer include features and tools that you can use to troubleshoot the data flows in an Integration ServicesIntegration Services package.

  • SSISSSIS 設計師」會提供資料檢視器。 Designer provides data viewers.

  • SSISSSIS 設計師」和 Integration ServicesIntegration Services 轉換會提供資料列計數。 Designer and Integration ServicesIntegration Services transformations provide row counts.

  • SSISSSIS 設計師」提供執行階段的進度報表。 Designer provides progress reporting at run time.

資料檢視器Data Viewers

資料檢視器可以顯示資料流程中兩個元件之間的資料。Data viewers display data between two components in a data flow. 從資料來源擷取資料或資料第一次進入資料流程時、在轉換更新資料之前和之後,以及在資料載入其目的地之前,可以透過資料檢視器來顯示資料。Data viewers can display data when the data is extracted from a data source and first enters a data flow, before and after a transformation updates the data, and before the data is loaded into its destination.

若要檢視資料,請將資料檢視器附加至連接兩個資料流程元件的路徑。To view the data, you attach data viewers to the path that connects two data flow components. 具有在不同資料流程元件之間檢視資料的能力,可讓您更容易識別非預期的資料值、檢視轉換變更資料行值的方式,以及探索轉換失敗的原因。The ability to view data between data flow components makes it easier to identify unexpected data values, view the way a transformation changes column values, and discover the reason that a transformation fails. 例如,如果您發現參考資料表中的查閱失敗,為了修正此問題,您可能希望加入為空白資料行提供預設資料的轉換。For example, you may find that a lookup in a reference table fails, and to correct this you may want to add a transformation that provides default data for blank columns.

資料檢視器可以在方格中顯示資料。A data viewer can display data in a grid. 使用方格時,您需要選取要顯示的資料行。Using a grid, you select the columns to display. 選定資料行的值會以表格格式顯示。The values for the selected columns display in a tabular format.

您也可以在路徑上加入多個資料檢視器,You can also include multiple data viewers on a path. 並以不同格式顯示相同資料,例如,建立資料的圖表檢視與方格檢視,或是為不同資料行建立不同的資料檢視器。You can display the same data in different formats—for example, create a chart view and a grid view of the data—or create different data viewers for different columns of data.

當您將資料檢視器加入至路徑時,「 SSISSSIS 設計師」會在 [資料流程] 索引標籤之設計介面上的路徑旁邊,加入資料檢視器圖示。When you add a data viewer to a path, SSISSSIS Designer adds a data viewer icon to the design surface of the Data Flow tab, next to the path. 具備多個輸出的轉換 (例如「條件式分割」轉換) 可以在每個路徑上加入資料檢視器。Transformations that can have multiple outputs, such as the Conditional Split transformation, can include a data viewer on each path.

在執行階段, [資料檢視器] 視窗會開啟,並顯示以資料檢視器格式所指定的資訊。At run time, a Data Viewer window opens and displays the information specified by the data viewer format. 例如,使用方格格式的資料檢視器會顯示選定資料行的資料、傳遞至資料流程元件的輸出資料列數目,以及顯示的資料列數目。For example, a data viewer that uses the grid format shows data for the selected columns, the number of output rows passed to the data flow component, and the number of rows displayed. 資訊會以逐一緩衝區的方式顯示,且根據資料流程中的資料列寬度而定,緩衝區可能包含一或多個資料列。The information displays buffer by buffer and, depending on the width of the rows in the data flow, a buffer may contain more or fewer rows.

[資料檢視器] 對話方塊中,您可以將資料複製到剪貼簿、從資料表清除所有資料、重新設定資料檢視器、繼續資料流程,以及卸離或附加資料檢視器。In the Data Viewer dialog box, you can copy the data to the Clipboard, clear all data from the table, reconfigure the data viewer, resume the flow of data, and detach or attach the data viewer.

若要加入資料檢視器To add a data viewer

資料列計數Row Counts

經過某個路徑傳送的資料列數目,會顯示在「 設計師」中 [資料流程] SSISSSIS 索引標籤之設計介面上的該路徑旁邊。The number of rows that have passed through a path is displayed on the design surface of the Data Flow tab in SSISSSIS Designer next to the path. 隨著資料不斷經由路徑移動,該數目會定期更新。The number is updated periodically while the data moves through the path.

您也可以將「資料列計數」轉換加入資料流程,以擷取變數中的最後資料列計數。You can also add a Row Count transformation to the data flow to capture the final row count in a variable. 如需詳細資訊,請參閱 Row Count TransformationFor more information, see Row Count Transformation.

進度報表Progress Reporting

當您執行封裝時,「 SSISSSIS 設計師」會在 [資料流程] 索引標籤的設計介面上,使用指示狀態的色彩顯示每個資料流程元件,以描述進度。When you run a package, SSISSSIS Designer depicts progress on the design surface of the Data Flow tab by displaying each data flow component in a color that indicates status. 當每個元件開始執行其工作時,會從無色彩變更為黃色,並在成功完成時變更為綠色,When each component starts to perform its work, it changes from no color to yellow, and when it finishes successfully, it changes to green. 紅色則表示該元件已失敗。A red color indicates that the component failed.

下表描述色彩編碼。The following table describes the color-coding.

ColorColor 描述Description
無色彩No color 正在等候由資料流程引擎呼叫。Waiting to be called by the data flow engine.
黃色Yellow 正在執行轉換、擷取資料或載入資料。Performing a transformation, extracting data, or loading data.
綠色Green 已成功執行。Ran successfully.
紅色red 已執行但發生錯誤。Ran with errors.

資料流程分析Analysis of Data Flow

您可以使用 catalog.execution_data_statistics SSISDB 資料庫檢視,分析封裝的資料流程。You can use the catalog.execution_data_statistics SSISDB database view to analyze the data flow of packages. 每當資料流程元件傳送資料至下游元件,此檢視就會顯示一個資料列。This view displays a row each time a data flow component sends data to a downstream component. 您可以使用這項資訊深入了解傳送至每個元件的資料列。The information can be used to gain a deeper understanding of the rows that are sent to each component.


您必須將記錄層次設定為 [詳細資訊],以透過 catalog.execution_data_statistics 檢視來擷取資訊。The logging level must be set to Verbose in order to capture information with the catalog.execution_data_statistics view.

下列範例顯示在封裝元件之間傳送的資料列數。The following example displays the number of rows sent between components of a package.

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   

下列範例計算針對特定執行,每個元件每毫秒所傳送的資料列數目。The following example calculates the number of rows per millisecond sent by each component for a specific execution. 計算值包括:The calculated values are:

  • total_rows - 元件傳送的所有資料列總和total_rows - the sum of all the rows sent by the component

  • wall_clock_time_ms – 每個元件經過的執行時間總計 (以毫秒為單位)wall_clock_time_ms – the total elapsed execution time, in milliseconds, for each component

  • num_rows_per_millisecond – 每個元件每毫秒傳送的資料列數num_rows_per_millisecond – the number of rows per millisecond sent by each component

    HAVING 子句可用來避免在計算中發生除以零的錯誤。The HAVING clause is used to prevent a divide-by-zero error in the calculations.

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  

在資料流程元件中設定錯誤輸出Configure an Error Output in a Data Flow Component

許多資料流程元件都支援錯誤輸出,因元件的不同, SSISSSIS 設計師會以不同的方式設定錯誤輸出。Many data flow components support error outputs, and depending on the component, SSISSSIS Designer provides different ways to configure an error output. 除了設定錯誤輸出以外,您也可以設定錯誤輸出的資料行。In addition to configuring an error output, you can also configure the columns of an error output. 其中包括設定此元件所加入的 ErrorCodeErrorColumn 資料行。This includes configuring the ErrorCode and ErrorColumn columns that are added by the component.

設定錯誤輸出Configuring an Error Output

若要設定錯誤輸出,您有兩個選項:To configure an error output, you have two options:

  • 使用 [設定錯誤輸出] 對話方塊。Use the Configure Error Output dialog box. 您可以使用此對話方塊,在支援錯誤輸出的任何資料流程元件中設定錯誤輸出。You can use this dialog box to configure an error output on any data flow component that supports an error output.

  • 請針對此元件使用編輯器對話方塊。Use the editor dialog box for the component. 某些元件可讓您直接從它們的編輯器對話方塊設定錯誤輸出;Some components let you configure error outputs directly from their editor dialog box. 但如果是 ADO NET 來源、匯入資料行轉換、OLE DB 命令轉換或 [SQL Server]SQL Server Compact 目的地,您便無法從編輯器對話方塊設定錯誤輸出。However, you cannot configure error outputs from the editor dialog box for the ADO NET source, the Import Column transformation, the OLE DB Command transformation, or the [SQL Server]SQL Server Compact destination.

    下列程序描述如何使用這些對話方塊來設定錯誤輸出。The following procedures describe how to use these dialog boxes to configure error outputs.

若要使用 [設定錯誤輸出] 對話方塊設定錯誤輸出To configure an error output using the Configure Error Output dialog box

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含所需封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. SSISSSIS 設計師中,按一下 [資料流程] 索引標籤。In SSISSSIS Designer, click the Data Flow tab.

  4. 將錯誤輸出 (以紅色箭頭表示) 從錯誤來源元件拖曳到資料流程中的另一個元件。Drag the error output, represented by the red arrow, from the component that is the source of the errors to another component in the data flow.

  5. 在 [設定錯誤輸出] 對話方塊中,針對元件輸入中的每個資料行,在 [錯誤] 和 [截斷] 資料行中選取動作。In the Configure Error Output dialog box, select an action in the Error and Truncation columns for each column in the component input.

  6. 若要儲存已更新的封裝,請按一下 [檔案] 功能表上的 [儲存選取項目]。To save the updated package, on the File menu, click Save Selected Items.

針對此元件使用編輯器對話方塊來加入錯誤輸出To add an error output using the editor dialog box for the component

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含所需封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. 在 [ SSISSSIS 設計師] 中,按一下 [資料流程] 索引標籤。In SSISSSIS Designer, click the Data Flow tab.

  4. 按兩下要在其中設定錯誤輸出的資料流程元件,並依據此元件,執行下列其中一個步驟:Double-click the data flow components in which you want to configure an error output and, depending on the component, do one of the following steps:

    • 按一下 [設定錯誤輸出]。Click Configure Error Output.

    • 按一下 [錯誤輸出]。Click Error Output.

  5. 為每個資料行設定 [錯誤] 選項。Set the Error option for each column.

  6. 為每個資料行設定 [截斷] 選項。Set the Truncation option for each column.

  7. 按一下 [確定] 。Click OK.

  8. 若要儲存已更新的封裝,請按一下 [檔案] 功能表上的 [儲存選取項目]。To save the updated package, on the File menu, click Save Selected Items.

設定錯誤輸出資料行Configuring Error Output Columns

若要設定錯誤輸出資料行,您必須使用 [進階編輯器] 對話方塊的 [輸入與輸出屬性] 索引標籤。To configure the error output columns, you have to use the Input and Output Properties tab of the Advanced Editor dialog box.

設定錯誤輸出資料行To configure error output columns

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含所需封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. 在 [ SSISSSIS 設計師] 中,按一下 [資料流程] 索引標籤。In SSISSSIS Designer, click the Data Flow tab.

  4. 以滑鼠右鍵按一下要設定其錯誤輸出資料行的元件,並按一下 [顯示進階編輯器]。Right-click the component whose error output columns you want to configure and click Show Advanced Editor.

  5. 按一下 [輸入與輸出屬性] 索引標籤,並展開 [<元件名稱> 錯誤輸出],然後展開 [輸出資料行]。Click the Input and Output Properties tab and expand <component name> Error Output and then expand Output Columns.

  6. 按一下資料行並更新其屬性。Click a column and update its properties.


    資料行的清單包括元件輸入中的資料行、上一個錯誤輸出加入的 ErrorCodeErrorColumn 資料行,以及此元件加入的 ErrorCodeErrorColumn 資料行。The list of columns includes the columns in the component input, the ErrorCode and ErrorColumn columns added by previous error outputs, and the ErrorCode and ErrorColumn columns added by this component.

  7. 按一下 [確定].Click OK.

  8. 若要儲存已更新的封裝,請按一下 [檔案] 功能表上的 [儲存選取項目]。To save the updated package, on the File menu, click Save Selected Items.

將資料檢視器加入資料流程Add a Data Viewer to a Data Flow

本主題描述如何在資料流程中加入和設定資料檢視器。This topic describes how to add and configure a data viewer in a data flow. 資料檢視器可以顯示在兩個資料流程元件之間移動的資料。A data viewer displays data that is moving between two data flow components. 例如,在資料流程中的轉換修改從資料來源擷取的資料之前,資料檢視器可以先顯示該資料。For example, a data viewer can display the data that is extracted from a data source before a transformation in the data flow modifies the data.

將一個資料流程元件的輸出與另一元件的輸入連接,路徑可連接資料流程中的元件。A path connects components in a data flow by connecting the output of one data flow component to the input of another component.

在可以將資料檢視器加入封裝之前,該封裝必須包括「資料流程」工作和至少兩個連接的資料流程元件。Before you can add data viewers to a package, the package must include a Data Flow task and at least two data flow components that are connected.

將資料檢視器加入錯誤輸出中,以查看錯誤描述以及發生錯誤的資料行名稱。Add a data viewer to an error output to see the description of the error and the name of the column in which the error occurred. 錯誤輸出預設只包含錯誤和資料行的數值識別碼。By default the error output includes only numeric identifiers for the error and the column.

若要將資料檢視器加入資料流程To add a data viewer to a data flow

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含所需封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. 如果 [控制流程] 索引標籤尚未處於使用中,請按一下該索引標籤。Click the Control Flow tab, if it is not already active.

  4. 按一下要將資料檢視器附加至其資料流程的 [資料流程] 工作,然後按一下 [資料流程] 索引標籤。Click the Data Flow task to whose data flow you want to attach a data viewer and then click the Data Flow tab.

  5. 以滑鼠右鍵按一下兩個資料流程元件之間的路徑,然後按一下 [編輯]。Right-click a path between two data flow components, and click Edit.

  6. 在 [一般] 頁面上,您可以檢視和編輯路徑屬性。On the General page, you can view and edit path properties. 例如,您可以從 [PathAnnotation] 下拉式清單選取出現在路徑旁的註解。For example, from the PathAnnotation drop-down list you can select the annotation that appears next to the path.

  7. 在 [中繼資料] 頁面上,您可以檢視資料行中繼資料,並且將中繼資料複製到 [剪貼簿]。On the Metadata page, you can view the column metadata and copy the metadata to the Clipboard.

  8. 在 [資料檢視器] 頁面上,按一下 [啟用資料檢視器]。On the Data Viewer page, click Enable data viewer.

  9. 在 [要顯示的資料行] 區域中,選取要在資料檢視器中顯示的資料行。In the Columns to display area, select the columns you want to display in the data viewer. 根據預設,所有可用的資料行都會選取,並且在 [顯示的資料行] 清單中列出。By default, all the available columns are selected and listed in the Displayed Columns list. 將不想使用的資料行移至 [未使用的資料行] 清單,方法是選取它們然後按一下向左箭號。Move columns that you do not want to use to the Unused Column list by selecting them and then clicking the left arrow.


    在此方格中,代表 DT_DATE、DT_DBTIME2、DT_FILETIME、DT_DBTIMESTAMP、DT_DBTIMESTAMP2 和 DT_DBTIMESTAMPOFFSET 資料類型的值會以 ISO 8601 格式化字串的形式出現,而且空格分隔符號會取代 T 分隔符號。In the grid, values that represent the DT_DATE, DT_DBTIME2, DT_FILETIME, DT_DBTIMESTAMP, DT_DBTIMESTAMP2, and DT_DBTIMESTAMPOFFSET data types appear as ISO 8601 formatted strings and a space separator replaces the T separator. 代表 DT_DATE 和 DT_FILETIME 資料類型的值包括小數秒的七位數。Values that represent the DT_DATE and DT_FILETIME data types include seven digits for fractional seconds. 由於 DT_FILETIME 資料類型只會儲存小數秒的三位數,所以此方格會將其餘四位數顯示為零。Because the DT_FILETIME data type stores only three digits of fractional seconds, the grid displays zeros for the remaining four digits. 代表 DT_DBTIMESTAMP 資料類型的值包括小數秒的三位數。Values that represent the DT_DBTIMESTAMP data type include three digits for fractional seconds. 如果是代表 DT_DBTIME2、DT_DBTIMESTAMP2 和 DT_DBTIMESTAMPOFFSET 資料類型的值,則小數秒的位數會對應到針對資料行資料類型指定的小數位數。For values that represent the DT_DBTIME2, DT_DBTIMESTAMP2, and DT_DBTIMESTAMPOFFSET data types, the number of digits for fractional seconds corresponds to the scale specified for the column's data type. 如需 ISO 8601 格式的詳細資訊,請參閱 日期和時間格式For more information about ISO 8601 formats, see Date and Time Formats. 如需有關資料類型的詳細資訊,請參閱< Integration Services Data Types>。For more information about data types, see Integration Services Data Types.

  10. 按一下 [確定] 。Click OK.

資料流程點選Data Flow Taps

您可以在執行階段於封裝的資料流程路徑上加入資料點選,然後從資料點選將輸出導向至外部檔案。You can add a data tap on a data flow path of a package at runtime and direct the output from the data tap to an external file. 若要使用此功能,您必須使用專案部署模型將 SSIS 專案部署至 SSIS 伺服器。To use this feature, you must deploy your SSIS project using the project deployment model to an SSIS Server. 將封裝部署至伺服器之後,您需要對 SSISDB 資料庫執行 T-SQL 指令碼先加入資料點選,然後再執行該封裝。After you deploy the package to the server, you need to execute T-SQL scripts against the SSISDB database to add data taps before executing the package. 範例狀況如下:Here is a sample scenario:

  1. 使用 catalog.create_execution (SSISDB Database) 預存程序建立封裝的執行執行個體。Create an execution instance of a package by using the catalog.create_execution (SSISDB Database) stored procedure.

  2. 使用 catalog.add_data_tapcatalog.add_data_tap_by_guid 預存程序加入資料點選。Add a data tap by using either catalog.add_data_tap or catalog.add_data_tap_by_guid stored procedure.

  3. 使用 catalog.start_execution (SSISDB 資料庫) 啟動封裝的執行執行個體。Start the execution instance of the package by using the catalog.start_execution (SSISDB Database).

    以下是執行上述狀況各個步驟的範例 SQL 指令碼:Here is a sample SQL script that performs the steps described in the above scenario:

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 目錄中的資料夾、專案和封裝名稱。The folder name, project name, and package name parameters of the create_execution stored procedure correspond to the folder, project, and package names in the Integration Services catalog. 您可以從 SQL Server Management Studio 取得 create_execution 呼叫所要使用的資料夾、專案和封裝名稱,如下圖所示。You can get the folder, project, and package names to use in the create_execution call from the SQL Server Management Studio as shown in the following image. 如果您的 SSIS 專案沒有出現在此,表示您可能尚未將該專案部署至 SSIS 伺服器。If you do not see your SSIS project here, you may not have deployed the project to SSIS server yet. 請在 Visual Studio 中以滑鼠右鍵按一下 SSIS 專案,然後按一下 [部署] 將專案部署至所需的 SSIS 伺服器。Right-click on SSIS project in Visual Studio and click Deploy to deploy the project to the expected SSIS server.

除了輸入 SQL 陳述式之外,執行下列步驟也可以產生執行封裝指令碼:Instead of typing the SQL statements, you can generate the execute package script by performing the following steps:

  1. 以滑鼠右鍵按一下 [Package.dtsx],然後按一下 [執行]。Right-click Package.dtsx and click Execute.

  2. 按一下 [指令碼] 工具列按鈕以產生指令碼。Click Script toolbar button to generate the script.

  3. 接著,在 start_execution 呼叫前面加入 add_data_tap 陳述式。Now, add the add_data_tap statement before the start_execution call.

    add_data_tap 預存程序的 task_package_path 參數對應到 Visual Studio 中,資料流程工作的 PackagePath 屬性。The task_package_path parameter of add_data_tap stored procedure corresponds to the PackagePath property of the data flow task in Visual Studio. 在 Visual Studio 中,以滑鼠右鍵按一下 [資料流程工作],然後按一下 [屬性] 啟動 [屬性] 視窗。In Visual Studio, right-click the Data Flow Task, and click Properties to launch the Properties window. 請記下 PackagePath 屬性的值,其將做為 add_data_tap 預存程序呼叫的 task_package_path 參數值使用。Note the value of the PackagePath property to use it as a value for the task_package_path parameter for add_data_tap stored procedure call.

    add_data_tap 預存程序的 dataflow_path_id_string 參數對應到您要在其上加入資料點選之資料流程路徑的 IdentificationString 屬性。The dataflow_path_id_string parameter of add_data_tap stored procedure corresponds to the IdentificationString property of the data flow path to which you want to add a data tap. 若要取得 dataflow_path_id_string,請按一下資料流程路徑 (資料流程中位於工作之間的箭號),並記下 [屬性] 視窗所示 IdentificationString 屬性的值。To get the dataflow_path_id_string, click the data flow path (arrow between tasks in the data flow), and note the value of the IdentificationString property in the Properties window.

    當您執行指令碼時,輸出檔會儲存於 <Program Files>\Microsoft SQL Server\110\DTS\DataDumps。When you execute the script, the output file is stored in <Program Files>\Microsoft SQL Server\110\DTS\DataDumps. 如果已有同名的檔案存在,則將建立附帶尾碼的新檔案 (例如:output[1].txt)。If a file with the name already exists, a new file with a suffix (for example: output[1].txt) is created.

    如先前所述,您也可以使用 catalog.add_data_tap_by_guid預存程序,而不是使用 add_data_tap 預存程序。As mentioned earlier, you can also use catalog.add_data_tap_by_guidstored procedure instead of using add_data_tap stored procedure. 此預存程序接受資料流程工作的識別碼當做參數,而非 task_package_path。This stored procedure takes the ID of data flow task as a parameter instead of task_package_path. 您可以從 Visual Studio 屬性視窗取得資料流程工作的識別碼。You can get the ID of data flow task from the properties window in Visual Studio.

移除資料點選Removing a data tap

您可以使用 catalog.remove_data_tap 預存程序,在啟動執行之前移除資料點選。You can remove a data tap before starting the execution by using the catalog.remove_data_tap stored procedure. 此預存程序接受資料點選的識別碼當做參數,而識別碼可透過 add_data_tap 預存程序的輸出取得。This stored procedure takes the ID of data tap as a parameter, which you can get as an output of the add_data_tap stored procedure.

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  

列出所有資料點選Listing all data taps

您也可以使用 catalog.execution_data_taps 檢視表,列出所有資料點選。You can also list all the data taps by using the catalog.execution_data_taps view. 下列範例會擷取規格執行之執行個體 (識別碼:54) 的資料點選。The following example extracts data taps for a specification execution instance (ID: 54).

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

效能考量Performance consideration

啟用詳細資訊記錄層次和加入資料點選會致使資料整合方案執行更多 I/O 作業。Enabling verbose logging level and adding data taps increase the I/O operations performed by your data integration solution. 因此,建議您只有在進行疑難排解時才加入資料點選。Hence, we recommend that you add data taps only for troubleshooting purposes


這部 TechNet 上的影片 示範了如何在 SQL Server 2012 SSISDB 目錄中加入/使用資料點選,協助您以程式設計方式對封裝進行偵錯及在執行階段擷取部分結果。This video on TechNet demonstrates how to add/use data taps in SQL Server 2012 SSISDB catalog that help with debugging packages programmatically and capturing the partial results at the runtime. 該影片也將討論如何列出/移除這些資料點選,以及在 SSIS 封裝中使用資料點選的最佳作法。It also discusses how to list/ remove these data taps and best practices for using data taps in SSIS packages.

另請參閱See Also

處理資料中的錯誤Error Handling in Data