SQL Server Integration Services - Incremental data load hash comparison

Question

Using SQL Server Integration Services (SSIS) to perform incremental data load, comparing a hash of to-be-imported and existing row data. I am using this:

http://ssismhash.codeplex.com/

to create the SHA512 hash for comparison. When trying to compare data import hash and existing hash from database using a Conditional Split task (expression is NEW_HASH == OLD_HASH) I get the following error upon entering the expression:

The data type "DT_BYTES" cannot be used with binary operator "==". The type of one or both of the operands is not supported for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempts at casting each column to a string (DT_WSTR, 64) before comparison have resulted in a truncation error.

Is there a better way to do this, or am I missing some small detail?

Thanks

Answer 1

This post is older but in order to help other users...

The answer is that in SSIS you cannot compare binary data using the == operator.

What I've seen is that people will most often convert (and store) the hashed value as varchar or nvarchar which can be compared in SSIS.

I believe the other users have answered your issue with "truncation" correctly.

Answer 2

Have you tried expanding the length beyond 64? I believe DT_BYTES is valid up to 8000 characters. I verified the following are legal cast destinations for DT_BYTES based on the books online article:

1. DT_I4
2. DT_UI4
3. DT_I8
4. DT_UI8
5. DT_STR
6. DT_WSTR
7. DT_GUID
8. DT_IMAGE

I also ran a test in BIDS and verified it had no problem comparing the values once I cast them to a sufficiently long data type.

Answer 3

SHA512 is a bit much as your chances of actually colliding are 1 in 2^256. SHA512 always outputs 512 bits which is 64 bytes. I have a similar situation where I check the hash of an incoming binary file. I use a Lookup Transformation instead of a Conditional Split.