Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calling SSIS Package in a trigger Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 12:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:49 AM
Points: 70, Visits: 313
Hi

i trying call SSIS Package from a Trigger is there any perforamnce issue to the database
thanks in advance

with best regards
pradeep
Post #1405226
Posted Thursday, January 10, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 5,245, Visits: 12,161
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1405259
Posted Thursday, January 10, 2013 1:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:49 AM
Points: 70, Visits: 313
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
Post #1405271
Posted Thursday, January 10, 2013 2:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 5,245, Visits: 12,161
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1405284
Posted Thursday, January 10, 2013 4:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:49 AM
Points: 70, Visits: 313
CDC works if tables are in different instances of sql sever?

thank s

pradeep
Post #1405333
Posted Thursday, January 10, 2013 4:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
What about setting up replication for the table(s) in question?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1405339
Posted Thursday, January 10, 2013 6:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 7,135, Visits: 12,745
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
Post #1405392
Posted Friday, January 11, 2013 12:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #1406222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse