Calling SSIS Package in a trigger

Question

Calling SSIS Package in a trigger

mpradeep23

SSCommitted

Points: 1868
More actions
January 10, 2013 at 12:03 am

#393914

Hi
i trying call SSIS Package from a Trigger is there any perforamnce issue to the database
thanks in advance
with best regards
pradeep

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

mpradeep23 (1/10/2013)
Hi
i trying call SSIS Package from a Trigger is there any perforamnce issue to the database
thanks in advance
with best regards
pradeep

Based on the limited information provided, my best answer is 'probably' and my advice would be to look for an alternative solution to whatever requirement you have been given.

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.

mpradeep23 SSCommitted Points: 1868 More actions · Answer 2

i have two tables in two sql servers i need two move data from one table to other on insert /update i had written a ssis package for this so when thier is update in one table then i need update it in to other table so for that can i call SSIS Package in Trigger so that will copy the data from source to destination

thanks

pradeep

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

mpradeep23 (1/10/2013)
i have two tables in two sql servers i need two move data from one table to other on insert /update i had written a ssis package for this so when thier is update in one table then i need update it in to other table so for that can i call SSIS Package in Trigger so that will copy the data from source to destination
thanks
pradeep

Imagine a process on server 1 which has been written by a C# guy who knows nothing about set-based programming. He is inserting 10,000 rows one at a time in a loop in his program. Do you really want a trigger to execute an SSIS package 10,000 times in just a few seconds?

Far better to run the package every few minutes & pick up any changes then. Or use CDC if you have a high enough version of SQL Server.

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.

mpradeep23 SSCommitted Points: 1868 More actions · Answer 4

CDC works if tables are in different instances of sql sever?

thank s

pradeep

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

What about setting up replication for the table(s) in question?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

mpradeep23 (1/10/2013)
CDC works if tables are in different instances of sql sever?
thank s
pradeep

Sure, CDC is an option but if you do not need a record of all historical data changes then it is likely overkill and still will not deliver real-time data to the downstream system. With CDC changes are queued on the source system. You'll still need a process to take those changes and push them to a downstream system which is likely going to best implemented as an SSIS package.

Most types of SQL Server Replication require no development, only configuration. Have you looked into? I think you would be interested in Transactional Replication with a Push Subscription from your upstream system to your downstream system which requires no development and delivers as close to real-time data on the subscriber as possible.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

If all you are doing is copying the data from one instance to another, then I would agree that replication is probably a far better solution. It sounds to me as if you are trying to develop something that is already built in. 😀