August 20, 2004 at 6:02 pm
I'm pretty new to DTS and SQL Server. All I'm wanting to do is Connect to an Oracle DB (got that) and Insert records into a table on the SQL Server (got that). My problem is I want to delete the contents of the table each morning and import new data.
So, my remedial question is were in the process do I delete my current records and what's the best way to do it? I had built an 'Execute SQL Task' to do a Truncate on my table, then I was going to insert a workflow to connect the Truncate SQL Exec to the DB Connection and then another workflow to the SQL Exec that does the data insert.
A little direction and 'best way to do it' info would help.
Thanks,
Micah
August 20, 2004 at 11:29 pm
Hi
Run the DTS program, select the source Database and the destination database. Then select the tables that needs to be imported. After that click on the transform button aganist each table you ahve selected. On the Column Mappings tab of the transform window select th option "Delete rows in destination table". by default the option "Append rows in destination table" is selected.
Hope this should solve your problem
August 23, 2004 at 9:48 am
Micah,
It sounds like you've got the right idea. I would use a Transform Data task to copy the data, since an Execute SQL task can only use one connection.
If I built it, the package would have
Execute SQL task for the truncate --> workflow --> SQL Server connection --> Transform Data task --> Oracle connection
Jomy is referring to using the Import/Export Wizard rather than DTS Designer.
Greg
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply