Scripting Environments in SSIS

  • AlphaTangoWhiskey

    SSChampion

    Points: 10814

    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: 18

    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

  • john.killeen

    Newbie

    Points: 1

    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;

     

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

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