Execute SSIS Package - Specify All Required package parameters

 --

-- Show the required package parameters for a package deployed to the catalog

--

SELECT * FROM SSISDB.catalog.object_parameters

WHERE object_name = N'MA_CONTRACT_DATA.dtsx'

AND required=1

AND object_type=30; -- package parameter

GO

-- execute the package; specify all required package parameters

DECLARE 

  @CUSTOM_PARAMETER_TABLE dbo.ReportParameterType;

INSERT @CUSTOM_PARAMETER_TABLE([Name], [Value])

SELECT p.[Name], p.[Value]

FROM (

VALUES 

  (N'CONTRACT_NUMBER', CONVERT(SQL_VARIANT, N'H9999'))

, (N'START_DATE', CONVERT(SQL_VARIANT, CONVERT(DATE, N'20160101')))

, (N'END_DATE',   CONVERT(SQL_VARIANT, CONVERT(DATE, N'20161231')))

, (N'OUTPUT_UNC_PATH', CONVERT(SQL_VARIANT, N'\\OUTBOUND'))

) AS p([Name], [Value]);

EXEC [dbo].[ExecuteAutomatedSSISPackage]

@TARGET_NAME = N'CUSTOM'

, @SSIS_PACKAGE_NAME = N'MA_CONTRACT_DATA.dtsx'

, @ENVIRONMENT_NAME = N'DEV'

, @CUSTOM_PARAMETER_TABLE = @CUSTOM_PARAMETER_TABLE;