Master ETL Package (BBDW_ETL.dtsx)

The deployment utility runs an SSIS package, BBDW_ETL.dtsx. As the master package, BBDW_ETL.dtsx runs other SSIS packages for facts and dimensions. The flow for BBDW_ETL.dtsx is broadly divided into ETL Pre-Processing, Sub-Package Processing, Sub-Package Processing Extend and ETL Post-Processing. For the purposes of extending ETL, the key part of the flow is Sub-Package Processing Extend.

This section discusses a Blackbaud Product Development SSIS Package. This Package, along with other out-of-the box Packages are overwritten during deployment. To extend ETL, you should only add extension packages and not modify out-of-the-box packages. The master ETL package, BBDW_ETL.dtsx, runs out-of-the-box packages and extension packages.

The Sub-Package Processing Extend Sequence reads a file identified by a connection manager, BBETL_File_PackageList_EXT. BBETL_File_PackageList_EXT manages a connection to a flat file that maintains a list of SSIS Packages that extend the ETL process. The file connected by BBETL_File_PackageList_EXT and how to update it is discussed in SSIS Deployment Files. But the information contained in the file is comma-separated lines that list SSIS Package files and whether the SSIS package is enabled.

The corresponding Sequence and connection manager for Blackbaud Product Development packages follows a similar pattern for versions 2.94 and before.

From BBDW_ETL.dtsx in the Sub-Package Processing Extend Sequence, you can drill from the Control Flow view through Read Dimension Package List File Extend to its Data Flow.

To view the Data Flow, double-click Read Dimension Package List File Extend.

To open the Flat Source File Editor, you can double-click the Package File List Extend data flow component. The Connection Manager page of the editor displays the name of the connection manager. For more information about Flat File Sources, see Microsoft's MSDN article at Flat File Source.

The Columns page of the Flat Source File Editor confirms the items that populate the list.

From the Data Flow, through a related tab called Connection Managers, you view a list of Connection Managers.

To find the name of the file to which BBETL_File_PackageList_EXT connects, look on General in File name.

Having obtained a list of SSIS packages with Read Dimension Package List File Extend, the Sub-Package Processing Extend Sequence proceeds to a ForEach loop that loops through each package in the list. The Sequence loop logs the start of the Package through a task called Log ETL Child Start. The loop executes each package through a task and a connection called BBETL_Package. Finally, the loop logs the end of the Package.