Can I get a DTS package to run in a loop until a table is empty?

Posted on 2005-04-07
Last Modified: 2008-02-01
Hello Experts,

I've been weening myself off Access though it is an easy tool to create stuff like this in.

I'd like to do this with a scheduled DTS package, here is how it is executing at the moment:

3 queries execute one after another-
1. Drop table DeleteDups
2. Make table query creates a table of ID numbers I would like to delete from another table
3. Delete query runs and deletes those ID numbers

I would like this to loop until the DeleteDups table has 0 rows (no more IDs to delete).

DeleteDups has only one column: MaxOfID

What would be the best way to do this?

- Mike
Question by:sph3rion
    LVL 13

    Expert Comment

    not sure i understand, but are you deleting a record at a time??
     execsql task : drop table deletedups
     execsql task  create your DeleteDups table and fill with the dups to delete
     execslq task : call a stored procedure
    In the stored procedure

    Delete SomeTable
    from   sometable  s
    join     DeleteDups  d ON d.MaxOfID = s.Id

    LVL 9

    Accepted Solution

    write a stored proc that executes these statements in a loop.

    basic algorithm:
    while RowCounter <> 0
      execute step 1
      execute step 2
      execute step 3
      RowCounter = rows in DeleteDups table.
    end loop

    you can then schedule this stored proc in DTS.
    LVL 1

    Author Comment

    I am not deleting a record at a time and @@rowcount <> 0 did the trick

    Kinda new at stored procs :)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now