March 16, 2011 at 10:34 am
Hello Friends,
Could someone please guide me if its possible to create a trigger which can fire an SSIS package ? If yes, then could you please give a hint or a starting point of how that can be done ?
There is a requirement to create a trigger which should fire an SSIS package whenever data is added to a table. The SSIS package would then take care of copying the data on another server.
As the destination table on another server has security restrictions, therefore the only way of copying data is through SSIS.
Thanks and Regards,
Paul
March 16, 2011 at 10:49 am
If possible, I would approach this slightly differently.
1) Set up your trigger to write data to a 'pending' table every time there is an update.
2) Create an SSIS task to read the 'pending' table and action updates, if necessary. The last part of this task would be to remove those items which have been processed from the table.
3) Schedule the SSIS task to run as frequently as necessary.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2011 at 11:21 am
Don't directly call SSIS from a trigger. Triggers are part of the transaction, which means locks will last longer, concurrency and performance will suffer, and, if the SSIS package has any issues, the transactions can end up being rolled back. I've also had issues with trying to run the same SSIS package in parallel with itself, which might also be an issue.
It can be done, but I highly recommend against it. If you absolutely have to, then create a job in SQL Agent, and have the trigger start the job. But really, don't do 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
March 16, 2011 at 11:39 am
I'm going to jump on the bandwagon and say DON'T! There are several ways to handle this and basically all of them are better..
Offering an idea I don't think I saw yet.. You could use Service Broker to add a new item to a queue. Then the execution of the SSIS would be near-term but would not be anywhere near as problematic as calling it directly from a trigger..
CEWII
March 16, 2011 at 11:51 am
Service Broker would definitely be better than in 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
March 22, 2011 at 3:21 am
Elliott Whitlow (3/16/2011)
I'm going to jump on the bandwagon and say DON'T! There are several ways to handle this and basically all of them are better..Offering an idea I don't think I saw yet.. You could use Service Broker to add a new item to a queue. Then the execution of the SSIS would be near-term but would not be anywhere near as problematic as calling it directly from a trigger..
CEWII
Hi Elliot,
Thanks a for your suggestion. But isnt service broker run by DBAs ? I have a limited knowledge of SQL and using service broker just seems out of my league to perform.
Thanks and Regards,
Paul
March 23, 2011 at 4:16 am
pwalter83 (3/22/2011)
But isnt service broker run by DBAs ? I have a limited knowledge of SQL and using service broker just seems out of my league to perform.
Aren't triggers used by DBAs? 😉
If Service Broker is too far out of your comfort zone, I would go with the solution of Phil Parkin or the one of GSquared.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2011 at 7:48 am
Aren't triggers used by DBAs? 😉
If Service Broker is too far out of your comfort zone, I would go with the solution of Phil Parkin or the one of GSquared.
Actually what I meant was isn't service broker one of the jobs performed by DBAs exclusively ?
March 23, 2011 at 7:57 am
pwalter83 (3/23/2011)
Aren't triggers used by DBAs? 😉
If Service Broker is too far out of your comfort zone, I would go with the solution of Phil Parkin or the one of GSquared.
Actually what I meant was isn't service broker one of the jobs performed by DBAs exclusively ?
Bwa, it depends 😀
In the MS Certification exams, Service Broker is a part of the developer exams, not the DBA exams.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 24, 2011 at 3:31 am
Hi,
Could someone please help me with the code to trigger an SSIS package when data is inserted in a table on server 1 ?
The SSIS package would then copy the data to another table on server 2. Due to security restrictions, on the destination server, only the SSIS port has been opened to copy data. In other words, SSIS is the only way to copy data to the destination server.
Similarly, if the data is deleted or updated on server 1, then the same should be replicated on server 2.
Thanks and Regards,
Paul
March 24, 2011 at 8:05 am
Paul,
I'm sorry to be this blunt but the gist of this thread is that this is an a bad practice. If you would like to discuss alternatives that can certainly be done. I would like to ask why SSIS is the only way to move the data?
CEWII
March 24, 2011 at 11:07 am
Elliott Whitlow (3/24/2011)
Paul,I'm sorry to be this blunt but the gist of this thread is that this is an a bad practice. If you would like to discuss alternatives that can certainly be done. I would like to ask why SSIS is the only way to move the data?
CEWII
The destination server has security implications and as such only the SSIS port is allowed on the server. This is the reason I think that a trigger would need to be added to the source table which would then run the SSIS package.
The SSIS package would copy data to the destination table. I cant seem to think of any other way due to the restriction on destination server.
March 24, 2011 at 11:25 am
pwalter83 (3/24/2011)
The destination server has security implications and as such only the SSIS port is allowed on the server. This is the reason I think that a trigger would need to be added to the source table which would then run the SSIS package.
The SSIS package would copy data to the destination table. I cant seem to think of any other way due to the restriction on destination server.
Paul,
I am a bit more confused now. The integrations services server uses a port but that is only to start and manage a package controlled within that service. When a package is running it uses port 1433 in most cases for data access and this port has nothing to do with ssis. Can you clarify please?
CEWII
March 24, 2011 at 11:34 am
pwalter83 (3/23/2011)
Aren't triggers used by DBAs? 😉
If Service Broker is too far out of your comfort zone, I would go with the solution of Phil Parkin or the one of GSquared.
Actually what I meant was isn't service broker one of the jobs performed by DBAs exclusively ?
I missed this one.. I would say no, it isn't, like most things in SQL is in maintained and monitored by the DBA but unless the DBA had development responsibilities I would argue that the dev was responsible for building it. Service Broker is really just a queueing system within SQL and it is a great way to get something done that doesn't have to be done this second. Having the call with the trigger means that the trigger will not return until the package has finished running, even a well written small package can take 5 seconds to spool up and finish. And this would be a wait for EVERY change. I await your clarification to my other post.
CEWII
March 25, 2011 at 11:01 am
Paul,
I am a bit more confused now. The integrations services server uses a port but that is only to start and manage a package controlled within that service. When a package is running it uses port 1433 in most cases for data access and this port has nothing to do with ssis. Can you clarify please?
CEWII
Elliot,
What I meant to say was the IT support team has a restriction on the destination server and the only way they allow data to be copied to the server is through SSIS only.
There is no other way to copy data to a table on that server.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply