Post reply

Calling SSIS Package in a trigger

  • 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

  • January 10, 2013 at 1:43 am

    #1575638

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 10, 2013 at 1:54 am

    #1575650

    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

  • January 10, 2013 at 2:28 am

    #1575661

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 10, 2013 at 4:17 am

    #1575703

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

    thank s

    pradeep

  • January 10, 2013 at 4:31 am

    #1575708

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

  • January 10, 2013 at 6:39 am

    #1575751

    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

  • January 11, 2013 at 12:20 pm

    #1576473

    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. 😀

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

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