executing a package from T-SQL

Is it possible to execute a package in a trigger or stored procedure ?

For most practical reasons, no.

You could use xp_cmdshell to call DTEXEC which will execute a package. This will run your package synchronously, so doing this in a trigger would generally be a very bad idea, as this would block the transaction from completing during the execution. If trying to reference the data from the transaction that invoked the trigger then you will be blocked as well as this has not been committed yet. Long running transactions are generally a very bad idea.

Another method would be to create schedule job that calls the package. You could then call sp_start_job to set this off. This would be asynchronous, so has some benefit potentially.

For both options, the level of security required is generally in excess of what a normal user should be given, and therefore this precludes such methods in most environments.

|||

thanks, the second option was what I needed