USE SSISDB;
SET NOCOUNT ON;
-------------------
-- Configuration
-------------------
DECLARE @FolderToExport VARCHAR(50) = 'BI'
DECLARE
@folder_id INT
,@name VARCHAR(150)
,@project_id INT
,@sql VARCHAR(MAX) = ''
,@sqlTemp VARCHAR(MAX) = ''
,@cr CHAR(1) = CHAR(10)
,@tab CHAR(4) = SPACE(4)
,@ProjectName VARCHAR(50)
,@VariableName VARCHAR(150)
,@Value VARCHAR(600)
SET @sql = '';
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 '+ @cr;
SET @sql += @tab + @tab + ' @folder sysname' + @cr;
SET @sql += @tab + @tab + ',@env sysname' + @cr;
SET @sql += @tab + @tab + ',@project sysname;' + @cr;
-- Get Folder ID
SELECT @folder_id = folder_id FROM [internal].[folders] WHERE name = @FolderToExport
PRINT @SQL;
SET @SQL = '';
---------------------------------------------
-- Export Current package Configuration
---------------------------------------------
DECLARE db_cursor_variable CURSOR FOR
SELECT DISTINCT
CONVERT(NVARCHAR(50), projects.name ) AS ProjectName
,CONVERT(NVARCHAR(150), projects.name +'__'+ parameter_name) AS VariableName
,CONVERT(NVARCHAR(600), ISNULL(design_default_value, '') ) AS Value
FROM internal.object_parameters main
INNER JOIN internal.projects ON main.project_id = projects.project_id
WHERE project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] s WHERE s.project_id = main.project_id)
AND parameter_name NOT LIKE 'CM.%'
AND projects.folder_id = @folder_id
ORDER BY CONVERT(NVARCHAR(50), projects.name )
,CONVERT(NVARCHAR(150), projects.name +'__'+ parameter_name)
,CONVERT(NVARCHAR(600), ISNULL(design_default_value, ''))
OPEN db_cursor_variable
FETCH NEXT FROM db_cursor_variable
INTO
@ProjectName
,@VariableName
,@Value
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DECLARE @'+ @VariableName +' NVARCHAR(600) = N'''+ @Value +''';';
FETCH NEXT FROM db_cursor_variable
INTO
@ProjectName
,@VariableName
,@Value
END
CLOSE db_cursor_variable
DEALLOCATE db_cursor_variable
SET @SQL += 'SET @folder = '''+ @FolderToExport +''''+ @cr + @cr+ @cr;
-- Begin transaction
SET @sql += ';' + @cr + '/* Starting the transaction */' + @cr;
SET @sql += 'BEGIN TRANSACTION' + @cr + @cr + @cr;
----
---- Test if folder exist -> If not create it
--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;
---------------------------------------------
-- Create environment + variables
-----------------------------------------------
DECLARE db_cursor CURSOR FOR
SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @project_id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;
SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE name = @env)' + @cr;
SET @sql += @tab + 'BEGIN' + @cr;
SET @sql += @tab + @tab +'RAISERROR(''Creating Environment: %s'', 10, 1, @env) WITH NOWAIT;' + @cr;
SET @sql += @tab + @tab +'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env' + @cr;
SET @sql += @tab + @tab +'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr + @cr;
SET @sql += @tab + 'END' + @cr;
print @sql;
SET @sql = '';
-- ---------------------------------------------
-- Generate the variable creation
-- ---------------------------------------------
SET @sql += ' -- ProjectId => '+ CONVERT(NVARCHAR(50), @project_id) + @cr;
SELECT DISTINCT [cmd] = @tab + 'RAISERROR('' Creating variable: ' + parameter_name + ' ...'', 10, 1) WITH NOWAIT;' + @cr
+ @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]' + @cr
+ @tab + @tab + ' @variable_name=N''' + parameter_name + '''' + @cr
+ @tab + @tab + ', @sensitive=' + CONVERT(varchar(2), sensitive) + @cr
+ @tab + @tab + ', @description=N''' + [description] + '''' + @cr
+ @tab + @tab + ', @environment_name=@env' + @cr
+ @tab + @tab + ', @folder_name=@folder' + @cr
+ @tab + @tab + ', @value=@' + @name +'__'+ parameter_name + @cr
+ @tab + @tab + ', @data_type=N''' + parameter_data_type + '''' + @cr
+ @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr
, [name] = parameter_name
INTO #cmd
FROM [SSISDB].[internal].[object_parameters]
WHERE project_id = @project_id
AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
AND parameter_name NOT LIKE 'CM.%'
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;
DROP TABLE #cmd;
SET @sql = '';
FETCH NEXT FROM db_cursor INTO @project_id, @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
---------------------------------------------
-- Set Environment on each Project
-- + Set Environment variables on each Project Variables
---------------------------------------------
DECLARE db_cursor CURSOR FOR
SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @project_id, @name
SET @sql = 'DECLARE @reference_id AS BIGINT'+ @cr;
PRINT @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @tab + 'SET @project = '''+ @name +''';'+ @cr;
SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;
SET @sql += 'RAISERROR(''Configure Project: %s ...'', 10, 1, @project) WITH NOWAIT;' + @cr + @cr
SET @sql += '-- ------------------------------------------ '+ @cr
SET @sql += '-- Set Environment to Project ' + @cr
SET @sql += '-- ------------------------------------------ '+ @cr
SET @sql += 'RAISERROR('' Set Environment (%s) as Reference ...'', 10, 1, @env) WITH NOWAIT;' + @cr
SET @sql += 'EXEC SSISDB.catalog.create_environment_reference'+ @cr
SET @sql += @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr
SET @sql += @tab + @tab + '@environment_name = @env, -- Environment Name '+ @cr
SET @sql += @tab + @tab + '@project_name = @project, -- Project Name '+ @cr
SET @sql += @tab + @tab + '@reference_type = ''R'','+ @cr
SET @sql += @tab + @tab + '@reference_id = @reference_id OUTPUT'+ @cr
PRINT @SQL
-- ------------------------------------------
-- Set Environment Variable to Project Variables
-- ------------------------------------------
IF OBJECT_ID('tempdb..#cmd2') IS NOT NULL DROP TABLE #cmd2
SET @sql = '-- ------------------------------------------ '+ @cr
SET @sql += '-- Set Environment Variable to Project Variables '+ @cr
SET @sql += '-- ------------------------------------------ '+ @cr
SET @sql = @tab + 'SET @project = '''+ @name +''';'+ @cr;
SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;
PRINT @sql
SELECT
[cmd] =
'RAISERROR('' Set Project variable ('+ parameter_name +') ...'', 10, 1) WITH NOWAIT;' + @cr
+ 'EXECUTE [SSISDB].[catalog].set_object_parameter_value '+ @cr
+ @tab + @tab + '@object_type = '+ CONVERT(NVARCHAR(10), object_type) +',' + @cr
+ @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr
+ @tab + @tab + '@project_name = @project, -- Environment Name'+ @cr
+ @tab + @tab + '@parameter_name = N'''+ parameter_name +''', -- Project Variable Name'+ @cr
+ @tab + @tab + '@parameter_value = N'''+ parameter_name +''', -- Environment Variable Name'+ @cr
+ @tab + @tab + '@object_name = N'''+ object_name +''','+ @cr
+ @tab + @tab + '@value_type = ''R'''+ @cr
,[name] = parameter_name
INTO #cmd2
FROM [SSISDB].[internal].[object_parameters]
WHERE project_id = @project_id
AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
AND parameter_name NOT LIKE 'CM.%'
/*Print out the variable creation procs */
WHILE EXISTS (SELECT TOP 1 1 FROM #cmd2)
BEGIN
SELECT TOP 1 @sql = cmd, @name = name FROM #cmd2 ORDER BY name;
PRINT @sql;
DELETE FROM #cmd2 WHERE name = @name;
END;
FETCH NEXT FROM db_cursor INTO @project_id, @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
---------------------------------------------
-- END!
---------------------------------------------
SET @sql = '';
/* finsih the transaction handling */
SET @sql += 'COMMIT TRANSACTION' + @cr;
--SET @sql += 'RAISERROR(N''Complete!'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' _____ _ _ _ _ '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''/ __ \ | | | | | | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| / \/ ___ _ __ ___ _ __ | | ___| |_ ___ __| | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| | / _ \| ''''_ ` _ \| ''''_ \| |/ _ \ __/ _ \/ _` | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| \__/\ (_) | | | | | | |_) | | __/ || __/ (_| |_|'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' \____/\___/|_| |_| |_| .__/|_|\___|\__\___|\__,_(_)'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' | | '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' |_| '', 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;
