Comparing Large Tables

I've successfully created SSIS packages where I compare two tables in different databases on different servers. However, this is good enough to compare hundreds of thousands of records quickly. The process becomes a huge performance problem when trying to compare table differences when I'm looking at tables that each contain tens of millions of records.

One database is on a SQL 2005 box and the other DB is SQL 7.0 so the lookup component fails for this type of SQL Server. I've been implementing merge joins and conditional components to do my standard table comparisons.

Is there another way to implement this process or maybe partition it somehow to take pieces of the table at a time and compare them I'm open to ideas.

Well, when you're doing a lookup for tens of millions of records against another table with tens of millions records I would think it becomes pretty considerable since you're doing a row-by-row comparison.

Do you think it would be faster to stage the tables and try comparing them through T-SQL or just use SSIS's lookup component

Hi Chip_Levis

I am new to SSIS and almost in the same situation you have. I need to create a SSIS Package to compare data in two tables in different databases on different servers. Can I know how you have accomplished this in SSIS package

Thanks,

Sundar.

Hi Phil-

Thanks for the reply. Both of my tables are in the same SQL 2005 Server. Only different databases.

Regards,

Sundar.

Hi Phil-

I would also like to know, any component available in data flow for using SQL scripts.

Thanks,

Sundar.

I am sorry. I didn't see your last post. I will check for the Lookup components.

Thanks,

Sundar.