问运行SSIS包，但包路径将在运行时确定

EN

CREATE TABLE ETL.PackageControl
(
    PackageControlSK int identity(1,1) NOT NULL PRIMARY KEY
,   ExecutionOrder int NOT NULL
,   PackagePath varchar(255) NOT NULL
);
-- Sample data
INSERT INTO ETL.PackageControl
SELECT * 
FROM
(
    VALUES
        (10, '\ssisdb\folder\project\package.dtsx')
    ,   (20, '\ssisdb\folder\project\package2.dtsx') 
    ,   (30, '\ssisdb\folder\project\package3.dtsx') 
)D(ExecutionOrder, PackagePath)
WHERE NOT EXISTS (SELECT * FROM ETL.PackageControl AS PC WHERE PC.PackagePath = D.PackagePath);

DECLARE @PackagePath varchar(255)
,   @ExecutionOrder int
,   @execution_id bigint
-- Your TODO will be parsing this data out of PackagePath
-- Or change the definition of PackageControl to hold the individual parts
-- whatever makes sense for your environment
,   @folder_name nvarchar(128) = 'folder'
,   @project_name nvarchar(128) = 'project'
,   @package_name nvarchar(128)

DECLARE CSR CURSOR 
FAST_FORWARD FOR
SELECT
    PC.PackagePath, PC.ExecutionOrder
FROM
    ETL.PackageControl AS PC
ORDER BY
    PC.ExecutionOrder ASC;

OPEN CSR;
FETCH NEXT FROM CSR INTO @PackagePath, @ExecutionOrder;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Lazy assumption here on how we can identify the package that is needed to be run. The trailing slash might be esacaping the single quote, handle appropriately
    SET @package_name = REPLACE(@packagePath, '\ssisdb\folder\project\', '');

    EXECUTE SSISDB.catalog.create_execution
        @package_name=@package_name
    ,   @folder_name=@folder_name
    ,   @project_name=@project_name
    -- TODO: Which runtime do you need to use?
    ,   @use32bitruntime=False
    ,   @execution_id=@execution_id OUTPUT;

    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;

FETCH NEXT FROM CSR INTO @PackagePath, @ExecutionOrder;
END
CLOSE CSR;
DEALLOCATE CSR;