July 25, 2011 at 5:03 am
Hi,
I need to create a trigger to execute an SSIS job which would achieve the following:
------------------------
When any record is inserted in a table in server 1 then the same record will be appended to the existing records in the equivalent tables in server 2.
The same trigger should be applied to existing records being edited, or indeed where records are being deleted.
---------------------------
Is it possible to include INSERT, UPDATE AND DELETE statement within the same trigger ? also, should the SSIS job be run on the source or the destination server ?
Thanks,
Paul
July 25, 2011 at 6:17 am
You can setup Transactional Replication for this purpose.
July 25, 2011 at 7:37 am
Suresh B. (7/25/2011)
You can setup Transactional Replication for this purpose.
Yeah, that could be another way...but before giving a one liner for an answer, you need to at least read the question carefully.
I have specifically asked the question with regards to doing this via a trigger. You should have either not answered this question or replied in the same context as the question.
By giving a useless one line answer, you have not answered the question at all and at the same time also reduced the chances of it being replied by others.
July 25, 2011 at 8:14 am
I'll give you an even simpler answer:
Don't do this.
Triggers will block transactions till they complete. Running an SSIS package for this is a really, really bad idea.
Queue the data up and move it over using a scheduled job. Do it through replication. Anything but a trigger that calls an SSIS package.
There was a detailed discussion on this site about this subject a while back. Google/Bing/whatever should be able to find it for you.
- 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
July 25, 2011 at 8:19 am
GSquared (7/25/2011)
I'll give you an even simpler answer:Don't do this.
Triggers will block transactions till they complete. Running an SSIS package for this is a really, really bad idea.
Queue the data up and move it over using a scheduled job. Do it through replication. Anything but a trigger that calls an SSIS package.
There was a detailed discussion on this site about this subject a while back. Google/Bing/whatever should be able to find it for you.
but doesn't using the command - SP_START_JOB to call the agent job prevents the table from locking up during update.
The ultimate objective of using triggers in this case is to avoid having to wait for news/incidents to be published to the public website when there are emergencies in progress.
The trigger would copy the record as soon as it is updated on the other server.
July 25, 2011 at 8:34 am
pwalter83 (7/25/2011)
GSquared (7/25/2011)
I'll give you an even simpler answer:Don't do this.
Triggers will block transactions till they complete. Running an SSIS package for this is a really, really bad idea.
Queue the data up and move it over using a scheduled job. Do it through replication. Anything but a trigger that calls an SSIS package.
There was a detailed discussion on this site about this subject a while back. Google/Bing/whatever should be able to find it for you.
but doesn't using the command - SP_START_JOB to call the agent job prevents the table from locking up during update.
Sure, if you don't mind your trigger returning this kind of error periodically:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job TestJob (from User mydomain\mylogin) refused because the job is already running from a request by User mydomain\mylogin.
I created a test job with just a waitfor delay command in it. Then called it using sp_start_job, and then called it again from another connection using the same command.
You could, of course, query msdb to find if the job is already running, and delay till it isn't any more. But there goes your table-lock again.
So, again, I'll say, "Don't do this".
- 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
July 25, 2011 at 8:40 am
GSquared (7/25/2011)
pwalter83 (7/25/2011)
GSquared (7/25/2011)
I'll give you an even simpler answer:Don't do this.
Triggers will block transactions till they complete. Running an SSIS package for this is a really, really bad idea.
Queue the data up and move it over using a scheduled job. Do it through replication. Anything but a trigger that calls an SSIS package.
There was a detailed discussion on this site about this subject a while back. Google/Bing/whatever should be able to find it for you.
but doesn't using the command - SP_START_JOB to call the agent job prevents the table from locking up during update.
Sure, if you don't mind your trigger returning this kind of error periodically:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job TestJob (from User mydomain\mylogin) refused because the job is already running from a request by User mydomain\mylogin.
I created a test job with just a waitfor delay command in it. Then called it using sp_start_job, and then called it again from another connection using the same command.
You could, of course, query msdb to find if the job is already running, and delay till it isn't any more. But there goes your table-lock again.
So, again, I'll say, "Don't do this".
The ultimate objective of using triggers in this case is to avoid having to wait for news/incidents to be published to the public website when there are emergencies in progress.
The trigger would copy the record as soon as it is updated on the other server.
July 25, 2011 at 8:57 am
GSquared (7/25/2011)
pwalter83 (7/25/2011)
GSquared (7/25/2011)
I'll give you an even simpler answer:Don't do this.
Triggers will block transactions till they complete. Running an SSIS package for this is a really, really bad idea.
Queue the data up and move it over using a scheduled job. Do it through replication. Anything but a trigger that calls an SSIS package.
There was a detailed discussion on this site about this subject a while back. Google/Bing/whatever should be able to find it for you.
but doesn't using the command - SP_START_JOB to call the agent job prevents the table from locking up during update.
Sure, if you don't mind your trigger returning this kind of error periodically:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job TestJob (from User mydomain\mylogin) refused because the job is already running from a request by User mydomain\mylogin.
I created a test job with just a waitfor delay command in it. Then called it using sp_start_job, and then called it again from another connection using the same command.
You could, of course, query msdb to find if the job is already running, and delay till it isn't any more. But there goes your table-lock again.
So, again, I'll say, "Don't do this".
Thanks for your suggestions but I have no idea about transactional replication and log shipping so doing this via trigger is the only option I have.
July 25, 2011 at 9:13 am
What business problem are you trying to solve?
Even though you may not know a better solution, it's likely someone does. Knowing the underlying problem will give insight into what the best solution will be, and then guidance can be given on how to implement it.
- 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
July 25, 2011 at 9:26 am
GSquared (7/25/2011)
What business problem are you trying to solve?Even though you may not know a better solution, it's likely someone does. Knowing the underlying problem will give insight into what the best solution will be, and then guidance can be given on how to implement it.
Thanks again. The thing is I work in a shipping company and there is a dept called LNG. This dept deals in emergencies and currently they have to wait for any news/incidents to take any action. The reason for this being there is an SSIS job which populates the table on a schedule. Due to this the LNG cant take any action in real time. They have to wait for the job to run and the table to be populated.
Therefore, the ultimate objective of what I am trying to do is to avoid LNG having to wait for news/incidents to be published to their public website when they have emergencies in progress.
The problem is the table which needs to be populated is on a different server and due to security restrictions, only SSIS can be used to populate that table.
Please find the request below which will explain the scenario fully now:
--------------------
As discussed, we now need to go ahead with development of a new trigger for copying website news to the DMZ (primarily for LNG).
Basic requirement as follows:
1. Whenever a new ‘News’ record is added to the News table in the website database, i.e. dbo.TB_ARTICLE, TB_ARTICLE_LOOKUP, TB_ARTICE_TYPES or TB_ATTACHMENTS, trigger a new SSIS package that will append those records to the equivalent tables in the website DMZ.
2. The same trigger should be applied to existing records being edited, or indeed where records are being deleted.
The ultimate objective of this CR is to avoid LNG having to wait for news/incidents to be published to their public website when they have emergencies in progress.
-------------------
Thanks,
Paul
July 25, 2011 at 9:32 am
I walked through this thread and have to agree that a trigger to do this is a particularly bad design choice. Now I know you said that this is all you know, however, you need to understand that posting a question on a forum is going to lead to people providing alternate methods. In this case 2 different ones. The replication one I think is likely the best one. However, there is also database mirroring that might also be available.
Instead of getting hung up on the solution you want to do, step back and look at the guidance that has been provided.
GSquared is a very knowledgable poster and you would do well to at least consider the guidance given.
CEWII
July 25, 2011 at 9:57 am
Okay, now that I have a better idea of what you're trying to solve, my suggestion would be have the trigger pass values to web service on the other server, via CLR.
That will keep it asynchronous enough to avoid lock escalation issues, while keeping it real-time, and without the security issues of a direct link. It will also avoid the Distributed Transaction Coordinator, which can be a serious performance killer all by itself.
That design is subject to the volume of data being processed by this. Is it a few columns with small byte-counts, or is it large amounts of byte-heavy XML, or something like that?
Alternately, a "high priority queue" could be set up that would transfer data every few seconds. You'd have to do that outside of SQL Agent, since it doesn't get that granular on time, but would allow you to keep the data transfer within SSIS, just decoupling it from the 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
July 25, 2011 at 9:59 am
Hey G, how about cross-server service broker queues?
CEWII
July 25, 2011 at 10:02 am
To me this seems like a perfect job for the service broker ( or possible trans replication as mentioned), I agree with everyone else that I would not use a SSIS trigger for this or anything.
July 26, 2011 at 2:45 am
Elliott Whitlow (7/25/2011)
I walked through this thread and have to agree that a trigger to do this is a particularly bad design choice. Now I know you said that this is all you know, however, you need to understand that posting a question on a forum is going to lead to people providing alternate methods. In this case 2 different ones. The replication one I think is likely the best one. However, there is also database mirroring that might also be available.Instead of getting hung up on the solution you want to do, step back and look at the guidance that has been provided.
GSquared is a very knowledgable poster and you would do well to at least consider the guidance given.
CEWII
I appreciate everyone's suggestions and comments over here. During the discussion with the IT support team, it emerged that transactional replication is out of question as they would not allow it because its a 2-way communication and also because the destination server is secure.
The only way they suggested was to run the SSIS job (but not SSIS as such) through a trigger which would be EXEC msdb.dbo.sp_start_job. this would not lock up the table and only the transaction would be rolled back. And so as of now, I no longer have replication or any other solution as an option.
Could someone please help me with the code that I have to create within an SSIS job for the insert, delete or update trigger ?
for e.g.- for an insert trigger, this is what I have created for the insert trigger:
----------------------
Select *
Into #TempA
From TB_ARTICLE
Where ARTICLE_ID not in (Select ARTICLE_ID from KEULONSV105..website.TB_ARTICLE)
set identity_insert TB_ARTICLE on
Insert into TB_ARTICLE
Select * From #TempA
---------------------------------
the problem i am facing in the above code is it would not let me compare the table on another server. Does anyone know what other option can be applied in the above code ?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply