Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Use DTS to delete all records before import?

    I want to modify a current DTS job I have. It simply copies records from one table to another after a given date. The catch is I only have read priviliges on the source table. I currently delete all the records from the destination table but have to use Query Analyzer to do it and then run the current package that is just a simple select statement. The statement in the DTS job is something like:

    Code:
    SELECT *
      FROM tablename
      WHERE date_field > '2004-09-30'
    I have been running this multiple times per month since every week or so I want to get more recent data to run a couple of reports. The source table is not ours, it is in a different department, so I can only read off of it. The destination server is ours so I have full priviliges there. I assume the fact that the source server allows me limited access that I cannot run a "delete from tablename" first even though it is on our table? Is there another way to set this up? Thanks again. You guys are the best.

    ddave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that you are setting this up using the DTS Wizard, there is a check box (I think in the transformations) that allows you to delete the rows in the target table before the copy. If you are using the DTS Designer, just add a job step to delete all of the rows in the target table before you copy.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    I am in Designer, sorry, I should have clarified. I would prefer to use Designer, as a matter of fact, if there is a way to do this and have it run overnight that would be great. What do you mean "add a job step"? I have tried to add a delete statement before the select * into statement but that didn't work.

    ddave

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Add an "Execute SQL Task" command that does the delete. Be sure that you specify the "Target" connection, and on success have it go on to your existing steps.

    -PatP

  5. #5
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13
    Yeah that's correct. Select the Execute SQL Task, use you connection to the SQL Destination database and write the query as:

    delete
    from tablename

    Add this as the first step and continue on Success
    TrevorW
    If at first you don't succeed, call it version 1.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •