Skip to content

Microsoft SSIS - DataVault - Hub load package

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

Model

See DWH Model

Template

Load_Entity_owner_H_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 H_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 determine if the record already exists.
  • A Derive Column component to add the LoadDateTime as a column to the output.
  • A OLE DB Destination component to write the data to the Destination table.

Again the Entity_owner and Entity_name placeholders are used.

Template Data Flow

In this example all components are covered that require CrossGenerate specific settings.

Source
Connection Manager

In the connection manager window a SQL statement is constructed that implements the source to target mapping including any business logic that is applied. The functions implementing the business logic are covered in the Business Logic example. Note that the SQL statement consists of several sections, annotated with the @XGenTextSection annotation. The text section functionality of CrossGenerate is nested in an XML template.

Template Source Connection Manager

Input and Output Properties - External Columns

When opening the Advanced Editor for the source component we can go to the 'Input and Output Properties' tab to find the 'KeyAttribute_name' column in the 'External Columns' list. The defined output column needs to be repeated for every key attribute in our model, so we set the section named 'KeyAttribute' on the [External Columns/attribute_name] element. This way CrossGenerate knows which part in the template to repeat (in this case the [KeyAttribute_name] column).

Template Source Input and Output Properties - External Columns

Input and Output Properties - Output Columns and Error Columns

We do the same for the 'Output Columns' on the Source Output and Source Error Output, since this column also needs to be repeat for every key attribute defined in the model.

Template Source Input and Output Properties - Output Columns Template Source Input and Output Properties - Error Columns

Lookup

The lookup is used to ignore records that already exist.

General

Redirect missing records to no match output. Lookup General

Connection

Set the input to the template Hub table. Lookup Connection

Columns

Map KeyAttribute_name for the lookup. Lookup Connection

Input and Output Properties - Lookup Input - Input Columns

Using advanced editor to set a section annotation in the input column KeyAttribute_name. Input columns

Derived column

The derived colum transformation does not need any specific configuration for CrossGenerate.

Destination
Connection Manager

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

Mappings

Template Destination Mappings

Input and Output Properties - External Columns

Template Destination Input and Output Properties - External Columns

Input and Output Properties - Input Columns

Template Destination Input and Output Properties - Input Columns

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_H_Country.dtsx
  • Load_BusinessVault_H_Customer.dtsx
  • Load_BusinessVault_H_Order.dtsx

Load_BusinessVault_H_Customer.dtsx

Control Flow

Output Control Flow

Data Flow

Output Data Flow

Source
Connection Manager

Output Source Connection Manager

Columns

Output Source Columns

Lookup
Connection

Output Lookup Connection

Columns

Output Lookup Columns

Advanced

Output Lookup Advanced

Destination
Connection Manager

Output Destination Connection Manager

Mappings

Output Destination Mappings