Have you ever created an environment in the Integration Services catalog, taking the time to manually create a ton of variables and then didn’t script the creation before hitting “OK”. Now you have to duplicate this environment on another server (test, UAT, or production).
At first, I thought to myself “surely I can just script out the environment”.
Doh! There is no option to script the environment! I could "Move" it, but that's not what I want.
I had spent a lot of time manually creating that environment and I didn’t want to do that again, ever. There had to be a way to script an environment and I was going to figure it out.
Environments are not objects in SQL Server but rather data stored in various tables in the SSISDB database (this might be different for you if you didn’t stick with the default name). This is the exact same way SQL Server treats jobs; just like SQL Server has CRUD stored procedures for jobs in the msdb database, it has CRUD stored procedures for SSIS Environments in the SSISDB database. Now I was onto something. I could use a combination of the data retrieved from the various tables in SSISDB to generate a script to create an environment and all of its variables.
Oh man, was this ever going to help when deploying the same environment across several servers (dev, test, uat, production). I played around with the data and stored procedures for a while to get things just how I like it and formalized the script into a stored procedure:
CREATE PROCEDURE dbo.usp_SSIS_ScriptEnvironment
@folder sysname
, @env sysname
AS
SET NOCOUNT ON;
DECLARE @project_id int,
@reference_location char(1),
@folder_description nvarchar(1024),
@sql varchar(max) = '',
@name sysname,
@cr char(1) = char(10),
@tab char(4) = SPACE(4),
@ver nvarchar(128) = CAST(serverproperty('ProductVersion') AS nvarchar);
SET @ver = CAST(SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) as int);
IF (@ver < 11)
BEGIN
RAISERROR ('This procedure is not supported on versions prior SQL 2012', 16, 1) WITH NOWAIT;
RETURN 1;
END;
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSISDB')
BEGIN
RAISERROR('The SSISDB database does not exist on this server', 16, 1) WITH NOWAIT;
RETURN 1;
END;
/* TO DO - get the folder, environment description-*/ SET @sql = '/**************************************************************************************' + @cr;
SET @sql += @tab + 'This script creates a script to generate and SSIS Environment and its variables.' + @cr;
SET @sql += @tab + 'Replace the necessary entries to create a new envrionment' + @cr;
SET @sql += @tab + '***NOTE: variables marked as sensitive have their values masked with ''<REPLACE_ME>''.' + @cr;
SET @sql += @tab + @tab + 'These values will need to be replace with the actual values' + @cr;
SET @sql += '**************************************************************************************/' + @cr +@cr;
SET @sql += 'DECLARE @ReturnCode INT=0, @folder_id bigint' + @cr + @cr;
SET @sql += '/*****************************************************' + @cr;
SET @sql += @tab + 'Variable declarations, make any changes here' + @cr;
SET @sql += '*****************************************************/' + @cr;
SET @sql += 'DECLARE @folder sysname = ''' + @folder + ''' /* this is the name of the new folder you want to create */' + @cr;
SET @sql += @tab + @tab + ', @env sysname = ''' + @env + ''' /* this is the name of the new environment you want to create */';
PRINT @sql;
/*
Generate the variable declarations at the "top" this makes it easier to replace/update the values
The variable names here map to the name of the variable being created
*/ SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type + '= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
, [name] = ev.name
INTO #env_var
FROM [SSISDB].[catalog].[folders] f
INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id
INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id
WHERE (f.name = @folder) AND (e.name = @env);
/*
Yes, I am looping here. We don't know how many variables, sql_variant can be up to 8,000 bytes for the base type
and don't want to be limited trying to print varchar(max) to the output window
... so we're going to print them one at a time
*/ WHILE EXISTS (SELECT TOP 1 1 FROM #env_var)
BEGIN
SELECT TOP 1 @sql = env_var, @name = name FROM #env_var ORDER BY name;
PRINT @sql;
DELETE FROM #env_var WHERE name = @name;
END;
SET @sql = ';' + @cr + '/* Starting the transaction */' + @cr;
SET @sql += 'BEGIN TRANSACTION' + @cr;
SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)' + @cr;
SET @sql += @tab + 'BEGIN' + @cr;
SET @sql += @tab + @tab + 'RAISERROR(''Creating folder: %s ...'', 10, 1, @folder) WITH NOWAIT;' + @cr;
SET @sql += @tab + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT' + @cr;
SET @sql += @tab + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr;
SET @sql += @tab + 'END' + @cr + @cr;
SET @sql += @tab + 'RAISERROR(''Creating Environtment: %s'', 10, 1, @env) WITH NOWAIT;' + @cr;
SET @sql += @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env' + @cr;
SET @sql += @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr + @cr;
SET @sql += @tab + '/******************************************************' + @cr;
SET @sql += @tab + @tab + 'Variable creation' + @cr;
SET @sql += @tab + '******************************************************/' ;
PRINT @sql;
/* Generate the variable creation */ SELECT [cmd] = @tab + 'RAISERROR(''Creating variable: ' + ev.name + ' ...'', 10, 1) WITH NOWAIT;' + @cr
+ @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]' + @cr
+ @tab + @tab + '@variable_name=N''' + ev.name + '''' + @cr
+ @tab + @tab + ', @sensitive=' + CONVERT(varchar(2), ev.sensitive) + @cr
+ @tab + @tab + ', @description=N''' + ev.[description] + '''' + @cr
+ @tab + @tab + ', @environment_name=@env' + @cr
+ @tab + @tab + ', @folder_name=@folder' + @cr
+ @tab + @tab + ', @value=@' + ev.name + @cr
+ @tab + @tab + ', @data_type=N''' + ev.type + '''' + @cr
+ @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr
, [name] = ev.name
INTO #cmd
FROM [SSISDB].[catalog].[folders] f
INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id
INNER JOIN [SSISDB].[catalog].[environment_variables] ev ON e.environment_id = ev.environment_id
WHERE (f.name = @folder) AND (e.name = @env);
/*Print out the variable creation procs */ WHILE EXISTS (SELECT TOP 1 1 FROM #cmd)
BEGIN
SELECT TOP 1 @sql = cmd, @name = name FROM #cmd ORDER BY name;
PRINT @sql;
DELETE FROM #cmd WHERE name = @name;
END;
/* finsih the transaction handling */ SET @sql = 'COMMIT TRANSACTION' + @cr;
SET @sql += 'RAISERROR(N''Complete!'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'GOTO EndSave' + @cr + @cr;
SET @sql += 'QuitWithRollback:' + @cr;
SET @sql += 'IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' + @cr;
SET @sql += 'RAISERROR(N''Variable creation failed'', 16,1) WITH NOWAIT;' + @cr + @cr;
SET @sql += 'EndSave:' + @cr;
SET @sql += 'GO';
PRINT @sql;
RETURN 0;
GOThere are a few things in this script that make it easier to create a new environment.
- The script is generated is similar to how SQL Server scripts jobs; the script is wrapped in a transaction so that if there is a failure, all changes will be rolled back.
- The script will generate the create scripts for the SSISDB Project folder (if it does not exist) and the Environment.
- All of the variable declarations are at the top of the script. This way you don’t have to scroll through the whole script to find the values you need to change.
- Since we cannot script out sensitive values, all of the sensitive values are masked with ‘<REPLACE_ME>’.
Let’s go through an example where we will create an environment, script it before creating it, and then run the stored procedure to generate the create script.
I’ve manually created an environment that has at least one variable for each type.
I then generated the create script before hitting “OK”. Here is that script.
DECLARE @var bit = N'True' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Boolean', @sensitive=False, @description=N'Boolean Test Value', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Boolean' GO DECLARE @var tinyint = N'0' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Byte', @sensitive=False, @description=N'Byte Test Variable', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Byte' GO DECLARE @var datetime = N'1900-01-01' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'DateTime', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'DateTime' GO DECLARE @var decimal(38,18) = N'1.2345' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Decimal', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Decimal' GO DECLARE @var float = N'6.78' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Double', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Double' GO DECLARE @var sql_variant = N'ImIns3nsitive!' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'InsensitivePwd', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'String' GO DECLARE @var smallint = N'16' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Int16', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Int16' GO DECLARE @var int = N'32' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Int32', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Int32' GO DECLARE @var bigint = N'64' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Int64', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Int64' GO DECLARE @var smallint = N'0' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SByte', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'SByte' GO DECLARE @var sql_variant = N'ImAS3ns1t1vePwd' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SensitivePwd', @sensitive=True, @description=N'Sensitive Password', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'String' GO DECLARE @var float = N'1' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Single', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'Single' GO DECLARE @var sql_variant = N'I am a string' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'String', @sensitive=False, @description=N'Test String', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'String' GO DECLARE @var bigint = N'320' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'UInt32', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'UInt32' GO DECLARE @var bigint = N'640' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'UInt64', @sensitive=False, @description=N'', @environment_name=N'Test1', @folder_name=N'Test', @value=@var, @data_type=N'UInt64' GO
The script generated by the GUI definitely works but it is kind of ugly. After the environment was created, I scripted it out using the newly created stored procedure:
EXEC dbo.usp_SSIS_ScriptEnvironment @folder = 'Test', @env = 'Test1'
The resulting script:
/**************************************************************************************
This script creates a script to generate and SSIS Environment and its variables.
Replace the necessary entries to create a new envrionment
***NOTE: variables marked as sensitive have their values masked with '<REPLACE_ME>'.
These values will need to be replace with the actual values
**************************************************************************************/DECLARE @ReturnCode INT=0, @folder_id bigint
/*****************************************************
Variable declarations, make any changes here
*****************************************************/DECLARE @folder sysname = 'Test' /* this is the name of the new folder you want to create */ , @env sysname = 'Test1' /* this is the name of the new environment you want to create */ , @Boolean bit= N'1'
, @Byte tinyint= N'0'
, @DateTime datetime= N'Jan 1 1900 12:00AM'
, @Decimal decimal(38,18)= N'1.234500000000000000'
, @Double float= N'6.78'
, @InsensitivePwd sql_variant= N'ImIns3nsitive!'
, @Int16 smallint= N'16'
, @Int32 int= N'32'
, @Int64 bigint= N'64'
, @SByte smallint= N'0'
, @SensitivePwd sql_variant= N'<REPLACE_ME>'
, @Single float= N'1'
, @String sql_variant= N'I am a string'
, @UInt32 bigint= N'320'
, @UInt64 bigint= N'640'
;
/* Starting the transaction */BEGIN TRANSACTION
IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)
BEGIN
RAISERROR('Creating folder: %s ...', 10, 1, @folder) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
END
RAISERROR('Creating Environtment: %s', 10, 1, @env) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
/******************************************************
Variable creation
******************************************************/ RAISERROR('Creating variable: Boolean ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Boolean'
, @sensitive=0
, @description=N'Boolean Test Value'
, @environment_name=@env
, @folder_name=@folder
, @value=@Boolean
, @data_type=N'Boolean'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Byte ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Byte'
, @sensitive=0
, @description=N'Byte Test Variable'
, @environment_name=@env
, @folder_name=@folder
, @value=@Byte
, @data_type=N'Byte'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: DateTime ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'DateTime'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@DateTime
, @data_type=N'DateTime'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Decimal ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Decimal'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Decimal
, @data_type=N'Decimal'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Double ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Double'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Double
, @data_type=N'Double'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: InsensitivePwd ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'InsensitivePwd'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@InsensitivePwd
, @data_type=N'String'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Int16 ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Int16'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Int16
, @data_type=N'Int16'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Int32 ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Int32'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Int32
, @data_type=N'Int32'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Int64 ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Int64'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Int64
, @data_type=N'Int64'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: SByte ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'SByte'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@SByte
, @data_type=N'SByte'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: SensitivePwd ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'SensitivePwd'
, @sensitive=1
, @description=N'Sensitive Password'
, @environment_name=@env
, @folder_name=@folder
, @value=@SensitivePwd
, @data_type=N'String'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: Single ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'Single'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@Single
, @data_type=N'Single'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: String ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'String'
, @sensitive=0
, @description=N'Test String'
, @environment_name=@env
, @folder_name=@folder
, @value=@String
, @data_type=N'String'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: UInt32 ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'UInt32'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@UInt32
, @data_type=N'UInt32'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
RAISERROR('Creating variable: UInt64 ...', 10, 1) WITH NOWAIT;
EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]
@variable_name=N'UInt64'
, @sensitive=0
, @description=N''
, @environment_name=@env
, @folder_name=@folder
, @value=@UInt64
, @data_type=N'UInt64'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
COMMIT TRANSACTION
RAISERROR(N'Complete!', 10, 1) WITH NOWAIT;
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
RAISERROR(N'Variable creation failed', 16,1) WITH NOWAIT;
EndSave:
GOThe script is formatted a little better (IMO) and includes error handling. I’ll leave it as an exercise to you to compare the scripts generated from the stored procedure and GUI to see that they in essence the same.
If I want to use this script to create a duplicate environment named ‘Test2’ to a new ‘Test2’ folder on my dev box. I need to change the @folder and @env variables, provide values for any sensitive values, and make any other changes to the variables.
/*****************************************************
Variable declarations, make any changes here
*****************************************************/DECLARE @folder sysname = 'Test2' /* this is the name of the new folder you want to create */ , @env sysname = 'Test2' /* this is the name of the new environment you want to create */ , @Boolean bit= N'1'
, @Byte tinyint= N'0'
, @DateTime datetime= N'Jan 1 1900 12:00AM'
, @Decimal decimal(38,18)= N'1.234500000000000000'
, @Double float= N'6.78'
, @InsensitivePwd sql_variant= N'ImIns3nsitive!'
, @Int16 smallint= N'16'
, @Int32 int= N'32'
, @Int64 bigint= N'64'
, @SByte smallint= N'0'
, @SensitivePwd sql_variant= N'ImAS3ns1t1vePwd2!'
, @Single float= N'1'When I execute the script, I get a synopsis of what occurred:
In SSMS you can see that newly created folder and environment:
… And a little sanity-check to make sure they are indeed the same:
Old Environment
New EnvironmentThis is a simple script/stored procedure that can save you quite a bit of time recreating environments. I welcome any questions or comments.
