Configuration - SQL Server Integration Services Design Patterns - page 431

INSERT INTO @FileList (RowNum, Id, Directory, Name)
SELECT ROW_NUMBER() OVER (ORDER BY id),
id, Directory, Name
FROM [dbo].[PackageConfiguration]
WHERE processed = 0
DECLARE @maxCount int = (SELECT MAX(RowNum) FROM
@FileList)
DECLARE @count int = (SELECT MIN(RowNum) FROM @FileList)
WHILE (@count <= @maxCount)
BEGIN
DECLARE @Id NVARCHAR(255) = (SELECT Id FROM
@FileList WHERE RowNum = @count)
DECLARE @DirectoryValue NVARCHAR(255) = (SELECT
Directory FROM @FileList WHERE RowNum = @count)
DECLARE @NameValue NVARCHAR(255) = (SELECT Name
FROM @FileList WHERE RowNum = @count)
-- Create the package execution
DECLARE @exec_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@execution_id = @exec_id OUTPUT,
@package_name = @PackageName,
@folder_name = @FolderName,
@project_name = @ProjectName
-- Set the Directory parameter value
EXEC
[SSISDB].[catalog].[set_execution_parameter_value]
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = @DirectoryParameter,
@parameter_value = @DirectoryValue
-- Set the File Name parameter value