Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drpcken
Starting Member
20 Posts |
 Posted - 2005-12-21 : 14:13:26
|
| I'm somewhat new to advanced DTS packages, but I have one now that does the following:It pulls data using an odbc driver, and puts that data in a sql table, but not before deleting the table (to prevent dupes in the sql table). My problem is the first thing it does is delete the table, and then connects to the source and transfers data to the destination. But what happens if it can't make a connection to my source? It still deletes the table and i'm left with no data because it deleted it all first. What I'd like to make it do is if the source connection is Successfull, THEN delete the sql table, THEN transfer the data. If the source connection is NOT successfull, then i want it to stop executing the package (and of course log the reason why it coudln't connect)I've messed with the package for hours and can't quite figure it out.Please help! And thank you in advance for ANY help directed at me. |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-12-22 : 18:54:36
|
I don't know of a way to do exactly what you want. Even if you could program the DTS package that way, it still won't guard against insert errors or data problems. Perhaps the best way would be to create a separate staging table, use DTS to transfer the data there, and then use a simple statement like this to move the data to its final destination:IF (SELECT COUNT(*) FROM staging_table) > 0 BEGIN DELETE destination_table WHERE ... INSERT INTO destination_table SELECT col1, col2 ... FROM staging_table END |
 |
|
|
|
|
|
DTS package that deletes according to condition