Take the 2-minute tour ×

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

CASE WHEN (CAST(__$update_mask as int) & 16)>0 THEN 1 ELSE 0 END ScdType2,
CAST(__$update_mask as int) Change,
,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.

share|improve this question
What is the symptom? What do you mean when you say it doesn't work? –  John Saunders Aug 10 '09 at 8:21
The CAST fails for most of the SSIS data types I tried. –  Faiz Aug 10 '09 at 13:59
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer


