Home > SSIS Best Practices > SSIS – Troubleshooting in Development

SSIS – Troubleshooting in Development

Troubleshooting Design-time Validation Issues

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.

Troubleshooting Control Flow

Set breakpoints on tasks, containers, and the package. You can set breakpoints by using the graphical tools that SSIS Designer provides.

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.

    Troubleshooting Data Flow

    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.

    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.

    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.

    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.

    Review the information on the Progress tab.

    Troubleshooting Scripts

    Set breakpoints in script in Script tasks

    Categories: SSIS Best Practices
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )


    Connecting to %s

    %d bloggers like this: