Launch SSIS package with SQL Event

Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?

You can use xp_cmdshell to call DTEXEC. You could also set up a job for the package and call sp_start_job.

See this link for some more detail and some other options: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

Launch exe using trigger within DTS

The following has been posted on another thread and I would like to know if anyone can tell me if and how this could be done. I'm a complete novice when it come to DTS.

TIA.

"I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible."You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'

You can start a dts package in the same way by using dtsrun.exe if you really want to use dts - but that would be starting an app to run an app.

I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.

Note that the exe must not have any user interaction as there is no display or input stream attached|||Originally posted by nigelrivett
You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'



Thanks Nigel

Do I just add this at the end of the trigger as per below ? Pretty new to this stuff !

CREATE TRIGGER tr_owner_coord_email ON dbo.ownership
after insert as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
update ownership
SET coordemail = (select con.emailaddress
from contact as con with (nolock)
where coordinator = (con.forename + ' ' + con.surname))

exec master..xp_cmdshell 'xxxx.exe'|||Originally posted by nigelrivett
I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.


Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...|||Originally posted by Brett Kaiser
Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...

If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.|||There is not going to be heavy demand with the trigger. It will probably only be average of 60-80 rows inserted per day.

The other way I was handling this was with a timer in Vb but max interval on that is 60 seconds so even more overhead.

I would prefer to do it as Nigel suggested but need more detailed info being a newbie. I just wanted to know if there is a way the exe can be launched after the trigger had been executed. I am open to all suggestions and as was put in the original post someone thought a DTS package could launch the exe after the trigger. That way it would only be run 60-80 times per day rather than over 500 times pd using vb timer.|||Originally posted by snail
If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.

So True....my bad...|||PMJI, be careful withn the trigger.

While the trigger runs, the transaction is not yet commited. The database keeps exclusive locks on the row or page you are about to insert into. If you have a multi-statement transaction, then there are even more locks, preventing everyone else to access your records.

Now imagine you put some long-running command in the trigger. AFAIK, xp_cmdshell runs synchronously, i.e. it waits untill your EXE completes. All the while your records are locked. You may not worry about concurrency, if this is a single-user application or some background drip-feed.

But if you intend to connect from the EXE back into Sql Server, then that is a separate connection. The EXE won't see the record just being inserted by the trigger. If the EXE attempts to Select one of the locked records, you may end up waiting forever ( Exe waits for Trigger to release the locks, while Trigger waits for Exe to complete ). Sounds like fun.

As about integrity: it is nice to know you can roll back a multi-statement transaction, including the efefcts of triggers. But xp_cmdshell is outside the Rbdms, so it won't roll back at all.

I realise triggers are an attractive thought, to streamline the chain of command, and to reduce the number of moving parts. In practice, they don't perform this role too well.

Andrew Schonberger

launch an appl from a trigger ?

Is there a way to launch an application(c:\hello.exe)
from a trigger when a new record is inserted into the
table or
a record is updated in the table?
If so, what is the correct syntax ?
THANKS!Understand that launching an application that requires the input from a =user can extremely critical ... This is because this app is invoked in =the same process of SQL Server and the credential of the user to =interact with desktop maynot be there ... Hence such operation can stop =the SQL Server service ... Hence avoid such operations ...
On the contrary you like to send a popup using the network messenger =service then use the xp_cmdshell command ... But it is not recommended =in production code ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"amy" <achen@.comverge.com> wrote in message =news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the > table or > a record is updated in the table?
> > If so, what is the correct syntax ?
> > THANKS!|||Amy,
You can use the xp_cmdshell extended procedure to start an application from
the command prompt. For exact syntax, do please refer to Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"amy" <achen@.comverge.com> wrote in message
news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the
> table or
> a record is updated in the table?
> If so, what is the correct syntax ?
> THANKS!|||I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible. If it
did work, it would get around the issues raised here
about having the .exe hang things up, etc.
>--Original Message--
>Amy,
>What are you trying to do ? Ofcourse you can launch an
application by using
>master..xp_cmdshell, as in
>exec master..xp_cmdshell "dtsrun /?"
>But for every insert and update? well , no..I wont do
that.Also, if this
>hello.exe has any GUI as in exec
master..xp_cmdshell "notepad" ,
>master..xp_cmdshell would hang leading to timeout
problems.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"amy" <achen@.comverge.com> wrote in message
>news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> Is there a way to launch an application(c:\hello.exe)
>> from a trigger when a new record is inserted into the
>> table or
>> a record is updated in the table?
>> If so, what is the correct syntax ?
>> THANKS!
>
>.
>|||Steve,
Since you replied to my post...
It may work or may be we can find a workaround but I still wont do that.In a
heavy OLTP system, triggers are always a overhead for the DML and a trigger
which calls a exe, gui or no gui , would be a recipe for disaster.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> I would investigate having the .exe be part of a "job"
> under DTS Local Packages and having the trigger start
> that job - not really sure if this is possible. If it
> did work, it would get around the issues raised here
> about having the .exe hang things up, etc.
> >--Original Message--
> >Amy,
> >
> >What are you trying to do ? Ofcourse you can launch an
> application by using
> >master..xp_cmdshell, as in
> >
> >exec master..xp_cmdshell "dtsrun /?"
> >
> >But for every insert and update? well , no..I wont do
> that.Also, if this
> >hello.exe has any GUI as in exec
> master..xp_cmdshell "notepad" ,
> >master..xp_cmdshell would hang leading to timeout
> problems.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"amy" <achen@.comverge.com> wrote in message
> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> Is there a way to launch an application(c:\hello.exe)
> >> from a trigger when a new record is inserted into the
> >> table or
> >> a record is updated in the table?
> >>
> >> If so, what is the correct syntax ?
> >>
> >> THANKS!
> >
> >
> >.
> >|||I agree that other paths might serve the same purpose,
but I'm not sure what the need really is. It would make
more sense to me to have a "scheduled" job run regularly
(every x minutes) and sweep up handling all the "rows"
inserted since the last sweep. Or code the .exe in C and
make it an extended stored procedure - run right from
inside the trigger. Or simply code the need in T-Sql and
get rid of the .exe requirement...
>--Original Message--
>Steve,
>Since you replied to my post...
>It may work or may be we can find a workaround but I
still wont do that.In a
>heavy OLTP system, triggers are always a overhead for
the DML and a trigger
>which calls a exe, gui or no gui , would be a recipe for
disaster.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
>> I would investigate having the .exe be part of a "job"
>> under DTS Local Packages and having the trigger start
>> that job - not really sure if this is possible. If it
>> did work, it would get around the issues raised here
>> about having the .exe hang things up, etc.
>> >--Original Message--
>> >Amy,
>> >
>> >What are you trying to do ? Ofcourse you can launch an
>> application by using
>> >master..xp_cmdshell, as in
>> >
>> >exec master..xp_cmdshell "dtsrun /?"
>> >
>> >But for every insert and update? well , no..I wont do
>> that.Also, if this
>> >hello.exe has any GUI as in exec
>> master..xp_cmdshell "notepad" ,
>> >master..xp_cmdshell would hang leading to timeout
>> problems.
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"amy" <achen@.comverge.com> wrote in message
>> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> >> Is there a way to launch an application
(c:\hello.exe)
>> >> from a trigger when a new record is inserted into
the
>> >> table or
>> >> a record is updated in the table?
>> >>
>> >> If so, what is the correct syntax ?
>> >>
>> >> THANKS!
>> >
>> >
>> >.
>> >
>
>.
>|||Steve,
Well if you are asking which of the requirement to take then, my =suggestion would be to use the T-SQL method and a jobs framework ... It =is simply because you are closer to the data and getting out of process =of SQL and then get the job done is something not advisable ...
Now trigger can prove costlier as this will fire with every insert =operation ... But would make it more live data ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Steve Z" <szlamany@.antarescomputing.com> wrote in message =news:0eb201c35779$6045c3b0$a401280a@.phx.gbl...
> I agree that other paths might serve the same purpose, > but I'm not sure what the need really is. It would make > more sense to me to have a "scheduled" job run regularly > (every x minutes) and sweep up handling all the "rows" > inserted since the last sweep. Or code the .exe in C and > make it an extended stored procedure - run right from > inside the trigger. Or simply code the need in T-Sql and > get rid of the .exe requirement...
> >--Original Message--
> >Steve,
> >
> >Since you replied to my post...
> >
> >It may work or may be we can find a workaround but I > still wont do that.In a
> >heavy OLTP system, triggers are always a overhead for > the DML and a trigger
> >which calls a exe, gui or no gui , would be a recipe for > disaster.
> >
> >-- > >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"Steve Z" <szlamany@.antarescomputing.com> wrote in > message
> >news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> >> I would investigate having the .exe be part of a "job"
> >> under DTS Local Packages and having the trigger start
> >> that job - not really sure if this is possible. If it
> >> did work, it would get around the issues raised here
> >> about having the .exe hang things up, etc.
> >>
> >> >--Original Message--
> >> >Amy,
> >> >
> >> >What are you trying to do ? Ofcourse you can launch an
> >> application by using
> >> >master..xp_cmdshell, as in
> >> >
> >> >exec master..xp_cmdshell "dtsrun /?"
> >> >
> >> >But for every insert and update? well , no..I wont do
> >> that.Also, if this
> >> >hello.exe has any GUI as in exec
> >> master..xp_cmdshell "notepad" ,
> >> >master..xp_cmdshell would hang leading to timeout
> >> problems.
> >> >
> >> >-- > >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"amy" <achen@.comverge.com> wrote in message
> >> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> >> Is there a way to launch an application
> (c:\hello.exe)
> >> >> from a trigger when a new record is inserted into > the
> >> >> table or
> >> >> a record is updated in the table?
> >> >>
> >> >> If so, what is the correct syntax ?
> >> >>
> >> >> THANKS!
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

