none
How to inactive old record and insert updated record into destination table without using staging tables in SSIS RRS feed

  • Question

  • Hi Team,

    It is possible to inactive old record and insert updated record into destination table without using staging tables in SSIS.

    In my destination table am adding two more fields like Checksum and isActive.

    table 1 in server 1

    table 2 in server 2

    Sample Data:

    Table 1-

    ID BirdName TypeOfBird ScientificName
    1 Eurasian Collared-Dove Dove Streptopelia
    2 Bald Eagle Hawk Haliaeetus Leucocephalus
    3 Coopers Hawk Hawk Accipiter Cooperii
    4 Bells Sparrow Sparrow Artemisiospiza Belli
    5 Mourning Dove Dove Zenaida Macroura
    6 Rock Pigeon Dove Columba Livia
    7 Aberts Towhee Sparrow Melozone Aberti
    8 Brewers Sparrow Sparrow Spizella Breweri
    9 Canyon Towhee Sparrow Melozone Fusca
    10 Black Vulture Hawk Coragyps Atratus

    Table 2-

    ID BirdName TypeOfBird ScientificName ChkSum isActive
    1 Eurasian Collared-Dove Dove Streptopelia -431711260 1
    2 Bald Eagle Hawk Haliaeetus Leucocephalus -930602208 1
    3 Coopers Hawk Hawk Accipiter Cooperii -1404287439 1
    4 Bells Sparrow Sparrow Artemisiospiza Belli 1260241452 1
    5 Mourning Dove Dove Zenaida Macroura 1833338614 1
    6 Rock Pigeon Dove Columba Livia 1036590421 1
    7 Aberts Towhee Sparrow Melozone Aberti -512603321 1
    8 Brewers Sparrow Sparrow Spizella Breweri -2032422573 1
    9 Canyon Towhee Sparrow Melozone Fusca 664005555 1
    10 Black Vulture Hawk Coragyps Atratus 1276845352 1

    Sql update query -

    update Table1 set TypeOfBird='Blk Hawk' where ID=10

    OUTPUT:


    Thanks Bala Narasimha

    Thursday, October 3, 2019 11:09 AM

All replies

  • Hi Bala,

    This should be doable by using the T-SQL MERGE statement.


    Arthur

    MyBlog


    Twitter

    Thursday, October 3, 2019 1:57 PM
    Moderator
  • Hi BaluChalla,

    We could use Derived Column Transformation to update column's value.

    Best Regards,

    Mona


    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

    Friday, October 4, 2019 3:27 AM
  • Hi Mona,

    Thanks for your quick response.

    Have one more question.

    In this case we have only 10 records and updating 1 record. If i have 10000 records in source table and people are updating randomly, in this case how we can capture updated data  without hard coding i.e dynamically how we will capture data into destination table.


    Thanks Bala Narasimha

    Friday, October 4, 2019 4:22 AM
  • Hi BaluChalla,

    Please use Lookup Transformation to match between 2 tables, then put no match data into Table2.

    Best Regards,

    Mona


    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


    Friday, October 4, 2019 8:14 AM
  • Hi Mona,

    Once again thanks.

    If you don't mind could you please elaborate step by step process.


    Thanks Bala Narasimha

    Friday, October 4, 2019 12:59 PM
  • Hi BaluChalla,

    Please refer to Lookup Transformation in SSIS.

    Best Regards,

    Mona


    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, October 7, 2019 9:55 AM
  • Hi Mona,

    Thanks for update.

    Based on your guidance have created a one package like bellow.

    In this package am getting expected results but am facing a issue while inactive the existing records.

    Could you please help on same. 


    Thanks Bala Narasimha

    Thursday, October 10, 2019 6:06 AM
  • Hi BaluChalla,

    Could you please share the issue in detail?

    Best Regards,

    Mona


    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

    Thursday, October 10, 2019 7:38 AM
  • Hi Mona,

    Thanks for reply.

    am not able to update and my struck in last step. 

    Could you please help how to update records 


    Thanks Bala Narasimha

    Thursday, October 10, 2019 10:18 AM
  • Hi BaluChalla,

    Could you please share the sql commands in your OLEDB Command?

    Why not put the no match data to OLEDB destination directly?

    Best Regards,

    Mona


    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

    Friday, October 11, 2019 6:43 AM