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
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.
ReplyDeleteIT 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