sql-serverssisssis-connection-manager

Copying parameters and connection managers from one SSIS Package to another


I have an SSIS package with some parameters and connection managers that are parametrized using environment variablesenter image description here enter image description here

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?


Solution

  • 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