SSIS 2012 Data taps

Case
I have a (very basic) package and want to add data taps to it in the Integration Services Catalogs.

My package adding colors to a table

Solution
Datataps are the 'dataviewers' for packages within the Integration Services Catalogs, but their output is to a file instead of to the screen. They can be added with the stored procedures from the SSISDB.

My package in the SSIS Catalog

1) Execution
We first have to create an execution and we need its execution id in the next stored procedure calls. The Folder, Project and Packagename can be found in the picture above.

-- Create a variable to store the ID of the package execution
DECLARE @execution_id bigint

-- Create a package execution and fill the variable
EXECUTE [SSISDB].[catalog].[create_execution] 
  @folder_name = 'ilionx'
  ,@project_name = 'DataTap'
  ,@package_name = 'DimColors.dtsx' 
  ,@reference_id = null
  ,@use32bitruntime = false
  ,@execution_id = @execution_id OUTPUT

-- Add some optional parameters like Verbose logging
EXECUTE [SSISDB].[catalog].[set_execution_parameter_value]
  @execution_id = @execution_id
  ,@object_type=50
  ,@parameter_name=N'LOGGING_LEVEL'
  ,@parameter_value=3 -- Verbose

2) Data taps
Now we have an execution, we can add data taps to it. For this we need to know the PackagePath or GUID of the Data Flow Task you want to tap.


The (GU)ID and PackagePath of the Data Flow Task.

And we need to know the IdentificationString of the Data Flow Path within the Data Flow Task.

Data Flow Path properties

I will add one data tap with the PackagePath and the other on the next Data Flow Path with the GUID.

-- Create a data type with the data flow PackagePath 
EXECUTE [SSISDB].[catalog].[add_data_tap]
  @execution_id = @execution_id
  ,@task_package_path = '\Package\Add Colors'
  ,@dataflow_path_id_string = 'Paths[SRC - Colors.Flat File Source Output]'
  ,@data_filename = 'ssisjoost1.txt'
  ,@max_rows = 10

-- Create a data type with the data flow ID
EXECUTE [SSISDB].[catalog].add_data_tap_by_guid
  @execution_id = @execution_id
  ,@dataflow_task_guid = '{9DE67956-E158-4264-AA98-F9C07A7C7731}'
  ,@dataflow_path_id_string = 'Paths[DER - Uppercase.Derived Column Output]'
  ,@data_filename = 'ssisjoost2.txt'
  ,@max_rows = 10

3) Execute
The last step is to execute the created execution and watch the output folder (C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps) for new files.

-- Execute the created execution
EXECUTE [SSISDB].[catalog].[start_execution]
  @execution_id = @execution_id

Note: For steps 1 and 3 of above you can also use the Script button. Then you only have to add the code from step 2. See steps in this picture: