I carved out the Transact-SQL from a SQL step in my SQL Server 2000 DTS
package into a stored procedure (sp_DTStest) to debug it in VS.Net 2003,
replacing the inline SQL step with a simple EXEC sp_DTStest, which worked
fine the first time in the VS.Net debugger.

When I try to execute the DTS package, I get mysterious errors about missing
table indexes (there is a SELECT .... FROM myTable WITH ( INDEX =
IX_SOMETHING) in the middle of the stored procedure. Table myTable is
dropped and recreated at the beginning of the sp. That table is loaded via
INSERT INTO/SELECT FROM a different table. Once myTable is loaded, the
indexe is created "just in time" for lookup purposes. Even stranger, the
stored procedure will no longer debug in the VS.Net debugger (via Step Into
Stored Procedure) nor will it run via SQL Analyzer.

Here's the pattern I notice:

1. Build the sp in VS.Net
2. Test new sp in VS.Net via debugger's Step Into Stored Procedure, which
works fine.
3. Test new sp in SQL Analyzer, which runs fine.
4. Run Job that uses DTSRUN to run the package and it fails in sp,
complaining about lack of existence of the "just in time" index mentioned in
a SELECT statement via the WITH feature.
5. Attempt to run the sp again in SQL Analyser, and it fails for the same
reason (index doesn't exist on specified table).
6. Attempt to single-step through the sp via VS.Net 2003 to see what is
wrong. The debugger skips over the entire sp as though it doesn't exist or
is some how marked invalid. Is this the real issue here?
7. Create a new, empty stored procedure called sp_DTStest2 and copy the
contents of sp_DTStest to sp_DTStest2.
8. Single-step through sp_DTStest2 via VS.Net 2003 debugger, which works.
9. EXEC sp_DTStest2 in SQL Analyzer which also works.
10. Delete sp_DTStest in SQL Administrator
11. Use VS.Net 2003 to rename sp_DTStest2 to sp_DTStest, the original name I
need.
12. Single-step through sp_DTStest to make sure it works - it does.
13. Start Job that invokes the DTS package that contains the EXEC sp_DTStest
task, which promptly fails again with the missing index error pointing to
the SQL task containing the EXEC sp_DTStest.

Is it possible that the DTS run-time is trying to do some type of trial-run
of the SQL task and, since the index in question won't be created until
"Just In Time", that the sp somehow gets marked invalid because the
SELECT..FROM ...WITH (INDEX = ix_something) technically won't work
"stand-alone", making it impossible to use a second time?

If the sp is being marked invalid, how can I tell that it is invalid (select
? from sys? where ?)? If invalid, short of deleting and recreating the sp,
is there a way to get it back into a "valid" state?

Should I change the SQL Task to EXEC sp_DTSfacade, a one-line sp that
invokes the real sp to keep this from happening again?