Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Net5
Starting Member
1 Post |
 Posted - 2007-09-19 : 12:10:30
|
| I have a table in a SQL database called tblInvoices which I want to be updated from an accounts system (which stores invoices in a table called INVOICES) using an ODBC connection.So far I have done it by having a DTS package delete all the tblInvoices records and then import the whole lot from INVOICES into tblInvoices.This has worked fine whilst I have been developing the system but obviously isn't very efficient. I'd now like to change and not delete tblInvoices but instead have 2 routines - one of which inserts new invoices from INVOICES to tblInvoices if they don't already exist and one routine that updates the invoice in tblInvoices from INVOICES (it will only update invoices that have not been posted to the ledgers though and there is a 'posted' field to allow me to filter out invoices not posted)Now I can do this easily in an Access database with linked tables connecting to the ODBC accounts and SQL server tables using an update query and an append query but in DTS I'm just not sure which type of task I need to use. A data driven query seems most likely but it does seem to get very complicated with ActiveX transformations etc.Anyone have any ideas? |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2007-09-19 : 16:32:17
|
| Use a SQL task in DTS, if you must use DTS. Otherwise write a stored procedure. |
 |
|
|
|
|
|
Best type of DTS task to use