疑難排解封裝開發的工具Troubleshooting Tools for Package Development

Integration ServicesIntegration Services 包含在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中開發封裝時可以用於疑難排解封裝的功能和工具。 includes features and tools that you can use to troubleshoot packages while you are developing them in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

疑難排解設計階段驗證問題Troubleshooting Design-time Validation Issues

在最新版的 Integration ServicesIntegration Services中開啟封裝時,系統會先驗證所有連線,然後才驗證所有資料流程元件,並將緩慢或無法使用的所有連線設為離線工作。In the current release of Integration ServicesIntegration Services, when a package is opened, the system validates all connections before validating all of the data flow components and sets any connections that are slow or unavailable to work offline. 這有助於減少驗證封裝資料流程的延遲。This helps reduce the delay in validating the package data flow.

開啟封裝之後,您也可以以滑鼠右鍵按一下 [連接管理員] 區域中的連接管理員,然後按一下 [離線工作] 來關閉連接。After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. 這可以在 SSIS 設計師中加快作業。This can speed up operations in the SSIS Designer.

已設為離線工作的連接將保持離線狀態,直到您執行下列其中一項作業為止:Connections that have been set to work offline, will remain offline until you do one of the following:

  • 以滑鼠右鍵按一下 SSIS 設計師之 [連接管理員] 區域中的連接管理員,然後按一下 [測試連接性] 來測試連接。Test the connection by right-clicking the connection manager in the Connection Managers area of SSIS Designer and then clicking Test Connectivity.

    例如,開啟封裝之後,一開始會將連線設為離線工作。For example, a connection is initially set to work offline when the package is opened. 您要修改連接字串來解決問題,然後按一下 [測試連接性] 來測試連線。You modify the connection string to resolve the issue and click Test Connectivity to test the connection.

  • 重新開啟封裝或重新開啟包含封裝的專案。Re-open the package or re-open the project that contains the package. 在封裝的所有連線中會再次執行驗證。Validation is run again on all of the connections in the package.

    Integration ServicesIntegration Services 包含以下額外的功能,以協助您避免驗證錯誤: includes the following, additional features to help you avoid validation errors :

  • 無法使用資料來源時,將所有封裝和所有連線設為離線工作Set all of the package and all of the connections to work offline when data sources are not available. 您可以從 [SSIS] 功能表啟用 [離線工作]You can enable Work Offline from the SSIS menu. 不同於 DelayValidation 屬性,在尚未開啟封裝之前, [離線工作] 就已經可供使用。Unlike the DelayValidation property, the Work Offline option is available even before you open a package. 您也可以啟用 [離線工作] 來加速設計師中的作業,並只有在想要驗證封裝時才停用這個功能。You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.

  • 在執行階段前無效的封裝元素上設定 DelayValidation 屬性Configure the DelayValidation property on package elements that are not valid until run time. 您可以將封裝元素 (其組態在設計階段無效) 的 DelayValidation 設為 True ,以避免發生驗證錯誤。You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors. 例如,您可能有一項會使用目的地資料表的資料流程工作,而這個目的地資料表卻要等到執行 SQL 工作在執行階段建立資料表後才會存在。For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time. DelayValidation 屬性可以在封裝層級啟用,也可以在封裝所包含的個別工作和容器層級啟用。The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes. 一般而言,當您部署封裝時,必須讓相同封裝元素上的這個屬性設為 True ,以避免在執行階段發生相同的驗證錯誤。Normally you must leave this property set to True on the same package elements when you deploy the package, to prevent the same validation errors at run time.

    您可以針對資料流程工作設定 DelayValidation 屬性,但無法針對個別資料流程元件設定這個屬性。The DelayValidation property can be set on a Data Flow task, but not on individual data flow components. 將個別資料流程元件的 ValidateExternalMetadata 屬性設為 false中開發封裝時可以用於疑難排解封裝的功能和工具。You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to false. 不過,當這個屬性的值是 false時,元件不會察覺對外部資料來源之中繼資料所做的變更。However, when the value of this property is false, the component is not aware of changes to the metadata of external data sources.

    如果發生驗證時,此封裝所使用的資料庫物件遭到鎖定,驗證程序可能會停止回應。If database objects that are used by the package are locked when validation occurs, the validation process might stop responding. 在這些情況下, SSISSSIS 設計師也會停止回應。In these circumstances, the SSISSSIS Designer also stops responding. 您可以使用 Management StudioManagement Studio 來關閉 SQL ServerSQL Server中的關聯工作階段,以便繼續驗證。You can resume validation by using Management StudioManagement Studio to close the associated session in SQL ServerSQL Server. 您也可以使用本章節所述的設定來避開此問題。You can also avoid this issue by using the settings described in this section.

