So say there is a process in place for importing data from excel spreadsheet to SQL using
SSIS. And there are two tables, one with new data and one with data from its previous run.
I sometimes have to run queries comparing data in both tables. Like there is a query which detects any difference in the data between the two tables. The excel import happens on the table to store new data, and at the end of the week, data from this table is copied to the previous run table.
My question is, say if something was to go wrong and transactions failed and now, there is stale data in the system because the import failed or maybe it failed at the step where it copies data to the previous run table, resulting in my queries comparing both tables to return false information (like it might say there is no change in data between the new and old table because of its failure to update one of the tables).
How do you ensure that all your information is relevant in that case?
UPDATE: Here is the detailed explanation of what it is I want. Step 1 : Table A imports data from Excel. TAble A's previous run tables are replaced with Table A's current contents when new data is filled into table A, same process for Table B too every next day. Step 2: Table B imports data from Table B last run Step 3: If successful, table A sends an email out (email pulls data from the Step 4: successfully imported table). Same for B, if B's process is done successfully, it also sends an email pulling data from B. Step 5: If Table A and B imports went well, a combined email that compares data from both Table A and Table B are sent in this case.
The issue is, I do not want Table B or Table A to go through the import and copy process at all in case of the failure of one stream of processes (let's say for example Table A's flow of work) until it has had the chance to catch up to Table B in the example above. So I want it to somehow be such that no matter where it fails, just re-running the package should bring it back to its correct state without messing up data especially for the comparison emails to be sent with data from both tables. It is proving to be horribly difficult laying out the logic for this. I gathered that I will need to track current run AND last run of the whole work flow. But what's happening is say Steps 3 and 4 had succeeded last time and 1 and 2 had failed, it would end up running 3 and 4 every next run. In that case, I do not want 3 and 4 to run in the next run at all until 1 and 2 had a chance to catch up to them. I hope I have described my situation in great detail here now. Thank you.