Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Adds a data tap on the output of a component in a package data flow, for an instance of the execution.
In the following example, a data tap is created on the data flow path, 'Paths[OLE DB Source.OLE DB Source Output], in the data flow task, \Package\Data Flow Task. The tapped data is stored in the output0.txt file in the DataDumps folder (<drive>:\Program Files\Microsoft SQL Server\130\DTS\DataDumps).
Declare @execution_id bigint Exec SSISDB.Catalog.create_execution @folder_name='Packages',@project_name='SSISPackages', @package_name='Package.dtsx',@reference_id=Null, @use32bitruntime=False, @execution_id=@execution_id OUTPUT Exec SSISDB.Catalog.set_execution_parameter_value @execution_id,50, 'LOGGING_LEVEL', 0 Exec SSISDB.Catalog.add_data_tap @execution_id, @task_package_path='\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[OLE DB Source.OLE DB Source Output]', @data_filename = 'output0.txt' Exec SSISDB.Catalog.start_execution @execution_id
To add data taps, the instance of the execution must be in the created state (a value of 1 in the status column of the catalog.operations (SSISDB Database)view) . The state value changes once you run the execution. You can create an execution by calling catalog.create_execution (SSISDB Database).
The following are considerations for the add_data_tap stored procedure.
If an execution contains a parent package and one or more child packages, you need to add a data tap for each package that you want to tap data for.
If a package contains more than one data flow task with the same name, the task_package_path uniquely identifies the data flow task that contains the component output that is tapped.
When you add data tap, it is not validated before the package is run.
It is recommended that you limit the number of rows that are captured during the data tap, to avoid generating large data files. If the machine on which the stored procedure is executed, runs out of storage space for the data files, the package stops running and an error message is written to a log.
Running the add_data_tap stored procedure impacts the performance of the package. It is recommended that you run the stored procedure only to troubleshoot data issues.
To access the file that stores the tapped data, you must be an administrator on the machine on which the stored procedure is run. You must also be the user who started the execution that contains the package with the data tap.