<\/em>
Problem<\/strong>
When we develop any SSIS package, to view the data passing through the pipeline we add Dataviewer, but once we deploy a package there was no built-in support for data tapping to analyze the data for troubleshooting. In SQL2012 and later versions this can be done using data taps.<\/p>\n\n\n\n
What is a Data Tap? How to add a Data Tap? After you deploy the package to the server, you need to execute T-SQL scripts against the SSISDB database to add data taps before executing the package.<\/p>\n\n\n\n 1. Create an execution instance of a package by using the catalog.create_execution (SSISDB Database) stored procedure. Instead of typing the SQL statements, you can generate the execute package script by performing the following steps: In our example following is the script generated<\/em><\/strong><\/p>\n\n\n\n Declare @execution_id bigint EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N\u2019LOGGING_LEVEL\u2019, @parameter_value=@var0<\/p>\n\n\n\n EXEC [SSISDB].[catalog].[start_execution] @execution_id There are 2 options for adding the datatap.<\/strong><\/p>\n\n\n\n 1. add_data_tap<\/strong><\/p>\n\n\n\n To supply the values for all of the parameters, you\u2019ll need the package in design mode available to you. Here is the add_data_tap command we\u2019ll insert before the start_execution line.<\/p>\n\n\n\n EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execution_id, @task_Pacakge_path is obtained from the DFT property name PackagePath.<\/p>\n\n\n @Dataflow_path_id_string value is obtained from the propertyname IdentificationString.<\/p>\n\n\n The datatap output file will get saved in the folder path \\Microsoft SQL Server\\110\\DTS\\DataDumps.<\/p>\n\n\n\n 2. add_data_tap_by_guid<\/strong><\/p>\n\n\n\n This function takes the task ID instead of the task package path. The command in my example is<\/p>\n\n\n EXEC [SSISDB].[catalog].add_data_tap_by_guid @execution_id = @execution_id, Listing all the Datataps<\/strong><\/p>\n\n\n\n You can also list all the data taps by using the catalog.execution_data_taps view. Performance Consideration<\/strong><\/p>\n\n\n\n Enabling verbose logging level and adding data taps increase the I\/O operations performed by your data integration solution. Hence, add data taps should be used only for troubleshooting purposes and when the data volume is less.<\/p>\n\n\n\n Conclusion:<\/strong><\/p>\n\n\n\n Troubleshooting data issues can get hairy at times and data taps are an extra tool in our belt that can help in cases where logging (which has also improved a lot in SSIS 2012) and dumps may not give us the required level of information.<\/p>\n","protected":false},"excerpt":{"rendered":"
<\/strong>A Data Tap is an output of the data from one of the data flow paths in your package. Data Taps are like Data Viewers. The difference is that a Data Tap exports the data to a file from an already deployed package.<\/p>\n\n\n\n
<\/strong>To get started, make sure you are using SQL Server 2012 or Later version and have deployed your package using the new Project Deployment Model.<\/p>\n\n\n![\"\"](\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/add-a-data-trap.webp\")
<\/figure><\/div>\n\n\n
2. Add a data tap by using either catalog.add_data_tap or catalog.add_data_tap_by_guid stored procedure.
3. Start the execution instance of the package by using the catalog.start_execution (SSISDB Database).<\/p>\n\n\n\n
1. Right-click Package.dtsx and click Execute.
2. Click Script toolbar button to generate the script.
3. Now, add the add_data_tap statement before the start_execution call.<\/p>\n\n\n\n
EXEC [SSISDB].[catalog].[create_execution] @package_name=N\u2019Package.dtsx\u2019,
@execution_id=@execution_id OUTPUT, @folder_name=N\u2019DataTap Example\u2019,
@project_name=N\u2019DataTap\u2019, @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1<\/p>\n\n\n\n
GO<\/p>\n\n\n\n
@task_package_path = \u2018\\Package\\DFT_ExportCurrency\u2019,
@dataflow_path_id_string = \u2018Paths[OLE_SRC_Currency.OLE DB Source Output]\u2019,
@data_filename = \u2018output.txt\u2019<\/p>\n\n\n\n![\"\"](\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/add-a-data-trap1.png.webp\")
<\/figure><\/div>\n\n\n![\"\"](\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/add-a-data-trap2.png.webp\")
<\/figure><\/div>\n\n\n![\"\"](\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/add-a-data-trap3.webp\")
<\/figure><\/div>\n\n\n
@dataflow_task_guid = \u2018{0210C9CC-635B-4038-AFC4-54220909F4F3}\u2019,
@dataflow_path_id_string = \u2018Paths[OLE_SRC_Currency.OLE DB Source Output]\u2019,
@data_filename = \u2018output.txt\u2019<\/p>\n\n\n\n
Select * from [SSISDB].[catalog].execution_data_taps where execution_id=@execid<\/p>\n\n\n\n