SSISDB6: Using Data Taps

Data streaming is a data Viewer similar to a dataflow path, which can be used to import data into a file for easy viewing of data in a data flow. Data flow offload must be implemented through code.

To add data taps, the instance of the execution must is 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.

Declare @execution_id bigintEXEC [SSISDB].[Catalog].[create_execution]     @package_name=N'package.dtsx',     @execution_id=@execution_idOUTPUT,@folder_name=N'Demo',     @project_name=N'Isstudy',     @use32bitruntime=False,@reference_id=1Select @execution_idDECLARE @var0 smallint = 1EXEC [SSISDB].[Catalog].[Set_execution_parameter_value]         @execution_id,          @object_type= -,         @parameter_name=N'Logging_level',         @parameter_value=@var0execSSISDB.catalog.add_data_tap@execution_id=@execution_id,    @task_package_path=N'\package\data Flow Task',    @dataflow_path_id_string=N'paths[ole DB source.ole db Source Output]',    @data_filename=N'Datatap.csv'EXEC [SSISDB].[Catalog].[start_execution] @execution_idGO

@data_filename: is the file name where the data is stored, the file path is fixed, cannot be changed, and typically SQL Server creates a data splitter file in the directory's folder

@task_package_path: Is the PackagePath property value of the data Flow task

@dataflow_path_id_string: Is the Identificationstring property value of the data Flow path

Reference Documentation:

Catalog.add_data_tap

Add_data_tap[@execution_id =]execution_id[@task_package_path =]Task_package_path[@dataflow_path_id_string =]dataflow_path_id_string[@data_filename =]Data_filename[@max_rows =]Max_Rows[@data_tap_id =]Data_tap_idoutput

[@task_package_path =] Task_package_path

The package path for the data Flow task. The PackagePath property for the Data Flow task specifies the path. The path is case-sensitive. To locate the package path, in SQL Server data Tools Right-click the data Flow task, and then click Properties. The PackagePath property appears in the Properties window.

The Task_package_path is a nvarchar (max).

[@dataflow_path_id_string =] dataflow_path_id_string

The identification string for the data flow path. A path connects the data flow components. The Identificationstring property for the path specifies the string.

To locate the identification string, in SQL Server data Tools Right-click the path between the data flow components and th En click Properties. The identificationstring property appears in the Properties window.

The dataflow_path_id_string is a nvarchar (4000).

[@data_filename =] Data_filename

The name of the file that stores the tapped data. If The data Flow task executes inside a Foreach Loop or a For loop container, separate files store tapped data for each it Eration of the loop. Each file was prefixed with a number, that corresponds to an iteration.

By default, the file was stored in the <Drive >:\program Files\Microsoft SQL Server\110\dts\datadumps folder.

The data_filename is a nvarchar (4000).

Remarks

To add data taps, the instance of the execution must is 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 a execution by calling Catalog.create_execution (SSISDB Database).

The following is considerations for the Add_data_tap stored procedure.

• If an execution contains a the parent package and one or more child packages, you need to add a data taps for each of the You want to tap data for.

• If A package contains + than one data Flow task with the same name, the Task_package_path uniquely identifies the data Flow task that contains the component output, which is tapped.

• When you add data tap, it's not a validated before the package is run.

• It's recommended that's the number of rows that's captured during the data tap, to avoid generating large data Files. If the machine on which the stored procedure is executed and runs out of storage space for the data files, the package stops Running and an error message are written to a log.

• Running the Add_data_tap stored procedure impacts the performance of the package. It is recommended so you run the stored procedure only to troubleshoot data issues.

• To access the file is stores the tapped data, you must is an administrator on the machine on which the stored procedure is run. You must also is the user who started the execution this contains the package with the data tap.

SSISDB6: Using Data Taps