SSIS Automation - Persist 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 -- -- Generate a TVC that we can use to load the parameter values into the TargetSSISPackageParameter table -- SCRIPT: GENERATE_PACKAGE_PARAMETER_SETTINGS -- ==> SET QUERY RESULTS TO TEXT <== -- DECLARE    @SSIS_PACKAGE_NAME NVARCHAR(256) = N'MA_CONTRACT_DATA.dtsx' , @TARGET_NAME NVARCHAR(256) = N'H9999'; ;WITH CTE AS ( SELECT    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) ROW_NUM ,  [object_name] ,  parameter_name FROM SSISDB.catalog.object_parameters WHERE object_name = @SSIS_PACKAGE_NAME AND required = 1 AND [object_type] = 30 -- package parameter ) SELECT   CASE WHEN ROW_NUM > 1 THEN ',' ELSE ' ' END +   '( ' +    '''' + @TARGET_NAME + '''' +    ', ' + '''' + [object_name] + '''' +   ', ' + '''' + parameter_name + '''' +   ', ' + '''' + 'PARAMETER_VALUE_GOES_HERE' + '''' +   ')' FROM CTE -- -- Load package settings for TargetName -- SCRIPT: MERGE_PACKAGE_PARAMETER_SETTINGS -- DECLARE @LAST_UPDATED DATETIME = GETDATE();     ;WITH cte_source AS ( SELECT src.TargetName, src.SSISPackageName, src.ParameterName, src.ParameterValue  FROM (VALUES -- INSERT TVF from GENERATE_PACKAGE_PARAMETER_SETTINGS script  ( 'H9999', 'MA_CONTRACT_DATA.dtsx', 'CONTRACT_NUMBER', CONVERT(SQL_VARIANT, N'H9999')) ,( 'H9999', 'MA_CONTRACT_DATA.dtsx', 'END_DATE', CONVERT(SQL_VARIANT, CONVERT(DATE, N'20161231'))) ,( 'H9999', 'MA_CONTRACT_DATA.dtsx', 'OUTPUT_UNC_PATH', CONVERT(SQL_VARIANT, N'\\OUTBOUND')) ,( 'H9999', 'MA_CONTRACT_DATA.dtsx', 'START_DATE', CONVERT(SQL_VARIANT, CONVERT(DATE, N'20160101'))) --  )  AS src(TargetName, SSISPackageName, ParameterName, ParameterValue)     )     MERGE dbo.TargetSSISPackageParameter AS tgt     USING (SELECT * FROM cte_source) AS src ON (     tgt.TargetName = src.TargetName  AND tgt.SSISPackageName = src.SSISPackageName  AND tgt.ParameterName = src.ParameterName )     WHEN MATCHED THEN     UPDATE SET               ParameterValue=src.ParameterValue , LastUpdated=@LAST_UPDATED     WHEN NOT MATCHED THEN             INSERT ( TargetName   , SSISPackageName   , ParameterName   , ParameterValue   , [LastUpdated] )             VALUES ( src.TargetName   , src.SSISPackageName   , src.ParameterName   , src.ParameterValue   , @LAST_UPDATED ); -- -- Show package parameter settings for TargetName and SSISPackageName -- SELECT *  FROM dbo.TargetSSISPackageParameter WHERE TargetName=N'H9999' AND SSISPackageName=N'MA_CONTRACT_DATA.dtsx'; ReplyForward