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




Comments

  1. IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project.

    IT Company Employess Productivity usually increases when a company implements corporate training courses on latest technologies.
    corporate training in chennai
    It Companies need of Corporate training programme arises due to improvement in technology, need for getting better performance or as part of professional development. corporate training companies in chennai Corporate Training refers to a system of professional development activities provided to educate employees.
    corporate training companies in india

    ReplyDelete

Post a Comment

Popular posts from this blog

Microsoft BI Implementation - Cube back up and restore using XMLA command

Databricks - incorrect header check