11/7/2008 8:15:18 AM #

Great task to have.

Just one question is it guranteed that the returned checksum is always unique?

This was also said on the T-SQL 2000 BINARY_CHECKSUM() but in real this is not the case:
SELECT BINARY_CHECKSUM('aa','AA','Arjan')
-----------
4225134

SELECT BINARY_CHECKSUM('BQ','AA','Arjan')
-----------
4225134

Ofcource the change that this occures limits by the number of columns you use in the checksum, but the change still exists.

Kind regards,
Arjan Fraaij

Arjan Fraaij

12/19/2008 9:17:02 PM #

It seems that the CRC32 algorithm mistakenly considers the row number when generating its checksum.  To test this, I created a SQL table named TestTable with identity column ID and string column TestData.  I added a bunch of records with the value 'ABC' for TestData in each record.  

I used this table as an OLE DB source sorted by ID ascending and then selected only column TestData as an input column when running it through the Checksum Transformation.  Each row produced a different checksum.  Next, I sorted by ID descending in the source, and again each row produced a different checksum.  Comparing both results, from top to bottom the checksums matched.  That is, row #1 produced the same checksum both times.

Now when I changed from CRC32 to either of the other options, as expected every row got the same checksum.  

Any idea what's going on here?

K Boone

12/23/2008 2:36:09 PM #

