SSIS – Create Environment from Packages variables

SSIS – Create Environment from Packages variables

I created this script to automate some “not funny” tasks with our SSIS Catalog. If you have several SSIS projects configured in project mode (with a project.params file), when deploy them on your servers you unfortunately have to manually create the different environments.

After deploying SSIS packages, you can run the following query and use the generated SQL code.

the generated SQL script:

  • Create the different environment (Based on the name of the projects)
  • Create variables with default values (value available in packages)
  • Assigning Environments to Projects
  • Assign the environment variables to the project variables.

The following code is not clean, but it does the work! 🙂

    [TSQL]
    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;
    [/TSQL]

    SSIS – Log Execution time

    SSIS – Log Execution time

    This query helps you to have a better view of your SSIS Packages executions.

    When you run a “master job / package”, you don’t clearly see the execution time for your child packages in SSISDB Reports.

    With this query, now it’s possible 🙂

    USE SSISDB
    GO
    
    SELECT
    	 package_name
    	,execution_id
    	,executable_id
    	,StartTime
    	,EndTime
    	,LastStatus
    	,execution_result
    	,RowIndex
    	,DATEDIFF(minute, StartTime, EndTime) AS 'execution_time[min]'
    FROM
    (
    	SELECT DISTINCT
    		 executables.package_name AS package_name
    		,executables.execution_id AS execution_id
    		,executables.executable_id AS executable_id
    		,FIRST_VALUE(start_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY start_time ASC) AS StartTime
    		,FIRST_VALUE(end_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY end_time DESC) AS EndTime
    		,FIRST_VALUE(execution_result) OVER(PARTITION BY executables.package_name ORDER BY end_time DESC) AS LastStatus
    		,execution_result AS execution_result
    		,ROW_NUMBER() OVER(partition by executables.package_name ORDER BY end_time DESC) AS RowIndex
      	  FROM    [catalog].[executables]
    	LEFT JOIN [catalog].[executable_statistics] ON
    														executable_statistics.execution_id = executables.execution_id
    													AND executable_statistics.executable_id = executables.executable_id
    
    	--WHERE executables.package_name = 'YourPackage.dtsx'
    ) A
     WHERE a.RowIndex = 1
     ORDER BY StartTime ASC

    Pin It on Pinterest