疑難排解控制流程Troubleshooting Control Flow

Integration ServicesIntegration Services 包含下列功能與工具,可以用來在封裝開發期間疑難排解封裝中的控制流程: includes the following features and tools that you can use to troubleshoot the control flow in packages during package development:

  • 設定工作、容器及封裝的中斷點Set breakpoints on tasks, containers, and the package. 可以使用「 SSISSSIS 設計師」提供的圖形工具來設定中斷點。You can set breakpoints by using the graphical tools that SSISSSIS Designer provides. 中斷點可以在封裝層級啟用,也可以在封裝包含之個別工作和容器層級啟用。Breakpoints can be enabled at the package level, or at the level of the individual tasks and containers that the package includes. 有些工作和容器會提供設定中斷點的其他中斷條件,Some tasks and containers provide additional break conditions for setting breakpoints. 例如,您可以在「For 迴圈」容器上啟用中斷條件,以便在迴圈的每個反覆運算開始時暫停執行。For example, you can enable a break condition on the For Loop container that suspends execution at the start of each iteration of the loop.

  • 使用偵錯視窗Use the debugging windows. 執行具有中斷點的封裝時, SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 中的偵錯視窗可讓您存取變數值和狀態訊息。When you run a package that has breakpoints, the debug windows in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) provide access to variable values and status messages.

  • 檢視 [進度] 索引標籤上的資訊SSISSSIS 設計師」會提供在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)Review the information on the Progress tab. SSISSSIS Designer provides additional information about control flow when you run a package in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). [進度] 索引標籤會以執行順序列出工作和容器,並包含每個工作和容器 (包括封裝本身) 的開始與完成時間、警告及錯誤訊息。The Progress tab lists tasks and containers in order of execution and includes start and finish times, warnings, and error messages for each task and container, including the package itself.

    如需這些功能的詳細資訊,請參閱< Debugging Control Flow>。For more information on these features, see Debugging Control Flow.

疑難排解資料流程Troubleshooting Data Flow

Integration ServicesIntegration Services 包含下列功能與工具,可以用來在封裝開發期間疑難排解封裝中的資料流程: includes the following features and tools that you can use to troubleshoot the data flows in packages during package development:

  • 只使用部分資料進行測試Test with only a subset of your data. 如果只使用資料集的範例來疑難排解封裝中的資料流程,則可以包括「百分比取樣」或「資料列取樣」轉換,以在執行階段建立內嵌資料範例。If you want to troubleshoot the data flow in a package by using only a sample of the dataset, you can include a Percentage Sampling or Row Sampling transformation to create an in-line data sample at run time. 如需相關資訊,請參閱 Percentage Sampling TransformationRow Sampling TransformationFor more information, see Percentage Sampling Transformation and Row Sampling Transformation.

  • 使用資料檢視器監看資料在資料流程中的移動Use data viewers to monitor data as it moves through the data flow. 資料檢視器會在資料於來源、轉換和目的地之間移動時顯示資料值。Data viewers display data values as the data moves between sources, transformations, and destinations. 資料檢視器可以在方格中顯示資料。A data viewer can display data in a grid. 您可以將資料檢視器中的資料複製到 [剪貼簿] 中,然後將資料貼到檔案或 Excel 試算表中。You can copy the data from a data viewer to the Clipboard, and then paste the data into a file or Excel spreadsheet. 如需相關資訊,請參閱 Debugging Data Flow 中開發封裝時可以用於疑難排解封裝的功能和工具。For more information, see Debugging Data Flow .

  • 設定支援錯誤輸出之資料流程元件中的錯誤輸出Configure error outputs on data flow components that support them. 許多資料流程來源、轉換和目的地也都支援錯誤輸出。Many data flow sources, transformations, and destinations also support error outputs. 經由設定資料流程元件的錯誤輸出,可以將有錯誤的資料導向其他目的地;By configuring the error output of a data flow component, you can direct data that contains errors to a different destination. 例如,您可以在另一個文字檔中擷取失敗或被截斷的資料。For example, you can capture the data that failed or was truncated in a separate text file. 您也可以將資料檢視器附加至錯誤輸出,並且只檢查錯誤的資料。You can also attach data viewers to error outputs and examine only the erroneous data. 在設計階段,錯誤輸出會擷取有問題的資料值,協助您開發可有效處理實際資料的封裝。At design time, error outputs capture troublesome data values to help you develop packages that deal effectively with real-world data. 不過,其他疑難排解工具與功能通常都只能用於設計階段,但錯誤輸出即使在實際執行環境中還是很有用。However, while other troubleshooting tools and features are useful only at design time, error outputs retain their usefulness in the production environment. 如需詳細資訊,請參閱 處理資料中的錯誤For more information, see Error Handling in Data.

  • 擷取已處理的資料列計數Capture the count of rows processed. 當您在「 SSISSSIS 設計師」中執行封裝時,經由路徑傳送的資料列數目會顯示在資料流程設計師中。When you run a package in SSISSSIS Designer, the number of rows that have passed through a path is displayed in the data flow designer. 隨著資料不斷經由路徑移動,這個數目會定期更新。This 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.

  • 檢視 [進度] 索引標籤上的資訊SSISSSIS 設計師」會提供在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)Review the information on the Progress tab. SSISSSIS Designer provides additional information about data flows when you run a package in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). [進度] 索引標籤會以執行順序列出資料流程元件,並包含封裝每個階段的進度資訊 (以完成百分比顯示),以及包含寫入目的地之資料列數目的資訊。The Progress tab lists data flow components in order of execution and includes information about progress for each phase of the package, displayed as percentage complete, and the number of rows written to the destination.

    如需這些功能的詳細資訊,請參閱< Debugging Data Flow>。For more information on these features, see Debugging Data Flow.

