Create trigger to fire SSIS package
-
In that case, don't do it in a trigger, unless the trigger is in a database that has absolutely no performance implications at all, and can deal with dropped transactions.
If you absolutely have to do it in a trigger:
1. Create the SSIS package and deploy it to the server.
2. Create an SQL Agent job that will run the package.
3. Use sp_start_job in the trigger to start the job. Details at: http://msdn.microsoft.com/en-us/library/ms186757.aspx
Please note, this is a REALLY, REALLY, REALLY bad idea. It can be done, but you can also use paper mache for your plumbing needs. Either one is going to result in all kinds of messy s**t all over the place in very short order.
If you absolutely have to use a solution like this, make sure management knows that the reason for it is moronic restrictions created by people who don't know anything at all about server security. The people who created that rule seriously need to step away from the databases with their hands in plain view, and please don't make any sudden moves. Make sure you have something in writing from those incompetent idiots that specifically spells out their policy and your restrictions within it. Make sure your boss and their boss both understand that calling SSIS from a trigger is only being done because of those restrictions, and make sure that you have documented that you sought expert advice on the subject and were told that (a) this policy does absolutely nothing to increase server security, and (b) it will cause problems on the server sending the data. Make them sign off on that document and that they understand that their policy is what is causing the problems.
If you do all of that, when the paper mache plumbing disintegrates and there's sewage all over the place, you'll at least have a slight chance of not being fired for it. Not a good chance, but at least some chance at all.
Either that, or avoid this, and queue the data up and run SSIS periodically through a scheduled job, and avoid all the messy stuff, but the destination server may have to wait a few minutes for data instead of either not getting it at all or getting it closer to right away (those are the two possibilities with SSIS from a trigger).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (3/25/2011)
In that case, don't do it in a trigger, unless the trigger is in a database that has absolutely no performance implications at all, and can deal with dropped transactions.If you absolutely have to do it in a trigger:
1. Create the SSIS package and deploy it to the server.
2. Create an SQL Agent job that will run the package.
3. Use sp_start_job in the trigger to start the job. Details at: http://msdn.microsoft.com/en-us/library/ms186757.aspx
Please note, this is a REALLY, REALLY, REALLY bad idea. It can be done, but you can also use paper mache for your plumbing needs. Either one is going to result in all kinds of messy s**t all over the place in very short order.
If you absolutely have to use a solution like this, make sure management knows that the reason for it is moronic restrictions created by people who don't know anything at all about server security. The people who created that rule seriously need to step away from the databases with their hands in plain view, and please don't make any sudden moves. Make sure you have something in writing from those incompetent idiots that specifically spells out their policy and your restrictions within it. Make sure your boss and their boss both understand that calling SSIS from a trigger is only being done because of those restrictions, and make sure that you have documented that you sought expert advice on the subject and were told that (a) this policy does absolutely nothing to increase server security, and (b) it will cause problems on the server sending the data. Make them sign off on that document and that they understand that their policy is what is causing the problems.
If you do all of that, when the paper mache plumbing disintegrates and there's sewage all over the place, you'll at least have a slight chance of not being fired for it. Not a good chance, but at least some chance at all.
Either that, or avoid this, and queue the data up and run SSIS periodically through a scheduled job, and avoid all the messy stuff, but the destination server may have to wait a few minutes for data instead of either not getting it at all or getting it closer to right away (those are the two possibilities with SSIS from a trigger).
Thanks a lot for your detailed explanation. I know what you mean and would stay clear of using triggers in this scenario. But I want to ask you one thing though- how do you queue the data up ?
This is one thing I failed to understand.
-
Instead of adding a trigger to the table, either add a column to the table that indicates it has been transfered to the other server, or add a log table that records which rows have been transfered. Then have the SSIS package query the table where either the added column says "not transfered yet", or the log table doesn't show a record of a transfer. If you need to transfer after updates as well as after inserts, then a combination of a RowVersion column and a log of those works well for that.
Then the SSIS package transfers data over, and then either marks it done in the column, or logs it in the separate table.
That queues it up, avoids all the problems that a trigger will cause, and also allows you to keep track of things like when something was transfered and was it transfered successfully.
The log table version for this is more complicated to code and can get a little tricky to keep it fast, but it has the advantage that you don't make any changes to the main table, which means you don't have to worry about breaking anything that uses that table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
yet another alternative ... raise an error that is being captured by sqlagent.
http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
If you're not comfortable with the sqlengine you are using, don't go for the service broker solution;
If it fails, it is a hard job to get it back on track.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply