SSIS - Automation of ETL packages

We are proposing to automate the ETL packages to our logistics customer. SSIS is using project deployment and not package deployment. We have developed detailed framework for package deployment which will not work here.  

More than 150 packages used to integrate legacy systems to Salesforce. All the legacy systems push their feed into FTP and SSIS data load framework pull it from this FTP path. Some of the packages are having dependency on other packages. All the source to stage packages will fail if they didn’t find the files on FTP.

ETL project implemented in three releases and all the releases using different solution approach.

We can use master package to call all these data load packages based on the scheduled time specified. As the packages are deployed in different projects, master package should have access to all these projects.

Normally SSIS master package can access those packages in the same solution. To overcome this issue we can use below script in execute SQL task of one package which can act as Master Package. We are planning to use stored procedure to better accommodate scheduling times, dependency etc. and dynamically change the folder and package name.

--execution creation

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution]

@package_name=N'PKG_SLIJ_STG_TO_ODS_TS.dtsx',

@execution_id=@execution_id OUTPUT,

@folder_name=N'CustomerName_ETL',

@project_name=N' CustomerName_ETL',

@use32bitruntime=False

--add parameters

DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@execution_id, 

@object_type=50, (20 for project parameter and 30 for package parameter)

@parameter_name=N'LOGGING_LEVEL',

@parameter_value=@var0

--execute the job

EXEC [SSISDB].[catalog].[start_execution] @execution_id