疑難排解指令碼Troubleshooting Scripts

MicrosoftMicrosoft Visual StudioVisual Studio Tools for Applications (VSTA) 是一種開發環境,您會在此環境中撰寫指令碼工作和指令碼元件所使用的指令碼。 Visual StudioVisual Studio Tools for Applications (VSTA) is the development environment in which you write the scripts that are used by the Script task and Script component. VSTA 包含下列功能與工具,可以用來在封裝開發期間針對指令碼進行疑難排解:VSTA provides the following features and tools that you can use to troubleshoot scripts during package development:

  • 在指令碼工作的指令碼中設定中斷點。Set breakpoints in script in Script tasks. VSTA 只針對指令碼工作中的指令碼提供偵錯支援。VSTA provides debugging support for scripts in the Script task only. 在指令碼工作中設定的中斷點會與封裝中設定的中斷點,以及封裝中的工作和容器整合,以便可以對所有的封裝元素進行嚴密的偵錯。The breakpoints that you set in Script tasks are integrated with the breakpoints that you set on packages and the tasks and containers in the package, enabling seamless debugging of all package elements.

    注意

    當您為包含多個指令碼工作的封裝偵錯時,偵錯工具只會在一個指令碼工作中叫用中斷點,而且將會忽略其他指令碼工作中的中斷點。When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. 如果指令碼工作是 [Foreach 迴圈] 或 [For 迴圈] 容器的一部分,偵錯工具會在迴圈的第一次反覆運算後,忽略指令碼工作中的中斷點。If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

    如需詳細資訊,請參閱 Debugging ScriptFor more information, see Debugging Script. 如需如何偵錯指令碼元件的建議,請參閱 指令碼元件的程式碼撰寫和偵錯For suggestions about how to debug the Script component, see Coding and Debugging the Script Component.

疑難排解沒有隨附描述的錯誤Troubleshooting Errors without a Description

如果您在封裝開發期間遇到沒有隨附描述的 Integration ServicesIntegration Services 錯誤號碼,可以在 Integration Services 錯誤和訊息參考中找到描述。If you encounter an Integration ServicesIntegration Services error number without an accompanying description during package development, you can locate the description in Integration Services Error and Message Reference. 此清單這次沒有包含疑難排解資訊。The list does not include troubleshooting information at this time.

另請參閱See Also

封裝執行的疑難排解工具 Troubleshooting Tools for Package Execution
資料流程效能功能Data Flow Performance Features