Debugging Data Flow in SQL Server Integration Services
SQL Server Business Intelligence Development Studio provides use SQL Server Integration Services package. The tools sample a subset of data, capture data flow row counts. The view data passes through data paths. It redirects data that generates errors, and package execution. This tool for package contains data flow, destination or transformations are being performed SSIS configured components in Debugging Data Flow.
SSIS configured components in Debugging Data Flow
The better understand the debugging tools, the more efficient troubleshoot data flow. The test environment includes comma-separated text file table in a SQL Server database. that retrieves data from the text file and inserts it into the table. The text file contains data from the Person
SSIS configured the package following components
- A connection manager to the AdventureWorks2008R2 database.
- Connection managers to the text file with the source data.
- An Execute SQL task that truncates the Person Name table.
- A Data Flow task retrieves data from the text file. creates a derived column, and inserts the data into the Person Name table.
The data flow components processing the Person data. the OLE DB Source, Derived Column, and OLE DB Destination components. The Derived Column transformation first and last names into the column named Full Name. The other components in the data flow are specific to debugging the rest of the article.
Working with a Data Sample
The building up an SSIS bundle recovers huge amounts of information. it can be useful to work with the subset of information the point issues in the information stream. SSIS provides two data flow components work with the subset of data. The Row Transformation Component number of rows want to random data sample. the Percentage Sampling Transformation component percentage of rows. Both components support two data outputs: the sampled data and one for the unstapled data. Each component seed value samples are same each time run the package.
Verifying Row Counts
The data passes through a data flow the SSIS design surface. The displays the number of rows passing along each data path. The count changes as data move through the pipeline. the package has finished executing, the number displayed is the total number of rows. that passed through the data path in the last buffer. If there were many buffers, the final number would not provide an accurate count. The Add Row Count Transformation component to the data flow. The transformation provides a final count. that add the rows from all buffers and stores the final count in a variable. This valuable to guarantee specific point in the information stream contains columns. Then compare a number of rows in source/destination.
The troubleshooting data flow used to view the actual data passes through a data path. This by adding one or more data viewers to your data flow. SSIS supports several types of data viewers. The most used grid data viewer, which displays the data in tabular format. The create data viewers that display histograms, scatter plot charts or column charts. These types of data viewers tend to be useful for more analytical types of data review. The basic troubleshoot grid data viewer is the best place to start.
Configuring Error-Handling on the Components
Many data flow components handle to data generate an error. By default, if data causes an error, the component fails. The configure components to redirect problem rows.
Monitoring Package Execution
The device for information stream identified with the execution and SSIS configuration surface. At the point when a bundle is running the information stream to occurring with every part. Row counts displayed the data paths and the components change colors. they’re executed By observing colors.
- White. The component has not executed.
- Yellow. The component is extracting, transforming, or loading data.
- Green. Component has completed its operation.
- Red. Component generated errors and package execution stopped.