BIML code to generate a HASHBYTE MD5 column

Question

I am getting an error with the following section of the code which deal with the HASHBYTE logic.

<ScriptComponentTransformation Name ="SCR HashByte Data Thread 1">

<ScriptComponentProject>
<ScriptComponentProject Name="SCRHashByteDataThread1">

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

<InputBuffer Name="Input0">
<Columns>
<Column Name="RowNumber" DataType ="Int64" UsageType ="ReadWrite"></Column>
</Columns>
</InputBuffer>

<OutputBuffers>

<OutputBuffer Name="Output0">
<Columns>
<Column Name="MD5" DataType ="AnsiStringFixedLength"></Column>
</Columns>
</OutputBuffer>
</OutputBuffers>

<Files>
<File Path ="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_AddRowNumber")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("SC_AddRowNumber")]
[assembly: AssemblyCopyright("Copyright @ Homeserve 2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]

</File>



<File Path ="main.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Text;
using System.IO;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private System.Security.Cryptography.MD5 _md5 =new System.Security.Cryptography.MD5CryptoServiceProvider();

    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }


    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        var errorMsg = string.Empty;
        byte[] hashBytes;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        try
        {
            string hashSource =
            (Row.ColA_IsNull ? "" : Row.ColA.ToString())
            + (Row.ColB_IsNull ? "" : Row.ColB.ToString())
            + (Row.ColC_IsNull ? "" : Row.ColC.ToString()));

            hashBytes = _md5.ComputeHash(
            System.Text.UnicodeEncoding.Unicode.GetBytes(hashSource));

            for (int i = 0; i <![CDATA[<]]> hashBytes.Length; i++)
            {
                sb.Append(hashBytes[i].ToString("X2"));
            }

            //Row.MD5 = "0x" + sb.ToString();
        }
        catch (Exception e)
        {
            errorMsg = e.Message;
        }
        finally
        {
            Row.MD5 = "0x" + sb.ToString();
        }

        if (errorMsg != null <![CDATA[&&]]> errorMsg != string.Empty)
            Row.MD5 = "Error";
    }
} 

</File>
</Files>



</ScriptComponentProject>
</ScriptComponentProject>
</ScriptComponentTransformation>-->

What's the error? Mental parse, it looks like the supplied code won't run because you're referencing `Row.ColA` but have only specified that the column `RowNumber` is available to the script — billinkc, Jan 21 '15 at 16:01
Hi, The error I am getting are " ;Expected" "invalid Expression Term ')'" "Internal COmpiler Error: Workflow EmitSsis contains fatal erros.Phase execution halted" — Aarion, Jan 21 '15 at 16:03
Is there anyway I could upload the SSIS package I have made if that helps? — Aarion, Jan 21 '15 at 16:28
The beautiful thing about biml is that you don't need to upload a package, I can see what you're doing. Unfortunately, don't think I'll have time to look into this today. General advice would be to reduce your logic of `Input0_ProcessInputRow` down to just `Row.MD5 = "Test";` If that works, slowly add code back in until it works or you've found the problem — billinkc, Jan 21 '15 at 17:16
Thank you for having a look. I will try and see if that get's it working. — Aarion, Jan 21 '15 at 17:23
I have not managed to get this to work as yet. I added Row.MD5 to the code — Aarion, Jan 21 '15 at 23:27
You should edit your question and add the error you are getting. **Use Copy/paste!** I seriously doubt those spelling errors did appear in the actual message. — Jongware, Jan 23 '15 at 14:41
Have you tried making an SSIS package from scratch and creating a `Script Component` with this code? Once it works as a normal package (With the added benefit of a much more useful debugger) you should be able to transfer to your Biml with ease. — iamdave, Oct 02 '17 at 08:14

sorrell · Answer 1 · 2015-01-22T18:16:44.070

At first glance, your hashSource has too many closing parentheses at the end.

If that's still not it, I do something similar in a DLL that I call into from my SSIS package. Maybe this will help:

        byte[] bs = System.Text.Encoding.ASCII.GetBytes(hashSource);
        bs = _md5.ComputeHash(bs);
        System.Text.StringBuilder s = new System.Text.StringBuilder();
        foreach (byte b in bs)
        {
            s.Append(b.ToString("x2").ToLower());
        }
        string md5hash = s.ToString();

Keep this madness in mind when you compare .Net and SQL Server hashes: http://weblogs.sqlteam.com/mladenp/archive/2009/04/28/Comparing-SQL-Server-HASHBYTES-function-and-.Net-hashing.aspx

Hi SOrrell, Thank you very much. I iwll try this today and get back to you . Apologies for the delay in responding to you, I had not received a notification for this. — Aarion, Jan 26 '15 at 12:23

BIML code to generate a HASHBYTE MD5 column

1 Answers1