Microsoft SSIS - DataVault - Hub load package¶

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

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