One of the cool features introduced in SQL Server Integration Services that adds real value add for the developers are Data Taps. Data taps provides a method of debugging packages programmatically during runtime.
One of the greatest strengths of Integration Services is the availability of good debugging features thanks to Visual Studio’s feature rich Integrated Development Environment. The ability to add breakpoints, use event handlers, visualize data in the data flow engine, add watch windows to inspect local variables, and much more, have helped developers a lot in the past in debugging during the package development stage. However, one thing that was lacking was the ability to debug and capture partial results at runtime. The only way to implement this on earlier versions was to use custom logging through utilizing an audit table. With the introduction of data taps, this has now become very easy to implement natively within SSIS.
Below is an illustration of data taps. Consider the package example as follows:
We have a data flow task here with multiple branches. Now consider the scenario where we need to capture the results of these individual branches at runtime. At design time, we can achieve this by using a data viewer on the corresponding data paths. At runtime we can achieve the same effect by using data taps in SSIS 2012. More generally, data taps can be regarded as a “runtime dataviewer” created programmatically. Let’s see how to add datatap in the above case.
Data Taps are implemented by making use of two system stored procedures available in SSISDB i.e. [SSISDB].[catalog].add_data_tap and [SSISDB].[catalog].add_data_tap_by_guid. The procedures work similarly except for the fact that one receives GUID of the data path as a parameter, whereas the other procedures accept the path name. These two procedures reside in the package catalog within SSISDB system database.
A typical code snippet to add a datatap can be given as follows:
@execution_id=@execution_id OUTPUT, @folder_name=N’NewProject’, @project_name=N’Integration Services
@object_type=50, @parameter_name=N’LOGGING_LEVEL’, @parameter_value=@var0
= ‘\Package\DFT –
Cleanse and transfer source data’,
= ‘Paths[CON – Identify
= ‘Paths[CON – Identify
The entire code snippet can be easily created by expanding the SSIDB catalog, to which project is deployed, and choosing “Execute” in the package dialog and selecting the “Script” option.
Add the two calls to the data tap procedures in between the generated script before the start of the execution step.
The parameters include:
- Execution ID generated for the package execution
- Task path name within the package
- Data path ID string for the data flow task path
- Data file name to save the path results
The data path ID string can be obtained by double clicking on the data flow path and viewing the properties window.
The data flow task GUID and path can be obtained from data flow engine properties of the corresponding data flow task:
The last parameter represents the file to which the result has to be captured. The file gets stored in the location C:\Program Files (x86)\Microsoft SQL Server\110\DTS\DataDumps in the server by default. The file will have the result of the data path stored.
Comparing the file contents with the output of data flow paths shown above reveals that row counts are the same (7 in adjusted path and 2 records in correct) in both the cases, which indicates data taps capturing correct information at runtime.