DSXchange

All , I have a question regarding loading and deleting from a same table.

Initial job design,

File-Tranformer-lookup-Join-DB2Connector.

Data looks like,

ID, NAME,START,END,STATUS
1234,John Smith,01/01/2015,12/31/2016,Pending
1234,John Smith,04/17/2015,12/31/2016,Pending

Now, as first record is the old record, I want to delete this record after loading in DB2 Connector stage. So, I can use a logic that of START of second row is >than previous then delete the record.

Any thoughts how to achieve this, Can we achieve this in a single job.

Thanks.

How about some details...

This "old" record, it is already there when you load the new one? If so, will there only ever be one old record in the table when you insert a new version? I'm asking because it sounds like you may be able to use the Delete then Insert write mode property:

Delete then insert: Replaces data in an existing table; runs the DELETE statement first, and then runs the INSERT statement.

If that ID is unique, you could key off that.

sam334 wrote:I want to delete this record after loading in DB2 Connector stage. So, I can use a logic that of START of second row is >than previous then delete the record.
Thanks.

You mean logical delete or that record will be deleted from the table?

There are things you can do before even sending the record to DB2 Connector. You can restrict that record in your job itself. I think if you sort on key and start and take the last record. Pass it through the lookup to get start of the record already there in the data base and if new record has start greater that previous, send it to DB2 in upsert mode else just drop it.


I prefer to keep history in Data warehouse and perform logical delete in there. But all depends on requirement.

Thanks everyone.
@Craig, both the record are in single excel file. So, when we get the first file it has both the records.but we want to store one which has recent start date,

OK. Rather than load both / everything and then go back and delete the ones you didn't really want, why not just load the ones you do want and ignore the rest? Seem like that would be a simple matter of making sure the records are sorted appropriately and then using a Remove Duplicates stage... unless I'm missing something.

Craig, did that and it worked. I sorted the data and removed duplicates based on the key and it is removing the old one.

Thanks.