Suggestions on data compare using SSIS?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Suggestions on data compare using SSIS?

Suggestions on data compare using SSIS?

Rate Topic

Display Mode

Topic Options

Author

Message

John8990

Posted Tuesday, September 11, 2012 8:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:25 AM
Points: 110, Visits: 95

Hi,

Perhaps someone can throw some suggestions my way on how to perform a data compare on very large tables. The tables are in different datawarehouses, in different locations and performance is a big issue. I need to compare 4 rows in one table against 4 rows in another (there is a common key). The data is: key, date, time, value.

I've looked into binary_checksum, checksum, hashbytes, EXCEPT, MERGE. I'm not 100 sure which would offer better performance, and accurancy seems to be an issue with the checksums.

Currently, with smaller tables I am using EXCEPT, it's performance is acceptable for small tables in the region of 2 million rows. But when I reach the larger tables, upwards of 900 millions rows, I've not even tried any comparisons for fear of causing a nuclear 'meltdown' Wink

.

Any ideas on how to handle a compare of this magnitude would be great?

Thanks!

Post #1357448

John8990

Posted Tuesday, September 18, 2012 3:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:25 AM
Points: 110, Visits: 95

Just as an update, I ended up going with EXCEPT using a subset of data each day for the comparison. It's performance is better than any of the other options I tried out!

Post #1360645

« Prev Topic | Next Topic »

Permissions