Skip to content

Microsoft SSIS - DataVault - Hub-Sat load package

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

Model

See DWH 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

Hub Lookup
General

Fail on no match, (since Hub key cannot be found). Lookup General

Connection

Set the input to the template Hub table. Lookup Connection

Columns

Map KeyAttribute_name for the lookup Lookup Connection

Hub-Sat Lookup
General

Ignore failure, since the Hub-Sat record does not have to exist. Lookup General

Connection

A Query is defined for getting the current (active) records from the Hub-Sat table. Lookup Connection

Columns

Lookup Columns

Conditional Split

Conditional Split

Destination
Connection Manager

The destination needs to be set to the template Hub-Sat table. Destination connection manager

Mappings

Template Destination Mappings

Documentation

For documentation on templates, please see Template.

Config

See config section in DataVault.

Output

When running CrossGenerate with the given Model, Template and Config, the following packages are created:

  • Load_BusinessVault_HS_Country.dtsx
  • Load_BusinessVault_HS_Customer.dtsx
  • Load_BusinessVault_HS_Order.dtsx

Load_BusinessVault_HS_Customer.dtsx

Control Flow

Output Control Flow

Data Flow

Output Data Flow

Source
Connection Manager

Note that the business rules are applied on the SQL statement. Output Source Connection Manager

Columns

Output Source Columns

Lookup
Connection

Output Lookup Connection

Columns

Output Lookup Columns

Enddate BusinesVault HS_Customer
SQL Command

Output Enddate SQL Command

Column Mappings

Output Enddate Columns

Destination
Connection Manager

Output Destination Connection Manager

Mappings

Output Destination Mappings