Microsoft SSIS - DataVault - Hub-Sat load package¶

In this example the Hub-Sat load package for the DataVault solution is described.

Model¶

Template¶

Load_Entity_owner_HS_Entity_name.dtsx¶

A SSIS package is created with the following components (note the @XGenXmlSection annotations in the description fields of various components):

Control Flow¶

On the Control Flow we have a Data Flow Task with the name 'DFT Load Entity_owner HS_Entity_name'. During generation this will be resolved to the proper entity owner and name.

Template Control Flow ]

Data Flow¶

In the DataFlow task of this template we have:

A OLE DB Source component with a query implementing the source to target mapping
A lookup to the target Hub table to obtain the technical Hub key.
A lookup to the target Hub-Sat table to obtain the current hash value (if any).
A Derive Column component to add the LoadDateTime and EndDateTime as a columns to the output.
A Conditional Split to route records (new or modified) to the appropriate next component.
A Multicast for modified records to enable insert of new version and end-date the existing version.
A union all combining the new and modified records for insertion.
An SQL command updating existing records (setting enddate) if a new, changed version is introduced.
A Destination for inserting new and modified records.

Again the Entity_owner and Entity_name placeholders are used.

Template Data Flow

Since setting the appropriate section annotation on input/output and extended columns through the advanced editor has been covered in the Simple Staging and Hub load package examples, we only focus on CrossGenerate settings specific for this Hub-Sat loading pattern.

Source¶

Connection Manager¶

Note that there is a similar but not equal SQL statement when compared to the Hub load template package. The SQL statement in this Hub-Sat template also exposes nonkey columns and creates a hash column for change detection.

Template Source Connection Manager

Input and Output Properties - External Columns, Input and Output columns¶

Compared to the Hub package, the Hub-Sat packages has a NonKeyAttribute_name column next to the KeyAttribute_name column. For both these columns, the section annotation needs to be present in the External, Input and Output columns of each relevant component, as illustrated below for the External Columns of the Source component.

Template Source Input and Output Properties - External Columns