January 8, 2013 at 10:57 am
Hello All,
In my ssis package, i have 25 data flow tasks which will load from some third party database in sql server to staging database in sql server(both db's are in same server)
the target tables has some primary key constraints,
this job runs daily once,
first step is it clears all tables in target DB,
second it loads data to tables,
so when ever one dataflow task fails(due with primary key violate or soem data type errs mostly) then some tables has data and some tables has no data,
so what i am planning is first store temp tables (normal tables) in target db then at last (if no errors) then load to actual tables.,
is this goodway or can i do any smart way to do this funcatioanlity (because i have to create 25 temp tables)?
so at the end, if any step fails they the tables should contain yesterdays data
Thanks
asita
January 8, 2013 at 4:38 pm
You certainly could use temp tables (though if you take that approach, I recommend using permanent staging tables to avoid potential issues with temp tables). However, if this all-or-nothing operation is critical, you must also account for the possibility that the operation loading from the temp (or staging) tables to the destination tables load could also fail.
To revert back to the previous copy of the data in the event of any error is a little more difficult. Unless you use SSIS transactions (which I don't recommend, by the way), you'll need to build in a mechanism for each table to store the current contents of that table for each operation. This would probably look like another set of staging tables, this time used just to persist the last known good set of data. In the event of any error, you'd essentially reverse that ETL process to load from that second set of staging tables back to the destination tables.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
January 9, 2013 at 10:27 am
I agree that permanent staging tables would be a better option than true temp tables (# tables).
One option to consider:
1. create 25 staging tables with the exact schema (including constraints, indexes and triggers) as your 25 destination tables, choosing alternate names where necessary
2. point your SSIS package at the staging tables
3. if the clearing and loading of all 25 staging tables succeeds (a Sequence Container could come in handy here)
...
4. rename the destination tables to in effect move them out of the way
5. rename the staging tables into the place of the destination tables
6. rename the renamed destination tables to have the original name of the staging tables so you're ready for tomorrow's import
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply