locked
How can i achieve this RRS feed

  • Question

  • I have a task to get a utility APP  which would basically help to move the application data from one system to another 
    Ex: Prod System to Dev System or Dev to Q.A system
    My database have multiple tables and have relationship with each other.
    Now I have wondering what would be the best way to move data so that the the data does not get messed up.
    Remember I have add to the existing data.

    Example:

    Production Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    1
    2   Tom     24    4
    3   Hary    25    1
    4   Arnold  26    3

    AddressDetails

    Id PlaceName
    1  newyork
    2  California
    3  canada
    4  Dubai

    Dev Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    2
    3   Hary    25    1


    AddressDetails

    Id PlaceName
    1  newyork
    2  California

     Now What I want to with my utility App is it will show me the list of a students as a list and I can select the student information that I want to move from prod to dev
    In the above scenario:

    My app should only show Tom and Arnold and I can select which i need to move . This should happen in a single transaction . This is just an example and I have 50 tables like this which have relationships within.
    Please provide your suggestion and the best way to achive this.
    Friday, January 12, 2018 10:51 AM

All replies

  • I have a task to get a utility APP  which would basically help to move the application data from one system to another 
    Ex: Prod System to Dev System or Dev to Q.A system
    My database have multiple tables and have relationship with each other.
    Now I have wondering what would be the best way to move data so that the the data does not get messed up.
    Remember I have add to the existing data.

    Example:

    Production Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    1
    2   Tom     24    4
    3   Hary    25    1
    4   Arnold  26    3

    AddressDetails

    Id PlaceName
    1  newyork
    2  California
    3  canada
    4  Dubai

    Dev Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    2
    3   Hary    25    1


    AddressDetails

    Id PlaceName
    1  newyork
    2  California

     Now What I want to with my utility App is it will show me the list of a students as a list and I can select the student information that I want to move from prod to dev
    In the above scenario:

    My app should only show Tom and Arnold and I can select which i need to move . This should happen in a single transaction . This is just an example and I have 50 tables like this which have relationships within.
    Please provide your suggestion and the best way to achive this.
    Friday, January 12, 2018 10:48 AM
  • so you're looking at only identifying the delta changes in data? what about schema changes that might have happened? like column addition, removal etc

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 12, 2018 11:11 AM
  • duplicate of

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/544adcf4-69f7-4522-9ac8-d90467e2eab4/how-can-i-achieve-this?forum=sqlintegrationservices

    Please dont open multiple threads for same question


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 12, 2018 11:17 AM
  • Schema changes will be not part of this App. The deployment team will male sure that both the environments will have identical schema.

    Friday, January 12, 2018 1:23 PM
  • When building the SQL statement for your source component, filter out the primary keys from the source table that are already in your destination table.  Such as StudentId NOT IN (blah...).  Then on your destination component, check the Keep identity box.  You should have a data flow task for each of your tables, and do them in order of student then address.
    Friday, January 12, 2018 2:26 PM
  • Could you please provide me a prototype for the package ?
    Friday, January 12, 2018 2:35 PM
  • Hi Rehan Mubarak,

    It looks like you may use lookup transformation in your package, please have a look: SSIS Upsert With Lookup Transform

    Check if this meets your requirement.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 15, 2018 8:15 AM
  • Schema changes will be not part of this App. The deployment team will male sure that both the environments will have identical schema.

    Looks like what you would need to implement is the merge functionality

    You can use a logic like below

    http://www.rad.pasfu.com/index.php?/archives/150-Insert,-Update,-and-Delete-Destination-table-with-SSIS.html



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Pirlo Zhang Wednesday, January 17, 2018 2:20 AM
    Monday, January 15, 2018 8:27 AM