Compare Disparate Data

Question

We currently have a process where we receive an inventory file from a vendor every hour. We need to compare that inventory data in the file to what we currently have in our DB. Any thoughts/ideas on how you would approach this?

Here is what we are doing currently:

We pull the data from the db table into C# and do the same with the file then use LINQ to compare.

We had originally been staging the file data into a separate db table then comparing.

There can be up to about 100,000 records in the file.

Thanks in advance.....

S

Answer 1

Can the vendor include a last updated datetime field in the inventory file? It would be a lot simpler to only process changes than iterating through 100,000 records.

Answer 2

You should use SSIS. As a data flow engine, it has all sort of optimizations around the whole process of reading, processing and writing data that your application will miss. See Considerations for High Volume ETL Using SQL Server Integration Services for an introductory discussion on the topic.

Answer 3

Hi there,

If you are using SQL Server, then you might want to give a try to Volpet's Table Diff:

http://www.volpet.com/

You can try a fully-functional copy for 30 days.

Please let me know for anything.

Thank you,

Giammarco

Volpet Software

Answer 4

Create an ODBC text DSN for the text file (I am assuming it is fixed format or delimited) using Microsoft ODBC text driver or DataDirect ODBC text driver (DataDirect driver requires a license, but you can download an evaluation copy for testing if MS text driver does not do what you want).

Create an ODBC DSN data source for the database table and then use an ODBC data comparison/sync tool such as Zidsoft CompareData to compare and/or sync the data. CompareData has scheduling so you can schedule the comparison to run continuously every 1 hour