Microsoft SSIS - DataVault - Hub-Sat load package¶
In this example the Hub-Sat load package for the DataVault solution is described.
See DWH Model
A SSIS package is created with the following components (note the @XGenXmlSection annotations in the description fields of various components):
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.
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.
Entity_name placeholders are used.
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.
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.
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.
Fail on no match, (since Hub key cannot be found).
Set the input to the template Hub table.
Map KeyAttribute_name for the lookup
Ignore failure, since the Hub-Sat record does not have to exist.
A Query is defined for getting the current (active) records from the Hub-Sat table.
The destination needs to be set to the template Hub-Sat table.
For documentation on templates, please see Template.
See config section in DataVault.
When running CrossGenerate with the given Model, Template and Config, the following packages are created:
Note that the business rules are applied on the SQL statement.
Enddate BusinesVault HS_Customer¶