Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it reliable to compare HASHBYTES for equality when CAST as VARCHAR? Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 11:55 AM
Points: 24, Visits: 194
SSIS does not allow comparison of the varbinary data type; the output of HASHBYTES. So if I want to compare source and destination HASHBYTES within the Conditional Split component I must convert to string, e.g. (DT_STR,8000,1252)SourceHashbytes != (DT_STR,8000,1252)DestinationHashbytes. In this case, I'm using SHA1 so SourceHashbytes and DestinationHashbytes are varbinary(20).

I'm not sure about a few things:
- What's the max varchar (DT_STR) length I need to use in order to compare varbinary(20) when it is CAST as a string, i.e. what does varbinary(20) translate to as a string? I've looked at the string output and I'm definitely not in danger of exceeding 8000 characters/appx bytes. The lengths do vary, however, and I'd like to understand how that works.
- Is this a reliable comparison, i.e. will the varchar conversion output always contain reproducible, unique values for the varbinary input?
- Am I taking it on the performance nose forcing the compare in SSIS using strings; would it be better to do it in t-sql (where I would now have to deal with crossing source/destination boundaries in the same stored proc)?

Any insight appreciated, thanks.
Post #1442998
Posted Wednesday, April 17, 2013 1:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 1,167, Visits: 1,206
Bruce Hendry (4/16/2013)
SSIS does not allow comparison of the varbinary data type; the output of HASHBYTES. So if I want to compare source and destination HASHBYTES within the Conditional Split component I must convert to string, e.g. (DT_STR,8000,1252)SourceHashbytes != (DT_STR,8000,1252)DestinationHashbytes. In this case, I'm using SHA1 so SourceHashbytes and DestinationHashbytes are varbinary(20).

I'm not sure about a few things:
- What's the max varchar (DT_STR) length I need to use in order to compare varbinary(20) when it is CAST as a string, i.e. what does varbinary(20) translate to as a string? I've looked at the string output and I'm definitely not in danger of exceeding 8000 characters/appx bytes. The lengths do vary, however, and I'd like to understand how that works.
- Is this a reliable comparison, i.e. will the varchar conversion output always contain reproducible, unique values for the varbinary input?
- Am I taking it on the performance nose forcing the compare in SSIS using strings; would it be better to do it in t-sql (where I would now have to deal with crossing source/destination boundaries in the same stored proc)?

I do the same thing in my SSIS packages; SHA1 produces varbinary(20) which would convert to a string that's 40 characters long (two characters to represent one byte (00 to FF).

From a conditional split:
Saved_Type1Hash != (DT_WSTR,40)Calc_Type1Hash

HTH,
Rob
Post #1443476
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse