Data Flow Taps

The catalog.add_data_tap stored procedure allows you to add a data tap on the output of a component in a package data flow and direct the output from the data tap to an external file. For example, the following Transact-SQL statements create an execution and then create a data tap on the output of the component, SRC DimDCVendor, in the data flow task, DFT Load DC Vendor. The tapped data is stored in the DCVendorOutput.csv file in the DataDumps folder (<drive>:\Program Files\Microsoft SQL Server\110\DTS\DataDumps).

Use SSISDB
Declare @execution_id bigint
EXEC [catalog].[create_execution] @package_name=N'Package.dtsx', @folder_name=N'DC Project', 
    @project_name=N'DC Vendor', @execution_id=@execution_id OUTPUT
EXEC [catalog].add_data_tap   @execution_id, '\Package\DFT Load DC Vendor',
    'Paths[SRC DimDCVendor.OLE DB Source Output]', 'DCVendorOutput.csv'
EXEC [catalog].[start_execution] @execution_id

The catalog.add_data_tap_by_guid stored procedure allows you to add a data tap to a specific data flow path in a package data flow.

You can use the catalog.execution_data_taps view to display the data taps for an execution, and you can remove a data tap by using the catalog.remove_data_tap stored procedure.