Skip to content

Microsoft SSIS - DataVault - Hub load package

In this example the Hub 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):

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.

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


The lookup is used to ignore records that already exist.


Redirect missing records to no match output. Lookup General


Set the input to the template Hub table. Lookup Connection


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.

Connection Manager

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


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


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:

  • Load_BusinessVault_H_Country.dtsx
  • Load_BusinessVault_H_Customer.dtsx
  • Load_BusinessVault_H_Order.dtsx


Control Flow

Output Control Flow

Data Flow

Output Data Flow

Connection Manager

Output Source Connection Manager


Output Source Columns


Output Lookup Connection


Output Lookup Columns


Output Lookup Advanced

Connection Manager

Output Destination Connection Manager


Output Destination Mappings