Dear pedro,

We cannot use CDC feature now. However, I set scheduler in every five minutes and using some other logic such as last modified date of each table.Insert and update operations are fine at this moment.

Is it possible to create another job to check source table ids and target table ids and then delete those ids in target table which are not found in source table. Any idea of job settings.

Thanks.

Asif

My Source and Target table both are same. Everytime data inserted or updated in the source table my etl job insert or update same data in target table migrating from source table. 

So operatios are
if source table has a new record. my etl job insert the rows to the target table.
if source table has updates some fields of any rows, etl job also update  fileds of target table.

I want if source table data is deleted, etl job will also delete target table rows.

Thanks again.

asif

i am extrememly sorry. let me explain you.

I have source table and target table.I read data from target table and keep in hash. Then I have tMap where source table is main and hash is lookup. Then tMap settings inner join and unique match model. tMap output link has two mssqloutput, one for insert and one for update. For Inert I have the above logic catchlookup inner join reject=true and false for update.


see the job design




Targettable -> tHashoutput
|
|
SourceTable \
                    \
                     tMap ----------tmssqloutput(forInsert - catch lookup inner join reject = true)
                 /        \
hashInput/            \
                             -------------tMssqlOutput(forUpdate - catch lookup inner join reject = false)

I wanted to know if add another link from tMap to mssqloutput for delete operation what should be the condition
catchlookup inner join reject.

Thanks.

Asif

Thanks pedro.

But you didn't write output table property value for delete operation
Catch output reject = ?
Catch lookup inner join reject = ?

Thanks.

asif

Hello Pedro,

Would you please help , how can I add delete operation along with insert and update operation.

scenerio:
Source table has data deleted -> target table will also delete the same data.

FYI, at this time, our migration project job,  tMap has match model->Unique match, join model->Inner Join and ForInsert Catch lookup inner join reject=true and ForUpdate, Catch lookup inner join reject=false.

These works fine. Now suddenly situation arises, I  need to implement delete operation with this condition. What can the tmap settings.

Thanking you.

Asif

I tried you example but cannot make it work. Please see images.

I have created two rows in bookingCard Table.Run the job. Successfully created two lines in shippingReport.
shippingCardId    shippingCardNo    shippingStatus
1    BC101         ACTIVE   
2    BC102         ACTIVE   


Then created one line in shippingProduct.
shippingProductId    shippingCardId    productGroup    billingMethod
1    1    AIR           CASH
     
It generates duplicate insert error.

Thanks.

asif

Thank you so much for your attention.
Ok, here is the example. I make it very simple. If this can be done, I hope rest I can follow. There are two input tables 1) shippingCard and 2) shippingProduct are joined(in my case 9 tables to be joined) and output table is the shippingReport table. shippingCardId field is used to join shippingProduct and  shippingCard Tables.

Table shippingCard
shippingCardId(pk);shippingCardNo;shippingStatus

Table shippingProduct
shippingProductId(pk);shippingCardId(fk);productGroup;billingmethod;

Output Table; shippingReport
shippingReportId(pk);shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
CASE1; Create shipping Card
shippingCardId(pk);shippingCardNo;shippingStatus
1;BC101;ACTIVE
output in shippingReport Table:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;NULL; BC101;null;null; (insert operation in output table)
CASE2; Create shipping Product
shippingProductId;shippingCardId;productGroup;billingmethod;
1;1;AIR;CASH
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;ACC;CASH (update operation in output table)
CASE3 ; Create another shipping Product (insert operation in output table)
shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CASH
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CASH
CASE4 ; Update productGroup from CASH TO CHEQUE
shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CHEQUE
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CHEQUE

Thanks.

Asif