Nightly Imports using DTS or Incremental Updating
Posted on 2004-08-11
I am trying to update a table using DTS. Here is the setup
1. AS/400 Machine with DB/2
2. MS SQL 2000
I am actually updating invoice history, since querying against the as/400 takes too long, so I decided to move the history files over to the SQL Server to improve performance. The history dates back quite a bit.
There are errors in the dates, ie. something could have been invoiced July 2006, (the year is wrong) ... As my original idea was just to select the max date on the SQL Server, then select all invoices past that date on the as400.
The invoice numbers are also erronous.
So, I am left with two choices.
1. Complete import of the table during off hours ( 2:00 am)
2. figure out some way of incremental updates. The best solution.
Can someone walk me through how to create/deploy a DTS package that can do this? Or, if there is a better way than DTS please let me know.