lastUpdatedTs and userID from Inserted table

I wrote the following trigger:

CREATE TRIGGER dbo.tru_Employee
ON Employee
AFTER update
AS

DECLARE @.lastUpdatedTs datetime
DECLARE @.table NVARCHAR(50)
DECLARE @.transID INT
DECLARE @.userID INT

SET @.table='Employee'

SELECT @.userID = lastUpdatedBy,
@.lastUpdatedTs=lastUpdatedTs
FROM inserted

INSERT INTO AUD_tracking VALUES(@.table,@.lastUpdatedTs,@.userID)
SET @.transID=@.@.IDENTITY

Every time this triger fires it always reads the same value for userID and lastUpdatedBy. It's like 'inserted' table is not even affected by my UPDATE statement while @.oldvalue and @.newvalue that I populated the other table with (Tracking details table) are just fine.

What could it be?
ThanksYour trigger fires once for each operation, even if many records were updated. Try this more common syntax:

INSERT INTO AUD_tracking (Table, LastUpdatedTs, UserID)
SELECT 'Employee', LastUpdatedTs, LastUpdatedBy
FROM Inserted

Tip: I like to add a field to my audit table where I can store the Primary Key of the affected records so that I also know what was altered. Sometimes, I even store the before and after values. It makes a thorough paper trail that has proven helpful in diagnosing problems and averting blame.

blindman|||Originally posted by blindman
Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:


Thank you for your reply.

I am sorry for giving a confusing information. This was only a beginning part of the trigger. It is followed by the part for updating each and every column. Also I am creating two tables: one that I already mentioned and the other one with tracking details(oldvalue, newvalue, etc.).

As for the timestamp amd userID being the same, I just noticed that lastUpdatedTs and lastUpdatedBy doesn't change on update in the original table either. At least when I simply run update with QA.
So I guess I have to go back to this on Monday.

lastmodified

Hi,
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>

Last Transact-SQL Statement Executed

Hello All,
Please how can I retrieve/save the last Transact-SQL
statements executed against a database? Can I use a
trigger for this?
Thanks.Triggers work on tables and will only fire for data
updates not selects.
If all your access is via SPs then you could log in the SP.
You could log in the client if you are in control of that.
Otherwise the easiest way is to use the profiler to log
statements to a table or text file.
>--Original Message--
>Hello All,
>Please how can I retrieve/save the last Transact-SQL
>statements executed against a database? Can I use a
>trigger for this?
>Thanks.
>
>.
>

last time a table was used

Any way without a trigger to determine the last time a table was accessed? we have duplicate tables accross some databases, and suspect we don't need a few of them. ;)

They won't let me change the names and see who squeals.

Sql profiler?!|||

Yeah, forgot to mention I only have developer rights.

<<sigh>>

I was hoping there was an additional magic microsoft faerie out there that would somehow know.

sql