Hi,

I am have a problem using a temp table in a stored procedure which is called
from DTS. Within the Transform Data Task Properties dialog the
Transformations for the Source does not display any fields. I know the
problem is caused by the temp table, what I want to know is there any work
around.

Thanks!

In message <OB2auPQGEHA.1228 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Kevin Potter
<temp (AT) temp (DOT) com> writes
Temp tables will work at run-time, but unfortunately it is virtually
impossible to use them in the designer. There are two options to get
round this-

Change the proc to use a permanent table. Then change it back once
design is complete.

Change the source to something fixed, for example a simple select from a
dummy table of the same structure that allows you to build the package.
Then once built use Disconnected Edit to change the SQL back to the
procedure.

Basically the Designer needs something it can work with to get the
required meta-data, but once you have finished designing you can change
back to the real code, and the run-time will work as we know it should.

Make sense?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org