Scripting Environments in SSIS

Question

Post reply

Scripting Environments in SSIS

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

Thanks for creating this, just saved me a bunch of time creating a new SSIS server.

As the last poster mentioned the string data is not showing up like the example:

@String sql_variant= N'I am a string'

They come across as simply nvarchar which chops them off to 1 character. I just did a find replace on the final script, that took care of it.

Thanks again!

Philippe Addelia Grasshopper Points: 20 More actions · Answer 2

Hi,

I really like this procedure. I changed this line to add the length of the variable (4000).

SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type + '(4000) = N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''

This reply was modified 5 years, 4 months ago by Philippe Addelia.

Philippe

john.killeen Newbie Points: 1 More actions · Answer 3

Great script, I added a few lines to remove the existing environment if it exists before adding it in. I had to upgrade a few projects and removed / added a lot of environment variables. So when I deployed to SAT / UAT / PROD I can delete the existing environment before re-adding it with the new/updated variables

After the line containing - SET @sql += 'BEGIN TRANSACTION' + @cr;

I added this

SET @sql += @tab + 'IF EXISTS (SELECT 1

FROM [SSISDB].[catalog].[environments] env

INNER JOIN [SSISDB].[catalog].[folders] fld

ON env.[folder_id] = fld.[folder_id]

AND env.[name] = @env

AND fld.name = @folder)' + @cr;

SET @sql += @tab + 'BEGIN' + @cr;

SET @sql += @tab + @tab + 'RAISERROR(''Deleting existing environment: %s ...'', 10, 1, @env) WITH NOWAIT;' + @cr;

SET @sql += @tab + @tab + 'EXEC [SSISDB].[catalog].[delete_environment] @folder_name = @folder , @environment_name = @env' + @cr;

SET @sql += @tab + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr;

SET @sql += @tab + 'END' + @cr + @cr;

planetmatt SSCrazy Points: 2207 More actions · Answer 4

This script works perfectly for recreating existing environments.

Is there a similar script that can automate or script out the mapping of package parameters to the environment variables?