Facebook Twitter LinkedIn YouTube E-mail
Home Microsoft SQL Server 2012 Runtime Debugging Using Data Taps in SSIS 2012
formats

Runtime Debugging Using Data Taps in SSIS 2012

Share

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:

Declare
@execution_id bigint

 

EXEC
[SSISDB].[catalog].[create_execution]
@package_name=N’DQSPackage.dtsx’,
@execution_id=@execution_id OUTPUT, @folder_name=N’NewProject’, @project_name=N’Integration Services
Project1′
,
@use32bitruntime
=True,
@reference_id=Null

Select
@execution_id

 

DECLARE
@var0 smallint
= 1

 

EXEC
[SSISDB].[catalog].[set_execution_parameter_value]@execution_id, 
@object_type=50, @parameter_name=N’LOGGING_LEVEL’, @parameter_value=@var0

 

EXEC
[SSISDB].[catalog].add_data_tap
@execution_id =
@execution_id,

@task_package_path
= ‘\Package\DFT –
Cleanse and transfer source data’
,

@dataflow_path_id_string
= ‘Paths[CON – Identify
Cleansed Data.Correct]’
,

@data_filename
= ‘correctoutput.txt’

EXEC
[SSISDB].[catalog].add_data_tap_by_guid
@execution_id =
@execution_id,

@dataflow_task_guid
=
‘{35E498E6-CCD1-4AC0-A236-03153DB645FC}’
,

@dataflow_path_id_string
= ‘Paths[CON – Identify
Cleansed Data.Adjusted]’
,

@data_filename
= ‘adjustedoutput.txt’

 

EXEC
[SSISDB].[catalog].[start_execution]
@execution_id

GO

 

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.

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
Comments Off on Runtime Debugging Using Data Taps in SSIS 2012  comments