modifiy connection manager information from SSIS Catalog from sql

Question

Is there a way, to update the connection manager information from the ssis catalog after deploying with an sql code ?

I'd like to deploy the project without sensitive data first :

• exec catalog.deploy_project ..
• then add username and password to the SSIS Catalog Project via SQL...

Answer 1

Is there a way, to update the connection manager information from the ssis catalog after deploying with an sql code ?

It is possible since all changed in SSISDB catalog are via stored procedure.

This is a SQL script generated by SSMS during connection string change:

DECLARE @var SQL_VARIANT
= N'Data Source=ServerName;Initial Catalog=dbName;
Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;';

EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type = 20,
                                                     @parameter_name = N'_ConnectionStringParam',
                                                     @object_name = N'ProjectName',
                                                     @folder_name = N'FolderName',
                                                     @project_name = N'ProjectName',
                                                     @value_type = V,
                                                     @parameter_value = @var;

References:

• catalog.set_object_parameter_value (SSISDB Database)
• How can I automate setting SSIS Project Parameters in SSIS 2012?

Answer 2

Since you're asking how to change the username and password once a package is deployed, I assume you're already familiar with the SSIS Catalog, if not the documentation outlines this further. To set the username and passwords via T-SQL use environment variables, with the variable holding the password marked as sensitive. The SSISDB.CATALOG.SET_ENVIRONMENT_VARIABLE_VALUE stored procedure is used to update the values of environment variables, including those marked as sensitive, and can be used as follows.

• Start off by creating an environment. To do this, right-click SSISDB and create a new folder. Then in this folder, right-click and create a new environment.
• After this right-click the environment and select Properties then go to the Variables pane. Create a string variable for both the username and password, with the variable storing the password set as Sensitive.
• Next, right click the package or project depending on the scope that the connection manager was defined in, and press Configure and go to the References page. On this add the newly created environment with the Add button.
• Still in the properties window of the project/package, go to the Parameters page and then Connection Managers tab. Find the connection manager that will use the environment variables, right-click the ellipsis on both the username and password properties, change the radio button to "Use Environment Variable", and choose the corresponding variables.
• Before executing the package, run the SSISDB.CATALOG.SET_ENVIRONMENT_VARIABLE_VALUE stored procedure to update the environment variables. An example of this follows.
• If this package is executed using T-SQL from a SQL Agent job or another method, linked the environment reference to it as done below.

Example:

DECLARE @usernameVar SQL_VARIANT = N'UsernameValue'
EXEC SSISDB.[CATALOG].SET_ENVIRONMENT_VARIABLE_VALUE @variable_name=N'Username', 
@environment_name=N'Environment Name', @folder_name=N'Environment folder', @value=@usernameVar

DECLARE @passwordVar SQL_VARIANT = N'PasswordValue'
EXEC SSISDB.[CATALOG].SET_ENVIRONMENT_VARIABLE_VALUE @variable_name=N'Password', 
@environment_name=N'Environment Name', @folder_name=N'Environment folder', @value=@passwordVar

--make sure environment mapped with @reference_id
DECLARE @execution_id bigint
EXEC SSISDB.[CATALOG].CREATE_EXECUTION @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'Project Folder', @project_name=N'PackageProject', @use32bitruntime=False, @reference_id=99

DECLARE @var0 smallint = 1
EXEC SSISDB.[CATALOG].SET_EXECUTION_PARAMETER_VALUE @execution_id,  @object_type=50, 
@parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0 

--execute package
EXEC SSISDB.[CATALOG].START_EXECUTION @execution_id