Troubleshooting Package Development
Integration Services includes features and tools that you can use to troubleshoot packages while you are developing them in Business Intelligence Development Studio.
Troubleshooting Design-time Validation Issues
While you are developing packages in BI Development Studio, there may be times when you cannot connect to your data sources, or when portions of your package cannot be validated until prior tasks in the package have executed at run time. Integration Services includes the following features to help you avoid the validation errors that would otherwise result from these conditions:
Work Offline when data sources are not available. Normally, SSIS Designer tries to connect to each data source that is used by your package to validate the metadata associated with sources and destinations. This causes validation errors when the data sources are not available. To prevent these connection attempts, you can enable Work Offline from the SSIS menu. 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.
Configure the DelayValidation property on package elements that are not valid until run time. You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors. 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. The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes. 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.
The DelayValidation property can be set on a Data Flow task, but not on individual data flow components. You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to 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. In these circumstances, the SSIS Designer also stops responding. You can resume validation by using Management Studio to close the associated session in SQL Server. You can also avoid this issue by using the settings described in this section.
Important
When running a package with DTC transactions (TransactionOption property = required) and validating external metadata in data flow components (ValidateExternalMetadata property = true), the package can hang under the following conditions: when other tasks already exist in the DTC transactions before the data flow is executed, and Table or view or Table name or view name variable is used as the data access method for an OLE DB Destination. In this scenario, the validation process will be blocked by other tasks which are already running. This is because connections used for the validation are never enlisted in the DTC transactions. This is by design. To avoid this situation, set the data flow component ValidateExternalMetadata property to false.
Troubleshooting Control Flow
Integration 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. You can set breakpoints by using the graphical tools that SSIS 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 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. When you run a package that has breakpoints, the debug windows in Business Intelligence Development Studio provide access to variable values and status messages.
Review the information on the Progress tab. SSIS Designer provides additional information about control flow when you run a package in Business Intelligence Development Studio. 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.
For more information on these features, see Debugging Control Flow.
Troubleshooting Data Flow
Integration 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. For 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, histogram, scatter plot, or column chart. You can copy the data from a data viewer to the Clipboard, and then paste the data into a file or Excel spreadsheet. For more information, see How to: Add a Data Viewer to a 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 Handling Errors in the Data Flow.
Capture the count of rows processed. When you run a package in SSIS 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. For more information, see Row Count Transformation.
Review the information on the Progress tab. SSIS Designer provides additional information about data flows when you run a package in Business Intelligence Development Studio. 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.
For more information on these features, see Debugging Data Flow.
Troubleshooting Scripts
Microsoft Visual 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 provides the following features and tools that you can use to troubleshoot scripts during package development:
Set breakpoints in script in Script tasks. 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.
Note
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. 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.
For 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
If you encounter an Integration Services error number without an accompanying description during package development, you can locate the description in SSIS Error and Message Reference. The list does not include troubleshooting information at this time.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services