Running DTS from a trigger

[tlaksh]

I need to execute a dts from within a trigger. Can I do this. If so...how???


Thanks

Lakshmi.

 

[tlbroadbent]

I'm curious about why you need to run DTS from within a trigger. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[tlaksh]

I need to save records into a txt file before deleting them. So i have a trigger on the table that inserts the deleted records into a temp table. I then plan to use dts to convert it to txt file. For this to automate itself i need to execute the dts from within the trigger

Is this okay?? Any suggestions???

 

[tlbroadbent]

Here are a couple of ways to handle the situation. In both cases, your temporary table shouldn't really be temporary. I would prefer calling it a transaction holding table. It should be a permanent table in your database.

Suggestion:

1- Have the Trigger insert deleted records into the holding table.

2a- Schedule a DTS job to append the records in the holding table to the text file. This job could run daily, every hour, every five minutes or whatever your need may be.

OR

2b- Schedule the DTS job to start in response to an Alert. Create your own Alert and execute Raiserror in the Trigger to fire the Alert.

The problem with the second approach is that the job may not finish before the Trigger fires the alert and tries to start the Job again. Therefore, I would prefer step 2a to 2b. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[tlaksh]

Hey Terry ..

Thanks..ur suggestion no 2a just gave me an idea.

Let me try that and then i will let u know if it worked.

Thanks again

Lakshmi