SSIS IsBig… Really Big… I mean HUGE!

I’ll be blogging some on SSIS as I now have a new gig slinging data hither and yon. Duck, you don’t want to get a DT_I8 in the face.

So, today I was bitten by the IsBig flag on DataFlow Aggregate Transformation. This flag is notated in the BOL:

A column may contain numeric values that require special consideration because of their large value or precision requirements. The Aggregation transformation includes the IsBig property, which you can set on output columns to invoke special handling of big or high-precision numbers. If a column value may exceed 4 billion or a precision beyond a float data type is required, IsBig should be set to 1.

So, the result here is a column that is Input to the Aggregate Transformation as DT_I4 and has IsBig = 1 WILL be Output DT_I8, and the IDE will not allow the otuput datatype to be changed with Advanced Editor!

This bit me with one column in a large Aggregate somehow getting set IsBig and messing up the downstream metadata. Several different looks at the issue were all proving dead ends.

Next time you run into a metadata mismatch, especially if its a DT_I4 or DT_R4 turning into their 8-byte counterparts, remember, you need to tap your inner IsBig and see the light!