When you script SSIS environment variables it gives you all the SP calls you need, but all the values are hardcoded in each line. This isn’t as usable as I’d like, so here I show you how to use regex to reformat the code produced by SSMS so it’s more readable, and easier to extend.
Each param will go from this:
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name=N’Date_Month’, @object_name=N’MonthlyMarketing’, @folder_name=N’MarketingReport’, @project_name=N’MonthlyMarketing’, @value_type=R, @parameter_value=N’Date_Month’
To this:
——————————————————————————
————————BEGIN ‘Date_Month’—————————————
——————————————————————————
SET @object_type = 20;
SET @parameter_name = ‘Date_Month’;
SET @object_name = ‘MonthlyMarketing’;
SET @folder_name = ‘MarketingReport’;
SET @project_name = ‘MonthlyMarketing’;
SET @value_type = R;
SET @parameter_value = ‘Date_Month’;
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type = @object_type, @parameter_name = @parameter_name, @object_name = @object_name, @folder_name = @folder_name, @project_name = @project_name, @value_type = @value_type, @parameter_value = @parameter_value;
——————————————————————————
————————END ‘Date_Month’—————————————
——————————————————————————
Loading...