locked
Transfer server objects only the if data is updated in the source table SSIS-package implementation RRS feed

  • Question

  • Hi,

    I have around 40 source table on server A.

    I will be using 4- TSO (Transfer Server Object) tasks,split for 10 tables each to transfer the data to server B as below in the pic.(will be a truncate and load).

    But I want to run this seq container only if the data on the source table has been updated.There is a field-'dateofupdation' in every table which changes as the data is updated.

    So can anyone please advice me on the setup of package/precedence constraint?

    Thanks in Advance

    Jenni




    Wednesday, August 5, 2020 4:22 PM

All replies

  • Hi Jennifer,

    But I want to run this seq container only if the data on the source table has been updated.There is a field-'dateofupdation' in every table which changes as the data is updated.

    1.May I know if you want to run the seq container only if the data on each of the source table has been updated?

    2.May I know if you have specified date for updating? Then you can compare the date with the value of dataofupdation.

    If so, we can use Execute SQL Task to extract the value of dataofupdation and store it into one variable.

    Then we can compare the value of variable with the specified date in Precedence Constraint.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Thursday, August 6, 2020 2:01 AM
  • Hi Mona,

    Thanks for your reply.

    1.May I know if you want to run the seq container only if the data on each of the source table has been updated?

    2.May I know if have specified date for updating? Then you can compare the date with the value of dataofupdation.

    Yes!As you suggested I can use execute seq task to get max(dateofupdation) on both the source and destination table and use in the expression.(@sourcetable>@destinationtable).

    But how would I do that for every respective table ?I mean, I want it to first execute the sql task for that table,validate the expression in the precedence constraint to true and then transfer that table (from the list of 10 tables in each TSO task) ??

    how to setup that for every table thats in the list of TSO task?

    Thanks in advance

    Jenni



    Thursday, August 6, 2020 3:13 AM
  • Hi Jennifer,

    Please check if each table is updated in the source Database using t-sql.

    Then choose the updated table in the TablesList.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 10, 2020 2:14 AM