I discovered the same "bug" myself. Since the other two options (Original and FrameworkChecksum) have known issues I want to avoid using them, but this issue with the CRC32 option makes it impossible to use. I have breifly reviewed the code (I'm not a dot.net programmer by any means) but it seems the stream that you are pushing to the hash function is somehow grabbing the internal row number on each row that it processes.

I can't imagine this is by design, unless I'm missing something here.

Robert G.

1/5/2009 1:08:25 PM #

SSIS – CHECKSUM Transformation

CHECKSUM is a TSQL function that computes a hash value over a list of arguments.  The great thing

Devin Knight

1/8/2009 1:08:25 PM #

Hi all,

Is there any news about the CRC32 bug? We can't use the CRC32 in this way.

Mark Gwenn

1/28/2009 3:25:33 AM #

Hi,

Is Checksum-transformation compatible in 64 bit environment ? Please any comment..

Thanks.

Hendra

2/5/2009 4:03:14 PM #

I selected the columns for the check sum but they keep disappearing from the 'Input columns' box

Polzovatel

3/23/2009 7:31:39 PM #

Hello,

I just wanted to share some information about the possible CRC32 BUG. I'm running on SQL2008 (64) and I've installed the latest Checksum release for this SQL server version. However, be aware that if you are using the Checksum with CRC32 to detect row level changes in combination with Conditional Split, you will see that from the moment a row is identified as different, the rest of rows that come after that one will also be identified as different (which doesn't happen with the "original" version). I'm not sure if this is an issue that someone is working on, any one knows?

Noemi

3/26/2009 3:19:44 PM #

I concur with Noemi.  I've tried instances of both SQL2005 and SQL2008 under Server 2003 x64 and have had the exact same experience.  As soon as one row changes, the remaining rows fail the checksum comparison through the conditional split, regardless of their update status.

Ryan

4/10/2009 12:56:39 AM #

Problems with CRC32:  I import the exact same xml file twice.  The second time returns a different checksum number, making it appear that the row changed, when in fact it didn't.  Using SQL 2008 32 bit.

craig

4/16/2009 4:06:00 PM #

Glad someone took the initiative to create this component, will be very helpful.
would like to see a status regarding the comments on this version. A bit leery to use it at this point.
Thanks

Tom

4/17/2009 6:11:14 PM #

We have run into a checksum issue . We run the checksum package on a 64-bit production machine and we have seen this error from time to time.  We are looking for a solution on this as well..   Any idea ?   Please help.

We used the Checksum Version "1.5.0.16" on SQL 2005 Enterprise edition.  

OnError,DBSW0212,DMZMGMT\sgalvining,Validate Transform Data,{DD72865F-C8B1-4DF4-BF33-3724865CE957},{4E0CB18F-0373-414C-9852-4F29B28BD627},4/13/2009 5:03:49 AM,4/13/2009 5:03:49 AM,-1073450910,0x,System.ArgumentException: Item has already been added. Key in dictionary: '79764919'  Key being added: '79764919'
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at System.Collections.Hashtable.SyncHashtable.Add(Object key, Object value)
   at Konesans.Dts.Component.Helpers.CRC32..ctor(UInt32 aPolynomial, Boolean cacheTable)
   at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform.PreExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

OnError,DBSW0212,DMZMGMT\sgalvining,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{4E0CB18F-0373-414C-9852-4F29B28BD627},4/13/2009 5:03:49 AM,4/13/2009 5:03:49 AM,-1073450910,0x,System.ArgumentException: Item has already been added. Key in dictionary: '79764919'  Key being added: '79764919'
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at System.Collections.Hashtable.SyncHashtable.Add(Object key, Object value)
   at Konesans.Dts.Component.Helpers.CRC32..ctor(UInt32 aPolynomial, Boolean cacheTable)
   at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform.PreExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Chengshu

5/5/2009 5:39:58 PM #

We get the exact same error.  Anyone have any ideas?

Error: 2009-05-04 11:39:05.47
   Code: 0xC0047062
   Source: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX [102]
   Description: System.ArgumentException: Item has already been added. Key in dictionary: '79764919'  Key being added: '79764919'
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at System.Collections.Hashtable.SyncHashtable.Add(Object key, Object value)
   at Konesans.Dts.Component.Helpers.CRC32..ctor(UInt32 aPolynomial, Boolean cacheTable)
   at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform.PreExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)
End Error

c34miller

5/7/2009 5:24:49 AM #

The order in which fields are added (ticked) to the konesans checksum component matters. There are two checksum tasks in our standard Fact table pattern. For these two tasks to produce the same checksum value on the same columns, the columns need to be added (ticked) to the checksum tasks in the same order. Our theory is the component holds an internal array of the fields added, and fields are added in the order that they are ticked. Thus if you tick the fields in a different order you will get a different checksum generated.

Data Info Leaders

5/7/2009 4:59:34 PM #

HI Guys,
Do you suggest the use of this component?
Regards,
Pedro
www.pedrocgd.blogspot.com

PedroCGD

5/7/2009 5:51:52 PM #

Hi Pedro.  Yes we like the component and use it often.  You have to understand the limitations that are detailed above and after you do that then it can be a very efficient way of doing comparisons.

Allan Mitchell

5/18/2009 10:53:50 PM #

I have SQL Server 2005 SP2.  I installed your component and when I tried to drag the transformation into the Data Flow tab I get an error:
The component could not be added to the Data Flow task.  Please verify that this component is properly installed.  The data flow object "Konesans.dts.pipeline.chesumtransform.checksumtransform, Konesans.dts.pilepine.checksumtransform, version=1.0.0.0, culture=neutral,publickeytoken=b2ab4a111192992b" is not installed correctly on this computer.

I uninstalled and installed the product.  Same error.
Can you help?

Aixa Roche

5/20/2009 12:02:51 PM #

Having downloaded and installed the Checksum Transform task, I'm experiencing the same issue as Aixa Roche:

The data flow object "Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform, Konesans.Dts.Pipeline.ChecksumTransform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

Anyone know how to resolve?

Thanks

Alex Murray

5/20/2009 5:44:42 PM #

I am responding to the comments about the ordinal position of each of the columns in the checksum. Based on the earlier post if you deselect and reselect the columns in the CheckSum component the column is placed in the bottom of the ordinal position. Well, I have tested this on a 64-bit machine and found that this is not the case. If you go back into the component the ordinal position is the same as it was prior to the change. Ordinal position is driven by the input of the component. I would like the flexiblity to change the ordinal position of this component if it is possible.

Michael Rivera

6/17/2009 5:55:22 PM #

Can someone tell me if the  CRC32 bug is fixed or not? Is this component useful or not? Thanks

yuqing

6/19/2009 4:14:35 PM #

I DL'd Checksum Transformation last week and am experiencing the same problem as described in the 3/23/09 post by Noemi.  I switched to the "Orginal" algorithm.  What are the known issues with this one?

Jean Reece

7/13/2009 12:23:41 PM #

Im suffering from the same problem posted by Noemi - 64 bit SQL Server. Is there any update on this i.e. any chance of getting the bug fixed as I would rather switch to the CRC32 checksum over the original.

Many Thanks
Dan.

Daniel Garner

8/12/2009 1:26:37 PM #

I was looking for a manner to speed-up the 'lookup-process'; which rows already exist in my Datawarehouse and which are new.

I tried this component to generate a checksum of my businesskey as input voor de Slowly Changing Dimension Component (toddmcdermid) to speedup the lookup. Doing a lookup over a 4-bytes integer field is much faster (50%) instead of doing a lookup over a varchar(32) for example.

I tested all of the three algorithm but in every case, duplicated businesskeys comes up. This is caused by duplicated checksums of the component.

Is there a good and safe way to do generate a checksum over a Datawarehouse Business key which is an integer field? I know I can use the MD5 hash for example but this is 50% slower (I tested).

Thanks.

Ronald Kraijesteijn

10/6/2009 4:22:14 PM #

I also get the intermittent error:
System.ArgumentException: Item has already been added. Key in dictionary: `79764919`  Key being added: `79764919`    
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)    
at System.Collections.Hashtable.SyncHashtable.Add(Object key, Object value)    
at Konesans.Dts.Component.Helpers.CRC32..ctor(UInt32 aPolynomial, Boolean cacheTable)    
at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform.PreExecute()    
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Note the key value specified here is the same key value returned in both the other posts about this error.  I mention that this error is intermittent - it occurred more than once for a specific package, but not with every execution for that package.  In this package, I have four containers processing in parallel; each one of them includes a Data Flow task with a Checksum transform in it.  However, only one of the Data Flow tasks used the CRC32 algorithm - and this is the one that causes the package to fail.  The others used either Framework or Original.  Since this error occurs on PreExecute, always with the same Key value error, could this be caused by the SSIS engine attempting to add more than one type of Checksum object to the HashTables collections with the same key?  I'm fairly ignorant about the internals of SSIS and am not a .NET programmer, but the intermittent nature of the error screams "timing!" to me.  For others who have experienced this issue, do you have more than one Checksum transform (either CRC32 or one of the other types) potentially instantiating at the same time as a CRC32 type?  This transform is one of my can't-live-without objects, so until the CRC32 algorithm stabilizes, I'm still using the Original algorithm.

