January 3, 2006 at 7:29 am
hi all
ive created a dts write to file package - which should be executed
every time a specific view is updated.
( the view is updated very rarely )
the package just copies to data in the view into a txt file
My Qus. is:
how do i call the execute package command from a trigger ????
January 4, 2006 at 5:41 am
You would need to execute DTSRUN.EXE using the xp_cmdshell extended stored procedure:
exec master.dbo.xp_cmdshell 'dtsrun.exe...'
However, running xp_cmdshell from a trigger is not recommended. If xp_cmdshell or the DTS package gets hung up, so will the transaction, which could block other users.
An alternate method involves writing a copy of the data to another table that is polled by a scheduled job. Depending on your needs, the new table could include primary key columns only. The job would use the PK info to SELECT the data from the view and output it to a text file. If that's successful, the row in the new table would then be deleted.
January 4, 2006 at 12:34 pm
Well, you can create a job and run sp_startjob with the job name to kick it off, but you will want to test it to see how well that works for you.
Also, the login would need permissions to kick off the job regularly. If this doesn't have to be real-time, you could build a queue table. Insert a row in the queue table, schedule a job to run every xx interval. If there's a new row in the table, start the DTS.
January 5, 2006 at 1:27 am
Why not have the process that updates the view also initiate creation of the text file? In other words, if there is a front-end or batch process that modifies the view, add a line of code to create the text file.
If the text file is small, just create it through whatever native code is in the front end (for example, if it's a VB or ASP front end, use file scripting object).
If the text file is large, you could do this asynchronously by kicking off sp_startjob from the front end. This would start a job you've created, which would kick off a DTS package to dump the view data into a text file. The advantage to doing this (instead of scheduling a job, as paschott suggested) is that you have the least amount of lag time between the data changing and the text file being updated.
If there is no front-end (i.e., if users are editing the table directly) you can kick off sp_startjob from the TRIGGER as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply