Multiple Hash generator for SQL Server 2012

Introduction

In this blogpost I'll test the Multiple Hash generator Component for SQL Server 2012. This component calculates a hash value for one or more fields. With the Multiple Hash SSIS component you can generate all kind of different hashes: MD5, RipeMD160, SHA1, SHA256, SHA384 and SHA512. It's also possible to create multiple threading in case you have multiple cores on your system available.

Installation

The installation process starts with downloading the tool from Codeplex. An installer will install the component in the SSIS toolbox.

Next...

I Agree!

Close and after a refresh of the toolbox the component is finally there.

Working with Multiple Hash in SSIS
In this section I'll describe the testruns i've done with the Multiple Hash function. First i've created an testscript, i've tested an initial run and a second run. In the second run I've changed the order of the fields used for the hash calculation.

Testrun 1


USE [SSISMultipleHash]
GO


DROP TABLE [dbo].[IN]
DROP TABLE [dbo].[OUT]


CREATE TABLE [dbo].[IN](
[Test1] [nchar](10) NULL,
[Test2] [nchar](10) NULL,
[Test3] [tinyint] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[OUT](
[Test1] [nchar](10) NULL,
[Test2] [nchar](10) NULL,
[Test3] [tinyint] NULL,
[Hash] [varchar] (50) NULL
) ON [PRIMARY]
GO


INSERT INTO [IN] VALUES (NULL, NULL, NULL)
INSERT INTO [IN] VALUES ('test', NULL, NULL)
INSERT INTO [IN] VALUES ('Test', NULL, NULL)
INSERT INTO [IN] VALUES ('test', 'test', NULL)
INSERT INTO [IN] VALUES ('Test', 'Test', NULL)
INSERT INTO [IN] VALUES ('test', 'tEst', NULL)
INSERT INTO [IN] VALUES ('test', 'test', 1)
INSERT INTO [IN] VALUES ('test', 'test', 11)

I've created the following SSIS package Data Flow Task:

These are the input fields of the Multiple Hash function:

The output fields are shown below. In this case there is only one output field "hash" and it uses all three of the input fields to calculate the hash. The sorting is Test1, Test2 and Test3

Resulting in

Testrun 2
In the next testrun i've changed the sequence of the fields. The Multiple hash function calculates now the fields in a different ordering. The sorting is now Test3, Test2 and Test1.

Resulting in:

The result of Multiple Hash generator component are different. The hash values were calculated because of change of order in the fields. In case of the three NULLs for all of the fields there isn't a difference between the testrun 1 and tesrun 2 (offcourse).


Conclusion
This seems a powerful third party component of SSIS for calculating hashvalues. The only concern I can imagine is the future support in SQL Server 2015 (or 2014?). But the code seems available on codeplex. But, knowledge on how to change the component is inevitable.

Greetz,

Hennie