An interesting feature that is new with SSIS 2012 is data taps. Imagine you are troubleshooting an issue in your data flow. If you are like me, you enable data viewers to help you troubleshoot. Maybe you need to check the results of a derived column or see if a look up value was found. A data tap works like a data viewer except that the data is exported to a file and the package that you are running has been deployed to the SSIS catalog.

The command to add a data tap is part of the new T-SQL syntax for running SSIS packages. To get started learning about data taps, make sure you are using SQL Server 2012 or 2014 and have deployed your package using the new Project Deployment Model to the SSIS catalog. I created a project with one package that just exports a table to a file. Here’s how my Data Tap project looks in SSMS.

To use a Data Tap, you have to write a script to run the package. Right-click on the package name and select Execute to bring up the Execute Package dialog box. Add any parameters and make any other configuration changes you need to make and click Script. My sample package doesn’t have any parameters or anything to change.

The script generated is the code required to execute the package from within T-SQL (no xp_cmdshell required!).

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N’Package.dtsx’,
@execution_id=@execution_id OUTPUT, @folder_name=N’DataTap Example’,
@project_name=N’DataTap’, @use32bitruntime=False, @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].[start_execution] @execution_id
GO

If you have added parameter values or made other configuration changes, you will see additional lines of code setting those up.

You have your choice of two procs to add the Data Tap: add_data_tap_by_guid and add_data_tap. To supply the values for all of the parameters, you’ll need the package in design mode available to you. Here is the add_data_tap command we’ll insert before the start_execution line.

EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execution_id,
@task_package_path = ‘\Package\DFT_ExportCurrency’,
@dataflow_path_id_string = ‘Paths[OLE_SRC_Currency.OLE DB Source Output]’,
@data_filename = ‘output.txt’

Except for @execution_id, how in the world did I come up with the parameter values for the data tap? Go back to the package in VS and take a look at the properties of the Data Flow Task – not the Data Flow layer, the F4 properties. Find the PackagePath property. That is the @task_package_path value as shown here.

Now double-click the Data Flow Task and find the data flow path where you would like to see the data exported. Take a look at the IdentificationString property. That is the @dataflow_path_id_string value. Finally, you need a name for the file.

You may wonder where the file actually ends up since only a file name is needed. If everything works as expected, the file will end up at \Microsoft SQL Server\110\DTS\DataDumps or \Microsoft SQL Server\120\DTS\DataDumps.

There is also an optional parameter to limit the number of rows exported with the @max_rows parameter.

Next week I’ll post an article explaining why you might want to use the add_data_tap_by_guid function.

I’m having a great time exploring all the 2012 SSIS changes. How about you?