i have been searching a bunch of different places, but haven't found enough relevant info on what I need.
I have done DTS imports before, but not this advanced. What I need to do is this:
I have 2 tables in my db for PRODUCTS & PURCHASEORDERS. PURCHASEORDERS contains main detailed Po information, and PRODUCTS contains Product details for each product on a PO. There can be multiple Products for each PO.
Now I have data (in 2 files) from an as400 export that has to be imported to MSSQL every 2 days. On importing this as400 data I need to check the MSSQL PURCHASEORDERS table to see if the PO_NUMBER exists. If it does not exist, then insert record from as400 into PURCHASEORDERS. If PO_NUMBER does exist then check if MSSQL field ON_BOARD_DATE has a value there - if has value do not update record, if not hhas value then update record instead of insert.
please help as i am not good with transformation scripting and have found no examples I could easily dissect. Please just a snippet of sample code to illustrate a insert/update algorithm would help greatly.
Much easier to just import thge files into staging tables and run an SP or series of SPs to merge the data to the production tables.
You can call the SPs from the DTS package but I would advise calling g them from whatever executes the package - e.g. another step in a job, VB app, SP, ...
In this way the import is separate from the processing so that if any part has t ochange you won't have much to rewrite.