<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<# bool is2012 = true; #>
<# string multipleHashName = is2012 ? "MultipleHash2012" : "MultipleHash2008"; #>
<Connections>
<Connection Name="AdventureWorks" ConnectionString="Provider=SQLNCLI10;Server=localhost;Initial Catalog=AdventureWorks2008;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package Name="Foo" ConstraintMode="Linear">
<Tasks>
<Dataflow Name="Dataflow">
<Transformations>
<OleDbSource Name="OLE DB Source" ConnectionName="AdventureWorks">
<DirectInput>SELECT AddressLine1, AddressLine2, City FROM Person.Address</DirectInput>
</OleDbSource>
<CustomComponent Name="MultipleHash" ComponentTypeName="Martin.SQLServer.Dts.MultipleHash, <#=multipleHashName#>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44">
<CustomProperties>
<CustomProperty Name="MultipleThreads" DataType="Int32" TypeConverter="Martin.SQLServer.Dts.MultipleHash+MultipleThread, <#=multipleHashName#>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44">1</CustomProperty>
<CustomProperty Name="SafeNullHandling" DataType="Int32" TypeConverter="Martin.SQLServer.Dts.MultipleHash+SafeNullHandling, <#=multipleHashName#>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44">1</CustomProperty>
</CustomProperties>
<InputPaths>
<InputPath Identifier="Input" OutputPathName="OLE DB Source.Output">
<InputColumns>
<InputColumn SourceColumn="AddressLine1" />
<InputColumn SourceColumn="AddressLine2" />
<InputColumn SourceColumn="City" />
</InputColumns>
</InputPath>
</InputPaths>
<OutputPaths>
<OutputPath Name="HashedOutput" SynchronousInput="Input">
<OutputColumns>
<OutputColumn Name="HashedColumn" DataType="Byte" Length="16">
<CustomProperties>
<Property Name="HashType" DataType="Int32" TypeConverter="Martin.SQLServer.Dts.MultipleHash+HashTypeEnumerator, <#=multipleHashName#>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44">1</Property>
<Property Name="InputColumnLineageIDs" ContainsId="true" DataType="String" TranslateValueToLineageId="true" LineageIdListSeparator=",">AddressLine1,AddressLine2,City</Property>
</CustomProperties>
</OutputColumn>
</OutputColumns>
</OutputPath>
</OutputPaths>
</CustomComponent>
<OleDbDestination Name="Destination" ConnectionName="AdventureWorks">
<InputPath OutputPathName="MultipleHash.HashedOutput" />
<ExternalTableOutput Table="[Person].[AddressWithHash]" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
This sample extracts 3 column values from the AdventureWorks2008.Person.Address table. It then hashes these values using the (http://ssismhash.codeplex.com/). To do this the Biml CustomComponent tag is used. Finally the originally 3 column values and the new HashedColumn value are loaded into a Person.AddressWithHash table that has the appropriate schema definition.
Note that there are simpler approaches to including custom components in Biml code. These require a bit more upfront investment to reduce cost of reuse. These will be covered in other samples and walkthroughs.
EDIT: This sample has been updated to make it both simpler and version agnostic. Toggle the is2012 flag to set your version.
Comments
lbilali
1:01pm 02.29.16
Hi Scott,
I'm trying to create a custom Component which has some Custom Properties expecting XML as value.
The XML has LineageId internally So hte property must have a ContainsId="true" otherwise it fails. MY problem is that BIML engine is wrapping the xml with "#{}" even if I specify TranslateValueToLineageId="false".
Is there any way to tell the engine to add the containsID="true" attribute without wrapping the value with #{} but use it as it is?