There are several ways to start the ETL jobs of a data warehouse. You can create an SSIS package that calls all underlying ETL packages. You can also create an SSIS package that calls all Stored Procedures for ETL processing. Finally, you can create a Stored Procedure that calls all ETL jobs. The call to the ETL job can be a SQL agent job, a cmd file that kicks off a T-SQL script, or a stored procedure that does the work directly.
My experience with an SSIS “Master Package” is that it is slow. SSIS is slow to start and is no longer pleasant to work with. On top of that, you have to change the package by hand every time, for example when a new table is added (dimension or fact).
At the moment we work with a mechanism that kicks off agent jobs that belong to a certain “agent job category”. Say you have a star schema called “accounting”. All ETL jobs (agent jobs) belong to this category.
SELECT *
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.syscategories ON ( syscategories.category_class = 1
AND sysjobs.category_id = syscategories.category_id
)
The last column “name” shows the category of the agent job. With a smart procedure you can call all jobs at once or one after the other, and so load all ETL processes to update the Data Warehouse. As soon as a new table/dimension/fact is generated, a new agent job is automatically created in this category, and it is loaded automatically too. No more manual work.
How do you do it?
Frequently asked questions
How do I start all ETL jobs of a data warehouse with one call?
Create a procedure that calls all SQL Agent jobs in one agent job category, all at once or one after the other. That way you load all ETL processes of the data warehouse in one go.
Why is an SSIS Master Package not a good choice to control ETL jobs?
An SSIS Master Package is slow. SSIS is slow to start and is no longer pleasant to work with. You also have to change the package by hand when a new dimension or fact table is added.
How do I read the agent job category in SQL Server?
Join msdb.dbo.sysjobs to msdb.dbo.syscategories on category_id, with category_class = 1. The column name shows the category of the agent job.
What are the ways to start ETL jobs in a data warehouse?
An SSIS package that calls the ETL packages, an SSIS package that calls stored procedures, or a stored procedure that calls all jobs. The call itself can be a SQL agent job, a cmd file or a stored procedure.
How do I include a new dimension or fact table in the ETL load automatically?
A new agent job is created for the new table in the same agent job category. The procedure that calls the category then loads that job automatically. No more manual work in a master package.
Comments
Related blogs
Data conversion performance in SSIS
Metadata – Table growth in data warehouses
ETL Tool or SQL