exec_status.sql

USE SSISDB
GO

-- Create [toolbelt] schema if it does not yet exist
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'toolbelt')
	EXEC sp_executesql N'CREATE SCHEMA [toolbelt]'
GO

-- Drop table if exists
IF EXISTS (SELECT 1 FROM sys.tables WHERE OBJECT_ID = OBJECT_ID('[toolbelt].[ExecutionStatus]'))
  DROP TABLE [toolbelt].[ExecutionStatus]
GO

-- Create table
CREATE TABLE [toolbelt].[ExecutionStatus]
(
	status INT NOT NULL
	, statusName VARCHAR(50) NOT NULL
	, statusGroup VARCHAR(50) NOT NULL
)
GO

-- Populate with status values
INSERT [toolbelt].[ExecutionStatus]
VALUES (1, 'Created', 'Active')
		, (2, 'Running', 'Active')
		, (3, 'Canceled', 'Failed')
		, (4, 'Failed', 'Failed')
		, (5, 'Pending', 'Active')
		, (6, 'Ended unexpectedly', 'Failed')
		, (7, 'Succeeded', 'Succeeded')
		, (8, 'Stopping', 'Active')
		, (9, 'Completed', 'Failed')
GO

-- Create clustered PK on status
ALTER TABLE [toolbelt].[ExecutionStatus] ADD  CONSTRAINT [PK_ExecutionStatus] PRIMARY KEY CLUSTERED 
(
	[status] ASC
)
GO

-- Create index on status name
CREATE UNIQUE INDEX ix_ExecutionStatus_name
ON [toolbelt].[ExecutionStatus] (statusName)
GO