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.

  • The answer to your question depends on where the failure occurs and how you are capturing/receiving those failures. Is it something SSIS should be looking for and handling in error handling routines? Is it a SQL Server issue which may be manifested in some other way? One thing you could do in either case is some pre-processing routines that look at import dates or something similar to ensure whatever may be in your staging table can be dumped or if it needs to be processed first. – Steve Mangiameli Sep 3 '15 at 20:15
  • Hello Steve, I have updated the original question and described the problem in much greater detail now. I think you may have a point with date checks but in my scenario up there, how would I implement this? Thank you. – Ravi Sep 4 '15 at 13:56
  • Jaywant, my original comment won't change much because it will greatly depend on your implementation. If you use SSIS you can capture errors in each of the process flows with error handlers, alerting you immediately in case of a problem. As an additional step or if you are rolling your own solution via SPs, you can do date checks on the import and target tables. If your data doesn't have a natural date field with a finite and knowable range of dates to examine, you may need to create a table that tracks that data and check it for completed statuses before starting a new import. – Steve Mangiameli Sep 4 '15 at 15:47
  • Thank you Steve, you're right. I can do that, the date checks on the import separately as there are no fields like that in any of the tables I have. – Ravi Sep 8 '15 at 13:15
  • Just an additional comment. Do you think SSIS Variables can be of any use in this case? I looked into it and I don't think it can be. What do you think about that? – Ravi Sep 9 '15 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.