Ellen Russell

10/9/2009 4:50:23 PM #

I also have the same intermittent error:
System.ArgumentException: Item has already been added. Key in dictionary: '79764919'  Key being added: '79764919'
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
at System.Collections.Hashtable.Add(Object key, Object value)
at System.Collections.Hashtable.SyncHashtable.Add(Object key, Object value)
at Konesans.Dts.Component.Helpers.CRC32..ctor(UInt32 aPolynomial, Boolean cacheTable)
at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

I have also multiple containers processes in parallel and each container has a data flow task with Checksum Transform in it. But all of my Checksum Transform uses the 'Original' algorithm. So, I don't think CRC32 algorithm is an issue here. Interestingly, this error even occurs with an empty table too and it might occur once for a specific package.

Kazi Islam

10/19/2009 6:29:03 PM #

Related to this error: System.ArgumentException: Item has already been added. Key in dictionary: `79764919`  Key being added: `79764919`    

We have done some significant testing on this.  We encountered this a few times in production and decided we needed both a short term and long term solution.  In the short term, we have discovered that removing any parallel processing seems to reduce the occurrence of the error.  One of our frustrations is that the error was more prevalent in production, where we have better machines with more processors, than in our test environment.  This was caused by more worker packages to running in parallel, each using the Konesans component.  By forcing precedence constraints on these components so they only run in serial appears to have resolved the problem (at least for us, your results may vary).

In the long term we are removing the Konesans component and replacing it with an execute SQL component that calls the SQL checksum functionality directly.

c34miller

11/26/2009 8:39:10 AM #

Is there any news about the CRC32 bug? We can't use the CRC32 in this way.

Dawson

12/17/2009 7:10:35 AM #

It is getting boring.... but any news on the CRC32 algorithm? We get the same error as posted several times before. As we are about to go in production I would like this solved soon...

Erik van Dongen

1/6/2010 6:58:32 PM #

We have the same CRC32 issue. Once a record is determined to have different current and input checksums all remaining input records are assumed to be different. So if the 5th record in a set of 20 is the only one with new values (new checksum value) the first 4 are deemed no change but records 5 thru 20 are considered changes. The Original algorithm is fine. Can some explain why I should not use the Original algorithm?

Elijah

1/7/2010 9:23:48 PM #

For those wondering on a the CRC32 solution I found an alternative. There is a tranform on the Codeplex website called Multiple Hash. It allows you to use MD5, SHA1 plus more. It seems to work just fine. I defined my hash value on my table as Binary(16) with MD5. In the Conditional split I identify changes as (DT_STR,50,1252)New_Checksum != (DT_STR,50,1252)Old_Checksum. Here is the link. http://ssismhash.codeplex.com/wikipage?title=Usage%20Instructions&referringTitle=Home

Elijah