sql server dts: execute dts from trigger -- sql server dts

execute dts from trigger

Rick
4/26/2007 8:44:03 AM

sql server dts: I have a situation where I have a remote system that I need a File from.
I have created a database link from the oracle system to sql server.
When oracle generates the file it then writes a row to a indicatator table.
I need to then execute a dts job to ftp the file from the remote server to
sql so I can then do the processesing of the file.
If i put a trigger on the table to execute xp_cmd this works until the file
is to large then the oracle system will get an error . Because xp_cmdshell
operates synchronously.
Control is not returned until the command shell command completes which in
this case is many steps.
So the question is how can I have a trigger that will execute a job to
retrieve the file, after reciept of file return a code of success, and then
continue with the processing of the file?
It seems to me if I break up the dts job so that after I get the file it
then calls another dts job, the return still won't be untill the second job
completes.

I don't want to create a job that just checks for the file, and runs when it
finds one, the file is only create once a day 4 days a week on the remote
system. The file needs to be loaded when created.
any help would be apprecaited.
my trigger executes like so:
EXEC master..xp_cmdshell 'dtsrun /S server /U USER /P pass /N "dts job"',
no_return
I don't remember where I got the syntax and now I can't find any reference
to no_return. What is no_return doing if anything?
BTY the server is sql ver 8.0

Re: execute dts from trigger

Russell Fields
4/26/2007 5:06:21 PM

Rick,

You could create a SQL Agent job that runs your DTS package. If the job is
owned by the same account that monitors the existence of the file (or has
sysadmin rights), then that code can:

EXEC msdb.dbo.sp_start_job @job_name = 'Load My Oracle File'

If the account that sees the file is neither the job owner now sysadmin,
then you can set up the job to be started by an alert. If the alert message
number is configured to start a job, that can also begin the job. Once the
message and alert are defined you code could issue:

RAISERROR(50099,10,1)

Read about defining alerts in the books online.

RLF

[quoted text, click to view]