May 31, 2019 at 5:30 pm
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!
July 2, 2019 at 10:17 pm
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>') + ''''
Philippe
September 9, 2020 at 10:17 am
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;
October 14, 2022 at 8:43 am
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?
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy