CODEWITHSUNDEEP
ssis
planetmatt
planetmatt

Reputation: 418

Updating SSISDB Environment Variables

I know it is best practise to use the sproc, [SSISDB].[catalog].[set_environment_variable_value] to update existing Environment Variables.

However, can somebody explain why I can't just run UPDATES on the [SSISDB].[internal].[envrionment_variables] table?

What's the risk here and how is it any different from updating the old SSIS_Configrations table when using the old package deployment method?

It's faster and easier to mass update variables with a single UPDATE, than create a CURSOR to loop through and run the SPROC.

Upvotes: 0

Views: 553

Answers (1)

WojtekG
WojtekG

Reputation: 59

You can review the code of the set_environment_variable_value procedure and notice that it handles many scenarios - such as different datatypes, encrypting sensitive parameters, rollbacks, etc.

Another reason : SSIS catalog structure and envrionment_variables can look different in the newer versions, so your single update statement may not work after an upgrade.

Upvotes: 2

Related Questions