How to trigger SSIS pkg to run

[bt7403]

I have two SQL jobs running on set schedule (every 15mins.)
One job is a Fetch routine; the second job is an SSIS pkg.
My concerns are decreased db performance as these jobs consume alot of time to execute.

I can add both job tasks into one SQL job to run, but I want a trigger to execute the job (not time).
When Table A receives a new recordset (comes from T-SQL separately during replication), I want the job to run.
How do I make this happen?

thx..bt7403

 

[bt7403]

I tried to script the two jobs together, but they will not run as SQL query (error: specified @name already exists).
If I change the @name it will run once only as it now exists and can not run again under same name.

Still trying. Anybody got an answer on this one?

thx.bt7403

 

[mrdenny]

When you create out the jobs all you are doing is creating the jobs.

You could setup a trigger which would run the SSIS package, however who ever enters data into table would need access to xp_cmdshell. You'd also have to remember that the insert wouldn't be commited until the SSIS package was complete.

It's a much better idea to have the job run on a schedule.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com