<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?