Extending the component

[ccote-2]

Hi, we're using the multiple hash component in our data warehouse SSIS project. Our data warehouse is a data vault and the best practice for hashing using this technique is to remove the trailing NULL values as well as adding a delimiter (like pipe delimiter |) between each columns. How does it work if we want to add these options to the multiple hash component? Can we add it to the git code or do we have to maintain a separate code base? Also, are there any licensing implied into doing this?

Thanks,
Christian Cote

[keif888]

I would challenge that best practice, as described, but only where using Multiple Hash for ALL hash generation within your solution. The best practice is there for cases where you are using HashBytes (or it's equivalent in other DBMS').

On face value, as described, it will increase your risk of collisions due to dissimilar records generating the same hash.
In my view what is suggested will result in the following scenario where dissimilar records generate the same hash value:

Col 1	Col 2	Col 3	Hash
Text	NULL	Text 2	0x123456
Text	Empty String	Text 2	0x123456

If you are writing your own hashing from scratch, or using hashbytes, then other than empty string, that best practice will reduce the risk of dissimilar records generating the same hash. You need to include replacement of NULL with something that you can guarantee will never appear in your data to be safe.

Col 1	Col 2	Col 3	Hash
Text	NULL -> Never In Text	Text 2	0xab4f35
Text	Empty String	Text 2	0x123456

See Best practices on developing Data Vault in SQL Server (including SSIS)

Version 1.3 and greater of my component has a "Safe Null Hashing" option that is enabled by default to prevent this type of issue.

My component generates a byte array of the input to hash by repeating the following for each input column in the hashed field:

1. Append input column content to a data byte array, ignoring null columns, and formatting all input data into maximum precision
2. Append Null indicator to indicator byte array
3. If the input column is variable length, append the length of the field to the indicator byte array

This guarantees that there can be no duplicate hash values generated for input data (with the exception of hash collisions), and is a better solution than the "best practice".

That being said, if you really want an option added to support Pipe delimiting with null replacement hashing, which would have to be mutually exclusive of Safe Null Handling, it can be done.

It would require an increment to the version (1.8), as this will need a new property added (or the SafeNullHandling property updated to support On, Off and DataVault.), which needs the upgrade to execute when opening an old package.
The CaclulateHash function in Utility.cs will need updating to support creating the inputByteBuffer in the correct format.
And of course the UI's need updating to support the new option.

The license doesn't prevent changes, (fork or pull), or running your own repository (clone). You just have to use the same license...