When in development phase, we require to test the working of the package in Development environment. Before deploying the package in the final Production environment, we will test the package to check the working of the package.
To debug Data Flow Task in SSIS, we use Data Viewers. Data viewers display data between two consecutive components in Data Flow. 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. A data viewer can display data in a grid, histogram, scatter plot, or column chart. We can also include multiple data viewers on a path. We can display the same data in different formats.
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.
Launch SSISStudy / SSISStudy2012 project created earlier or create an new SSIS project. Create a new SSIS package. Rename the package as DataViewerExample.dtsx
Drag and drop a Data Flow Task on the Control Flow area. Configure Data Flow Task to read data from HumanResources.Employee table in AdventureWorks database using OLE DB Source.
Drag and drop Derived Column into Data Flow area and connect OLE DB Source to this Derived Column.
Right click on the green line connecting OLE DB Source and Derived Column and select Edit to open Data Flow Path Editor window.
Name can be edited or leave it to default. PathAnnotation provides with dropdown and following options to name the path:
- AsNeeded: If required to be named
- SourceName: Path name similar to source
- PathName: Path name similar to the output of the component where path originated.
- Never: Not required to name the path
MetaData lists the metadata information of the columns
Select Data Viewers and select Add button to open Configure Data Viewer window
Rename, if wish to, under Name. Select Grid under Type. Select Grid Tab. Under Unused columns, we see all the columns that are not selected to be displayed in Data Viewer. In this example, we have selected all the columns to be displayed as shown under Displayed columns. Now click OK.
The Data Viewer is listed under Data viewers. We can select and click on Delete to drop this Data Viewer or click on Configure to open the earlier window to reconfigure.
Click OK to return back to the Data Flow area.
Now we should see a small icon next to the path
Execute the package and we should see a sample output to tell us how our data will look when package finishes its execution.
Click on either Play button or Detach button to exit the Data Viewer or click on Copy Data to copy the data to Clipboard.
Thus we learnt the ways to use Data Viewer to debug Data Flow Task.
Click for NEXT article in the sequence.
Please feel free to correct me by commenting your suggestions and feedback.
No comments:
Post a Comment