Microsoft SSIS - DataVault¶
This example shows how you can generate a DataVault using CrossGenerate. If you haven't done already, have a look at the Simple Staging example first to read up on the CrossGenerate template- and config concepts used in this example.
The implementation covers Hubs, Links and Satelites for Hubs and Links. The Hub and Hub-Sat loading package templates are explained in detail in subsequent pages as well as how to create a master package. Since Link and Link-Sat are very similar to Hub and Hub-Sat (with regards to CrossGenerate concepts used) these packages are not explained in separate pages but are included in the downloadable example.
Model¶
In this example we will be generating DataVault packages for entities defined in the DWH model.
Template¶
Load_Entity_owner_H_Entity_name.dtsx¶
Please see Hub load package.
Load_Entity_owner_HS_Entity_name.dtsx¶
Please see Hub-Sat load package.
Load_DWH_DataVault.dtsx¶
Please see Master package.
Documentation¶
For documentation on templates, please see Template.
Config¶
Besides the concepts introduced in the Simple Staging example this config introduces the feature to use text templates within an XML template.
As can be seen in the TextTemplates section, a node in the xml template can be marked as text template, which results in the node's context being interpreted similar to a text template. Within the node, text sections can then be defined using the @XGenTextSection annotation. Sections can also be defined in the config by adding TextSections to the TextTemplate node, as is done in the last TextTemplate definition in the config below.
Full config example for Hub and Hub-Sat¶
<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
<Model>
<ModelAttributeInjections>
<!-- Translate the SQL data type into the SSIS data type and store it in the 'etldatatype' attribute on the attribute elements in the model. -->
<ModelAttributeInjection modelXPath="//attribute[@datatype='varchar']" targetAttribute="etldatatype" targetValue="str"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='varchar']" targetAttribute="codePage" targetValue="1252"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='nvarchar']" targetAttribute="etldatatype" targetValue="wstr"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='tinyint']" targetAttribute="etldatatype" targetValue="i2"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='smallint']" targetAttribute="etldatatype" targetValue="i2"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='bigint']" targetAttribute="etldatatype" targetValue="i8"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='int']" targetAttribute="etldatatype" targetValue="i4"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='timestamp']" targetAttribute="etldatatype" targetValue="bytes"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='datetime']" targetAttribute="etldatatype" targetValue="dbTimeStamp"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='datetime2']" targetAttribute="etldatatype" targetValue="dbTimeStamp"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='decimal']" targetAttribute="etldatatype" targetValue="numeric"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='bit']" targetAttribute="etldatatype" targetValue="bool"/>
<ModelAttributeInjection modelXPath="//attribute[@datatype='uniqueidentifier']" targetAttribute="etldatatype" targetValue="guid"/>
</ModelAttributeInjections>
</Model>
<XmlTemplate rootSectionName="Entity">
<FileFormat currentAccessor="_" commentNodeXPath="@description" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
<Output type="output_per_element" />
<XmlSections>
<!-- The input columns of the 'OLE DB Destination Input' need to be repeated for every attribute. -->
<!-- This element doesn't have a 'Description' attribute, so we need to create this section in this config file. -->
<!-- This also applies to input columns for a union all -->
<XmlSection name="KeyAttribute" templateXPath="//input[@name='Lookup Input']/inputColumns/inputColumn[@cachedName='KeyAttribute_name']"/>
<XmlSection name="NonKeyAttribute" templateXPath="//input[@name='Lookup Input']/inputColumns/inputColumn[@cachedName='NonKeyAttribute_name']"/>
<XmlSection name="KeyAttribute" templateXPath="//component[@componentClassID='Microsoft.UnionAll']/inputs/input/inputColumns/inputColumn[@cachedName='KeyAttribute_name']"/>
<XmlSection name="NonKeyAttribute" templateXPath="//component[@componentClassID='Microsoft.UnionAll']/inputs/input/inputColumns/inputColumn[@cachedName='NonKeyAttribute_name']"/>
<XmlSection name="Attribute" templateXPath="//input[@name='OLE DB Destination Input']/inputColumns/inputColumn[@cachedName='Attribute_name']"/>
</XmlSections>
<TemplateAttributeInjections>
<!-- Inject an attribute for the scale, precision, cachedScale & cachedPrecision on elements where the name is 'KeyAttribute_name'. -->
<TemplateAttributeInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]" attributeName="scale" attributeValue=""/>
<TemplateAttributeInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]" attributeName="precision" attributeValue=""/>
<TemplateAttributeInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]" attributeName="cachedScale" attributeValue=""/>
<TemplateAttributeInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]" attributeName="cachedPrecision" attributeValue=""/>
</TemplateAttributeInjections>
<TemplatePlaceholderInjections>
<!-- Inject a placeholder for the dataType, length, precision, scale, codePage attributes for the columns. -->
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@dataType" modelNode="etldatatype" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@length" modelNode="length" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@precision" modelNode="precision" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@scale" modelNode="scale" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@codePage" modelNode="codePage" scope="current" />
<!-- Inject a placeholder for the cachedDataType, cachedLength, cachedPrecision, cachedScale, cachedCodePage attributes for the columns. -->
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@cachedDataType" modelNode="etldatatype" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@cachedLength" modelNode="length" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@cachedPrecision" modelNode="precision" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@cachedScale" modelNode="scale" scope="current" />
<TemplatePlaceholderInjection templateXPath="//*[(@name='Attribute_name' or @cachedName='Attribute_name' or @name='KeyAttribute_name' or @cachedName='KeyAttribute_name' or @name='NonKeyAttribute_name' or @cachedName='NonKeyAttribute_name')]/@cachedCodepage" modelNode="codePage" scope="current" />
</TemplatePlaceholderInjections>
<TextTemplates>
<TextTemplate node="//component[@componentClassID='Microsoft.OLEDBSource']/properties/property[@name='SqlCommand']">
<FileFormat currentAccessor="_" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
</TextTemplate>
<TextTemplate node="//component[@name='LKP Entity_owner H_Entity_name']/properties/property[@name='SqlCommandParam']">
<FileFormat currentAccessor="_" />
<TextSections>
<TextSection name="KeyAttribute" begin="[refTable].[KeyAttribute_name]" end="?" prefix="AND " />
</TextSections>
</TextTemplate>
<TextTemplate node="//component[@name='LKP Entity_owner H_Entity_name']/properties/property[@name='ParameterMap']">
<FileFormat currentAccessor="_" />
<TextSections>
<TextSection name="KeyAttribute" begin="#{" end="};" />
</TextSections>
</TextTemplate>
</TextTemplates>
</XmlTemplate>
<Binding>
<SectionModelBinding section="Entity" modelXPath="/modeldefinition/system/mappableObjects/entity" placeholderName="Entity">
<Placeholders>
<Placeholder name="System" modelXPath="../.." />
</Placeholders>
<SectionModelBinding section="Attribute" modelXPath="attributes/attribute" placeholderName="Attribute" />
<SectionModelBinding section="NonKeyAttribute" modelXPath="attributes/attribute[not(boolean(@primary))]" placeholderName="NonKeyAttribute" />
<SectionModelBinding section="KeyAttribute" modelXPath="attributes/attribute[boolean(@primary)]" placeholderName="KeyAttribute">
<Placeholders>
<Placeholder name="Entity" modelXPath="../.." />
</Placeholders>
</SectionModelBinding>
<SectionModelBinding section="MappedKeyAttribute" modelXPath="mappableObjectMappings/mappableObjectMapping/attributeMappings/attributeMapping[boolean(@targetAttributePrimary)]" placeholderName="MappedKeyAttribute" />
<SectionModelBinding section="MappedNonKeyAttribute" modelXPath="mappableObjectMappings/mappableObjectMapping/attributeMappings/attributeMapping[not(boolean(@targetAttributePrimary))]" placeholderName="MappedNonKeyAttribute" />
<SectionModelBinding section="ObjectMapping" modelXPath="mappableObjectMappings/mappableObjectMapping" placeholderName="ObjectMapping" />
</SectionModelBinding>
</Binding>
</XGenConfig>
Documentation¶
For documentation on the configuration, please see Config.
Output¶
Hub load packages¶
- Load_BusinessVault_H_Country.dtsx
- Load_BusinessVault_H_Customer.dtsx
- Load_BusinessVault_H_Order.dtsx
Please see Hub package.
Hub-Sat load packages¶
- Load_BusinessVault_HS_Country.dtsx
- Load_BusinessVault_HS_Customer.dtsx
- Load_BusinessVault_HS_Order.dtsx
Please see Hub-Sat package.
Link load packages¶
- Load_BusinessVault_L_Order_Customer.dtsx
Link-Sat load packages¶
- Load_BusinessVault_LS_Order_Customer.dtsx
Master package¶
- Load_DWH_DataVault.dtsx
Please see Master package.
Attachments¶
The entire DataVault example, including SQL and SSIS templates, can be found in the following zip file: