Using Custom Components

gravatar

Scott Currie

Demonstrates how to use the SSIS Multiple Hash custom component (http://ssismhash.codeplex.com/) - SSIS 2012.

published 09.17.12

last updated 06.24.13


Share

                            


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

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

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?