How to load data to target tables (only if no dataflow task is fail in ssis package)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to load data to target tables (only if no...

How to load data to target tables (only if no dataflow task is fail in ssis package)

Rate Topic

Display Mode

Topic Options

Author

Message

asita

Posted Tuesday, January 08, 2013 10:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:46 AM
Points: 106, Visits: 206

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

Post #1404384

Tim Mitchell

Posted Tuesday, January 08, 2013 4:38 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

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
SQL Server MVP
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #1404495

opc.three

Posted Wednesday, January 09, 2013 10:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 6,698, Visits: 11,726

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1404892

« Prev Topic | Next Topic »

Permissions