SQL Server 2005 SSIS Checksum Package

Question

Folks,

We're building an ETL process to load mid-size dimensional data warehouse using SQL Server 2005 SSIS on 64bit OS. We're planning to use SSIS's Checksum package to manage SCDs (Slowly Changing Dimensions).

Even though we're doing a proof of concept using SSIS Checksum package, I'm not comfortable using it in real production scenario. I've heard that it is no good for 64bit OS. Also, since it is not out of the box SSIS product, installing that package would be a hassle.

What you guys think? Should I go with old-school T-SQL checksum or give that package a try? Has anyone any experience using it in heavy volume ETL process?

Answer 1

After quick research the checksum transformation package uses an algorithm based on the .Net framework GetHashCode method and it is not consistent with the T-SQL CHECKSUM() or BINARY_CHECKSUM() functions.

At this point based on that info, I would use T-SQL CHECKSUM for my SCD management.