How to CAST SQL Server type varbinary to integer in SSIS?

Question

I need to use a binary data in a transformation as an integer and do some manipulation, but how to do the casting? I tried many SSIS data types but no luck. By the way I do not want to use script task.

---

[Updated section below]

The data is the values stored in the __$update_mask column of the CDC function fn_cdc_get_net_changes. Following is the CAST I am attempting in SSIS,

(__$operation == 4) && (((DT_I8)__$update_mask & @[User::SCDColumnNumber]) > 0)

The corresponding TSQL code will be

SELECT 
CASE WHEN (CAST(__$update_mask as int) & 16)>0 THEN 1 ELSE 0 END ScdType2,
CAST(__$update_mask as int) Change,
* FROM 
cdc.fn_cdc_get_net_changes_dbo_Transactions
(sys.fn_cdc_get_min_lsn('dbo_Transactions')
,sys.fn_cdc_get_max_lsn(),'all with mask')

which is working fine. But the SSIS CAST is not. Hope this gives enough information about my query.

Answer 1

What is in the varbinary? Is it of variable length? How do you know there are integers in that data?

Answer 2

The __$update_mask column is of type varbinary, length 128 bytes. There is no way it can be casted (without loss of information) to INT or BIGINT as the cast destination sizes are 4 and 8 bytes respectively. Though I wanted to consider only first 8 bytes of the 128 bytes. But again, I couldn't find any supported data type or casting in SSIS that can do the job. Though this can be done in DB engine and brought to SSIS later.