IsBig Property of Aggregate Transformation in SSIS 2008 R2


I dunno how many of you guys are aware of the property IsBig in SSIS Aggregate Transformation. If you are aware of it then just skip this post else worth reading this. As you all aware that using aggregate we can calculate SUM,AVG,COUNT and COUNT DISTINCT of a column or columns. In some cases the return value will be soo big like more than 4 billion and to handle those kind of outputs we need this IsBig Property. This can be 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. The set the property, follow the steps.

  1. Right click on Aggregate transformationa nd select Show Advanced Editor.
  2. Go to Input and Output columns tab.
  3. Expand the Output columns and select the column to which you wish to set this property.

Using this property will have the following impact on Output data types flowing through Aggregate transformation.

  • DT_UI8 data type will be used for COUNT results.
  • DT_UI4 data type will be used for DISTINCT COUNT result.
  • DT_R8 data type will be used for remaining operations.

As I mentioned above, this can be used only for the above given operations. For Group By,Maximun and Minimum we can’t set this property.
Try it guys !!

Roopesh Babu V