I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique.
To give you a better picture from what I mean; In initial load we load data from a csv file into a table, in the subsequent loads, we will get another csv file (same format) that can have different records. We should compare what we have in the table vs. what exist in the new version of the csv file and only load the changed sub set. If there is a new records, it should be inserted, if update, it should be updated, if record deleted in the source, it should be marked as inactive in the table.
Now my questions are:
- Is there any way to compare two csv files in SSIS, while there is no key to make the records unique?
- How can we compare two tables in SQL Server, while there is no key to make records unique?
The volume of data in the csv file is quite high, more than 20 millions records!
Any idea is appreciated.