Reputation: 734
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 :
Upvotes: 3
Views: 2845
Reputation: 4810
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.
SSISDB.CATALOG.SET_ENVIRONMENT_VARIABLE_VALUE
stored procedure to update the environment variables. An example of this follows.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
Upvotes: 1
Reputation: 5940
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:
Upvotes: 2