data_tap_execution.sql

USE SSISDB
GO

/* 
	Testing SSIS data taps

	Output directory: "C:\Program Files\Microsoft SQL Server\130\DTS\DataDumps"
*/

-- Create the execution instance
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'070 Data Taps.dtsx', @execution_id=@execution_id OUTPUT
, @folder_name=N'TestingSSISPackages', @project_name=N'Testing SSIS Packages', @use32bitruntime=False, @reference_id=NULL


-- Set up system parameters
DECLARE @var0 SMALLINT = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
DECLARE @var1 SMALLINT = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@var1


-- Add the data tap to this execution instance
EXEC [SSISDB].[catalog].[add_data_tap] @execution_id = @execution_id					-- Execution_Id from the call to create_execution 
	, @task_package_path = '\Package\DFT Load Games'									-- Path to the data flow task
	, @dataflow_path_id_string = 'Paths[LKUP Validate Game.Lookup No Match Output]'		-- Output path name
	, @data_filename = 'Failed.csv'														-- Name of the file that stores the tapped data.
--	, @max_rows = 100000																-- Max rows to capture


-- Finally, execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO