Home / SSIS

SSIS Master ETL Package Overview

Rating: 5.0Blog-star
Views: 7480
by Ravindra Savaram
Last modified: February 11th 2021

Putting it all together is perhaps the easiest part of the ETL process because it involves simply using SSIS to coordinate the execution of the packages in the required order.

If you would like to Enrich your career and get a certified professional, then visit Mindmajix - A Global online training platform: “SQL Server Integration Services Training”  Course.  This course will help you to achieve excellence in this domain.

The best practice to do this is to use a master package that executes the child packages, leveraging the Execute Package Task. The determination of precedence is a matter of understanding the overall ETL and the primary-to foreign key relationships in the tables.

SSIS Master Package

The following steps assume that you are building your solution using the project deployment model. With the project deployment model, you do not need to create connections to the other packages in the project. If you are instead deploying your packages to the file system, you need to create and configure File Connection Managers for each child package, as documented in SQL Server our Tutorial Online.

  1. Create a new package in your project called Master_ETL.dtsx.
  2. Drag an Execute Package Task from the SSIS Toolbox into the Control Flow.
  3. Double-click the Execute Package Task to open the task editor.
  4. On the Package property page, leave the ReferenceType property set to Project Reference. For the PackageNameFromProjectReference property, choose the ETL_DimSalesTerritory.dtsx package.
  5. Your Execute Package Task will look like the one pictured in the below screenshot.

Execute Package Task Editor

The ETL packages for the dimension tables are executed, followed by the fact table ETL and concluding with the cube processing. The master package for the examples in this Loading a Data Warehouse Topic is shown in the below screenshot.

ETL Packages

The related packages are grouped with Sequence containers to help visualize the processing order. In this case, the Dim Sales Territory package needs to be run before the Dim Employee package because of the foreign key reference in the DimEmployee table. Larger solutions will have multiple dimension and fact packages.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses:

 SSRS  Power BI
 SSAS  SQL Server
 SCCM  SQL Server DBA
 SharePoint  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator

 

About Author

author
NameRavindra Savaram
Author Bio

 

Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.