Post reply

Row level relation transaction

  • May 15, 2007 at 9:25 am

    #173696

    Hi

     

    I need a suggestion. I have a scenario as below while designing SSIS for ETL:

    I need to copy many tables from Source to Destination. But this involves data manipulation like:

    Source :

    TableA:: PKA(Auto generated PK),Col1,Col2

    TableB:: PKB(Auto generated PK),RKA(Foreign Key Lined with PKA).Col3,Col4

     

    Target:

    TableX:: PKX(Auto generated PK),PKA,Col1,Col2,Col3 [Col3 picked up using relation of PKA and RKA]

    TableY :: PKY(Auto generated PK), PKX,Col4 [Col4 picked up using relation of PKA and RKA]

     

    Can any one tell me how to do this Row-By-Row Transaction and transmitting scope_identity() value with out using Cursor?

     

    Many thanks in advance.

     

    Regards

    Utsab Chattopadhyay


    CDBA

  • May 15, 2007 at 9:38 pm

    #706651

    Hi Utsab,

    Wouldn't you like to first transfer data to TableX and then transfer data to TableY?

    Gogula

  • May 16, 2007 at 4:33 am

    #706715

    Thanks for the reply. But per me it will not be possible as I need to take PKX value for each relevant row [That is scope_identity() ] and Col4 is not a primary key L. So it may be duplicated.

     

    Please advice.

     

    Thanks

    Utsab Chattopadhyay


    CDBA

  • May 16, 2007 at 4:58 am

    #706720

    Hi,

    But you could do this:

    First transfer data to TableX using a Data Flow task. Then you could Have another Data flow task where you would have data coming from the TableB (Col4) and TableA (PK4) as One source and another source which will be having PKX and PKA from TableX and then join them using a Merge Join task and direct the output to TableY.

    Regards

    Gogula

Viewing 4 posts - 1 through 3 (of 3 total)

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