gistfile1.sql

USE [SSISDB]

-- STEP 1: Create an execution instance of the package you want to tap data from
DECLARE	@return_value int,
		@execution_id bigint
EXEC	@return_value = [catalog].[create_execution]
		@folder_name = N'SSISDemo', --Folder in SSIS server where package is deployed
		@project_name = N'SSIS20112 Demo1', --Project in SSIS server where package is deployed
		@package_name = N'Package.dtsx', --Package being executed
		@execution_id = @execution_id OUTPUT

SELECT	@execution_id as N'@execution_id'
SELECT	'Return Value Create Execution' = @return_value


-- STEP 2: Add data tap to data flow path you want to tap data from
-- This example is adding a data tap to each of the 2 paths in the data flow

DECLARE	@data_tap_id bigint
EXEC	@return_value = [catalog].[add_data_tap]
		@execution_id = @execution_id,
		@task_package_path = N'\Package\Load Product',
		@dataflow_path_id_string = N'Paths[OLESRC Get Products from source.OLE DB Source Output]',
		@data_filename = N'SourceDataTap.csv',
		@data_tap_id = @data_tap_id OUTPUT

SELECT	@data_tap_id as N'@data_tap_id'
SELECT	'Return Value Add data tap' = @return_value


EXEC	@return_value = [catalog].[add_data_tap]
		@execution_id = @execution_id,
		@task_package_path = N'\Package\Load Product',
		@dataflow_path_id_string = N'Paths[Add Product Sufix.Derived Column Output]',
		@data_filename = N'DerivedColumnDataTap.csv',
		@data_tap_id = @data_tap_id OUTPUT

SELECT	@data_tap_id as N'@data_tap_id'
SELECT	'Return Value Add data tap' = @return_value



-- Step 3: Just run the package
EXEC	@return_value = [catalog].[start_execution]
		@execution_id 

SELECT	'Return Value start execution' = @return_value
GO