Checksum Transformation

By Konesans Ltd
Version 2005
Level Beginner

The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection.

As featured in The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite from the Kimbal Group. Have a look at the book samples especialy Sample package for custom SCD handling.

All input columns are passed through the transformation unaltered, those selected are used to generate the checksum which is passed out through a single output column, Checksum. This does not restrict the number of columns available downstream from the transformation, as columns will always flow through a transformation. The Checksum output column is in addition to all existing columns within the pipeline buffer.

The Checksum Transformation uses an algorithm based on the .Net framework GetHashCode method, it is not consistent with the T-SQL CHECKSUM() or BINARY_CHECKSUM() functions. The transformation does not support the following Integration Services data types, DT_NTEXT, DT_IMAGE and DT_BYTES.

ChecksumAlgorithm Property supports the ChecksumAlgorithm enumeration. The new algorithm introduced in version 1.3.0 is still supported for backward compatibility as ChecksumAlgorithm.Original (0). The default algorithm is ChecksumAlgorithm.Checksum (1).

The component is provided as an MSI file, however to complete the installation, you will have to add the transformation to the Visual Studio toolbox by hand. This process has been described in detail for the Trash Destination, just select Checksum from the SSIS Data Flow Items list in the Choose Toolbox Items

Download & Version History

Version 1.4.0.0 - Installer refresh only.
(22 Dec 2007)

Version 1.4.0.0 - Refresh for minor UI enhancements.
(5 Mar 2006)

Version 1.3.0.0 - SQL Server 2005 RTM. The checksum algorithm has changed to improve cardinality when calculating multiple column checksums. The original algorithm is still available for backward compatibility. Fixed custom UI bug with Output column name not persisting.
(10 Nov 2005)

Version 1.2.0.1 - SQL Server 2005 IDW 15 June CTP. A user interface is provided, as well as the ability to change the checksum output column name.
(29 Aug 2005)

Version 1.0.0 - Public Release (Beta).
(30 Oct 2004)

Screenshot