Monday, September 17, 2012

BIML MD5 ScriptComponent Hash

Demo biml using the ScriptComponent to create an MD5 hash, very handy if you are not allowed to use open source altenatives like ==> http://ssismhash.codeplex.com/
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
        <Annotation>
            File: MD5.ScriptComponent.biml
            Demo biml using the ScriptComponent to
            create an MD5 hash.
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
</Annotations>
<Connections>
<OleDbConnection
    Name="CnOleDBAdventureWorks2012"
    ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
</Connections>
   
<ScriptProjects>                       
    <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SC Add MD5">
        <AssemblyReferences>
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
            <AssemblyReference AssemblyPath="System.dll" />
            <AssemblyReference AssemblyPath="System.AddIn.dll" />
            <AssemblyReference AssemblyPath="System.Data.dll" />
            <AssemblyReference AssemblyPath="System.Xml.dll" />
        </AssemblyReferences>
        <Files>
            <File Path="AssemblyInfo.cs">
            using System.Reflection;
            using System.Runtime.CompilerServices;
            [assembly: AssemblyTitle("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyDescription("")]
            [assembly: AssemblyConfiguration("")]
            [assembly: AssemblyCompany("Ciber Nederland")]
            [assembly: AssemblyProduct("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
            [assembly: AssemblyTrademark("")]
            [assembly: AssemblyCulture("")]
            [assembly: AssemblyVersion("1.0.*")]
            </File>
            <File Path="ScriptMain.cs">
            using System;
            using System.Data;
            using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
            using Microsoft.SqlServer.Dts.Runtime.Wrapper;
            using System.Security.Cryptography;
            using System.Text;
            using System.IO;
            [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
            public class ScriptMain : UserComponent
            {
            static public string GetMd5Sum(string str)
            {
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i != result.Length; i++)
            {
            sb.Append(result[i].ToString("X2"));
            }
            return sb.ToString();
            }
            public override void PreExecute()
            {
            base.PreExecute();
            }
            public override void PostExecute()
            {
            base.PostExecute();
            }
            public override void Input0_ProcessInputRow(Input0Buffer Row)
            {
            Row.MD5AllColumns = GetMd5Sum(Row.FirstName + Row.LastName);
            }
            }
            </File>
        </Files>
<InputBuffer Name="Input0">
    <Columns>
        <Column Name="MD5AllColumns" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="FirstName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="LastName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
    </Columns>
</InputBuffer>
    <OutputBuffers>
        <OutputBuffer Name="Output0">
    </OutputBuffer>
</OutputBuffers>
</ScriptComponentProject>
</ScriptProjects>
    <Packages>
        <Package  ConstraintMode="Parallel" Name="MD5.ScriptComponent">
        <Variables>
                <Variable Name ="RowCount"
                          DataType="Int32"
                          EvaluateAsExpression="false">0</Variable>
                <!--Stock symbol has to be enclosed in double quotes-->
            </Variables>
            <Tasks>
                <Container ConstraintMode ="Parallel" Name="SEQ Main">
                    <Tasks>
                        <Dataflow Name="DFT Main">
                            <Transformations>
                                <OleDbSource Name="ODS Adventureworks Person" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableInput Table = "[Person].[Person]"></ExternalTableInput>
                                </OleDbSource>
                                <DerivedColumns Name="DC MD5">
                                <InputPath OutputPathName="ODS Adventureworks Person.Output"></InputPath>
                                <Columns>
                                    <Column Name="MD5AllColumns" DataType="AnsiString" Length="50">"--"</Column>                                   
                                </Columns>
                            </DerivedColumns>
                                <ScriptComponentTransformation Name="SC Add MD5">
                                <InputPath OutputPathName="DC MD5.Output"></InputPath>
                                <ScriptComponentProjectReference ScriptComponentProjectName="SC Add MD5" />                               
                            </ScriptComponentTransformation>
                            <!-- Dummy Rowcount to show results in dataviewer -->
                            <RowCount VariableName="User.RowCount" Name="RC Input">
                                <InputPath OutputPathName="SC Add MD5.Output0"></InputPath>
                            </RowCount>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Will get you:
image
Till Next Time

1 comment:

  1. Hi John, thank you for taking the time to do this. Do you by any chance have the sample package? I am trying to achieve the same result but the BIML script for the hash is failing.

    ReplyDelete