Can A Trigger Be Used to Execute a DTS Package?

Can a table's trigger be used to launch a dts package? FOr example, if I
have a table called tblBatch which is at the top of my hierarchy, and I have
a field called Validate in that table. And when a user updates its value,
can a trigger be executed to, in turn, execute a DTS package that would run
a bunch of validations?
If, yes, how would the trigger code look that calls tha DTS package?You could do this by using xp_cmdshell and calling the dtsrun.exe command
prompt utility. But remember that trigger is part of the transaction, which
could become very long if you run a DTS package inside it. Maybe you could
do this asynchronously? Maybe you could just write a custom error in the
Event Log and define an alert on that error, and the alert can call a job
that executes the package?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Dana Shields" <danashields@.softhome.net> wrote in message
news:Iw3rc.12923$zn.6526@.twister.rdc-kc.rr.com...
> Can a table's trigger be used to launch a dts package? FOr example, if I
> have a table called tblBatch which is at the top of my hierarchy, and I
have
> a field called Validate in that table. And when a user updates its value,
> can a trigger be executed to, in turn, execute a DTS package that would
run
> a bunch of validations?
> If, yes, how would the trigger code look that calls tha DTS package?
>|||Can this code be used? I don't know a lot about this:
CREATE TRIGGER [triggerRunDTSPackage] ON [dbo].[tblTestName]
FOR UPDATE
AS
declare @.CMD varchar(1000)
declare @.ERROR int
IF UPDATE (ImportMore)
set @.ERROR = 0
-- DTSRun parameters must be reset for any
server/database/password change
set @.CMD = 'DTSRUN.exe /S MyServerName /U MyUserName /P
MyPassword /N TestWizard'
EXECUTE @.ERROR = master..xp_cmdshell @.CMD
The DTS package name is TestWizard and the table/field is
tblTestName.ImportMore
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uiukVtnPEHA.2580@.TK2MSFTNGP09.phx.gbl...
> You could do this by using xp_cmdshell and calling the dtsrun.exe command
> prompt utility. But remember that trigger is part of the transaction,
which
> could become very long if you run a DTS package inside it. Maybe you could
> do this asynchronously? Maybe you could just write a custom error in the
> Event Log and define an alert on that error, and the alert can call a job
> that executes the package?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Dana Shields" <danashields@.softhome.net> wrote in message
> news:Iw3rc.12923$zn.6526@.twister.rdc-kc.rr.com...
> have
value,[vbcol=seagreen]
> run
>