Today, I came across a post in the SSIS forum today where Ash was trying to get the Max Value from a Varbinary column in a Table and update another Varbinary column in a second table. The poster was trying to use 2 execute SQL Task; but was having hard time to map the Varbinary value to a SSIS variable.
After doing some tests, I came to the conclusion that the range of data types available for SSIS variables does not offer a simple way of accomplishing this. If you decide to use an Object variable to place the value in the first Execute SQL Task; then you won't be able to reference that variable in the second Execute SQL Task. You could try to use the T-SQL cast function in the Select and Update statements to convert the varbinary value back and forth, to let's say string or integer; but this could open the door to other data conversion/lost issues.
Looking again at the requirement, I decided to give it a second try using a dataflow, as the Data Flow has a richer range of data types. In our case, it seems like DT_BYTES would be able to accept the Varbinary value with no problem. Then, to suffice our requirement all we need is an OLE DB Source component and an OLE DB Command transformation. As I said it before, OLE DB Transformation is very expensive from the performance stand point as the command is executed for every row passing through the pipeline; but in this case the statement will be executed only once.
Here, a couple of screen shots of the package:
