Sp_msforeachtable In A DTS Package With Global Variables

Hi,

what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.

To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.

This is the code in the Execute SQL Task:

sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"

This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.

Finally I've written a cursor to the job which looks like this:

DECLARE @tablename varchar(35),
@loadnumber int

SELECT @loadnumber = ?

DECLARE delete_cursor CURSOR FOR

select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')

OPEN delete_cursor

FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)

BEGIN

exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)

FETCH NEXT FROM delete_cursor INTO @tablename

END

CLOSE delete_cursor

DEALLOCATE delete_cursor

But this code doesn't compile either...

Any suggestions on how to get the code working within DTS?

Stef