USE ETLControl
GO
CREATE TABLE dbo.PackageExecution
(
	FolderName NVARCHAR(255) NOT NULL,
	ProjectName NVARCHAR(255) NOT NULL,
	PackageName NVARCHAR(255) NOT NULL,
	IsEnabled BIT NOT NULL CONSTRAINT DF_PackageExecution_IsEnabled DEFAULT (1),
	PackageGroup VARCHAR(10)
);
CREATE TABLE dbo.PackageWorker
(
	PackageName NVARCHAR(255) NOT NULL,
	WorkerName VARCHAR(50) NOT NULL
);
GO

CREATE PROCEDURE dbo.ExecutePackages
(
	@FolderName NVARCHAR(255),
	@ProjectName NVARCHAR(255),
	@PackageGroup VARCHAR(10),
	@EnvironmentName NVARCHAR(255)
)
AS 
BEGIN

	DROP TABLE IF EXISTS #Executions
	CREATE TABLE #Executions (ExecutionId BIGINT);


	DECLARE @PackageName NVARCHAR(255); 
	DECLARE @WorkerName NVARCHAR(50);
	DECLARE @execution_id BIGINT;
	DECLARE @EnvironmentId BIGINT = (
										SELECT reference_id
										FROM SSISDB.[catalog].[environment_references] 
										WHERE [environment_name] = @EnvironmentName 
										AND project_id = (
											SELECT project_id FROM SSISDB.[catalog].projects WHERE [name] = @ProjectName)
									) ;
	DECLARE @StatusId INT;
				
								
	DECLARE Packages CURSOR LOCAL FAST_FORWARD FOR
		SELECT 	PackageName FROM dbo.PackageExecution
		WHERE 	FolderName = @FolderName
		AND		ProjectName = @ProjectName
		AND		(PackageGroup = @PackageGroup OR @PackageGroup IS NULL)
		AND 	IsEnabled = 1
		ORDER BY PackageName;

		OPEN Packages

		FETCH NEXT FROM Packages
		INTO @PackageName

		WHILE @@FETCH_STATUS = 0 
		BEGIN
			DECLARE @useanyworker BIT = 1;
			IF EXISTS (SELECT * FROM dbo.PackageWorker WHERE PackageName = @PackageName)
			BEGIN
				SET @useanyworker = 0
			END;
			
			EXEC [SSISDB].[catalog].[create_execution] 
					@package_name=@PackageName, @execution_id=@execution_id OUTPUT, @folder_name=@FolderName, @project_name=@ProjectName, 
					@use32bitruntime=False, @reference_id=@EnvironmentId,
					@useanyworker= @useanyworker, @runinscaleout=True
			SELECT @execution_id
			INSERT INTO #Executions VALUES (@execution_id);
		
			IF (@useanyworker = 0)
			BEGIN
			DECLARE @workerid uniqueidentifier;

			DECLARE Workers CURSOR LOCAL FAST_FORWARD FOR
				SELECT WorkerName FROM dbo.PackageWorker WHERE PackageName = @PackageName;

				OPEN Workers

				FETCH NEXT FROM Workers
				INTO @WorkerName

				WHILE @@FETCH_STATUS = 0 
				BEGIN
					
					SET @workerid = (SELECT WorkerAgentId FROM SSISDB.catalog.worker_agents WHERE MachineName = @WorkerName AND IsEnabled = 1);
					IF (@workerid IS NOT NULL)
					BEGIN
						EXEC [SSISDB].[catalog].[add_execution_worker] @execution_id,  @workeragent_id=@workerid
					END
						
					ELSE
					--If the worker cannot be found (use asked for invalid worker, or worker is not enabled) pick a default worker to use, 
					--othewise the exectuion will not exit - it will sit on Pending Execution forever. 
					BEGIN
					EXEC [SSISDB].[catalog].[add_execution_worker] @execution_id,  @workeragent_id='DB600260-E9D6-49EB-881A-9DECE223C1D2'
					END

					FETCH NEXT FROM Workers
					INTO @WorkerName

					
				END
		
				CLOSE Workers
				DEALLOCATE Workers		
			END

			DECLARE @var0 smallint = 1
			EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
			EXEC [SSISDB].[catalog].[start_execution] @execution_id,  @retry_count=0

			FETCH NEXT FROM Packages
			INTO @PackageName
		END

	CLOSE Packages
	DEALLOCATE Packages

	WHILE EXISTS(SELECT package_name,status FROM SSISDB.catalog.executions WHERE execution_id IN (SELECT ExecutionId FROM #Executions) AND [status] NOT IN (3, 4, 6, 7, 9))
	BEGIN
		WAITFOR DELAY '00:00:01';
	END

	IF EXISTS(SELECT status FROM SSISDB.catalog.executions WHERE execution_id IN (SELECT ExecutionId FROM #Executions) AND [status] IN (3, 4, 6))
	BEGIN;
		THROW 50000, N'Error In Package Exeuction', 1
	END

END