Copying parameters and connection managers from one SSIS Package to another

Question

I have an SSIS package with some parameters and connection managers that are parametrized using environment variables

What I need to do is to come that ssis package, change the name and few other things and deploy it as a different package. After the new package is deployed I would like to generate a script that allows me to copy all the parametrizations from the old package.

I was able to create this code:

SELECT distinct
 v.[name], v.[type], v.[value],
    Script = 'EXEC [SSISDB].[catalog].[set_object_parameter_value]
        @object_type=30 
      , @parameter_name= N''' + CONVERT(NVARCHAR(500), v.name) + '''
      , @object_name= N''' +pk.name+ '''
      , @folder_name= N''' +f.name + '''
      , @project_name= N''' +pr.name + '''
      , @value_type= R
      , @parameter_value= N''' + CONVERT(NVARCHAR(500), v.name) + ''';
'
FROM [SSISDB].[catalog].[environments]          e
JOIN [SSISDB].[catalog].[folders]               f ON f.[folder_id]      = e.[folder_id]
JOIN [SSISDB].[catalog].[environment_variables] v ON e.[environment_id] = v.[environment_id]
JOIN [SSISDB].[catalog].projects pr ON  pr.folder_id = f.folder_id
JOIN [SSISDB].[catalog].packages pk ON pk.project_id = pr.project_id

WHERE 
     e.[name] = N'PreProd'
     AND v.NAME like '%MSDB%'
     AND pr.name = 'myProject'
     AND pk.name = 'myPackage.dtsx'

However when i try to execute the resulting script it get this message:

Cannot find the parameter 'MSDBConnection.ConnectionString' because it does not exist.

What I am doing wrong?

score 1 · Accepted Answer · answered Oct 29 '21 at 09:37

this will even generate the script to copy the mapping between parameters and environment variables:

-- VIEW THE MAPPING BETWEEN PACKAGE PARAMETERS AND ENVIRONMENT VARIABLES

USE SSISDB
GO 

WITH mapping AS (   
SELECT er.environment_folder_name,
       er.environment_name,
       p.name AS Project_Name,
       er.reference_type,
       ev.variable_id,
       ev.name AS Environment_Variable_Name,
       ev.sensitive ev_sensitive,
       ev.type,
       ev.value,
       op.parameter_id,
       op.object_type,
       op.object_name,
       op.parameter_name,
       op.data_type AS Parameter_Data_Type,
       op.required,
       op.sensitive op_sensitive,
       op.default_value,
       op.design_default_value,
       op.value_set,
       op.value_type
FROM catalog.environment_references er
    INNER JOIN catalog.projects p
        ON er.project_id = p.project_id
    INNER JOIN catalog.environments e
        ON er.environment_name = er.environment_name
    INNER JOIN catalog.environment_variables ev
        ON e.environment_id = ev.environment_id
    INNER JOIN catalog.object_parameters op
        ON op.project_id = p.project_id
           AND op.value_type = N'R'
           AND op.referenced_variable_name = ev.name
)

SELECT DISTINCT 
environment_folder_name,
Project_Name,
OBJECT_NAME,
parameter_name,
Environment_Variable_Name,
 Script = 'EXEC [SSISDB].[catalog].[set_object_parameter_value]
        @object_type=30 
      , @parameter_name= N''' + CONVERT(NVARCHAR(500), parameter_name) + '''
      , @object_name= N''' + OBJECT_NAME + '''   
      , @folder_name= N''' + environment_folder_name + '''
      , @project_name= N''' + Project_Name + '''
      , @value_type= R
      , @parameter_value= N''' + CONVERT(NVARCHAR(500), Environment_Variable_Name) + ''';
'


FROM mapping
WHERE OBJECT_NAME = 'myPackage.dtsx'
--AND parameter_name = 'myParameter'
ORDER BY 1,2,3,4,5

Copying parameters and connection managers from one SSIS Package to another

1 Answers1