Data taps in SSIS

• Data taps are a new feature in SQL Server 2012 for troubleshooting package execution. 

• You can capture a copy of the data from a specific data path in your data flow tasks in a comma-delimited file at run time.

• Much like data viewers in design time.

• Data taps enable you to analyze data in a production environment.

Information needed for Data tap

Folder Name : The Name of the folder in SSIS server.

Project Name : The name of the SSIS project that holds the package

Package Name : The package name.

Data Flow Path ID : The value of the IdentificationString property of the data flow path.

Data flow task path : The path of the data flow task within the package.This can be read from the PackagePath property of the selected data flow task in SSDT.

The data files will be created in installation drive.The path is 'C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps'.
I have specified the data file name as output.csv

 









We need to execute three below stored procedures aganist SSISDB
1.catalog.create_execution
  Create an execution instance of a package.

2.catalog.add_data_tap
  Add a data tap.

3.catalog.start_execution
  Start the execution instance of the package.

Sample script

Declare @execid bigint

EXEC [SSISDB].[catalog].[create_execution]
@folder_name=N'Advance SSIS',
@project_name=N'Advanced SSIS',
@package_name=N'ForEachLoop Container.dtsx',
@execution_id=@execid OUTPUT

EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid,
@task_package_path = '\Package\Load Flat Files',
@dataflow_path_id_string = 'Paths[File Extract.Flat File Source Output]',
@data_filename = 'output.txt'

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