sql server dts: I've tried this both ways but rather new to SSIS:

I have a stored procedure (uspReports) that returns several fields and I
would like to call it within a dts package, take out a specific column
(IncidentNumber) and place that column into a flat file. I am using a temp
table (#TempData) within ghe stored procedure, the last query is a select and
I am using explicit columns (not select * from x). This seems rather easy so
I must be missing something here.

In DTS, the data is returned after selecting the 'Preview' button but when I
try to map columns (through source, destination) I get an error (#TempData)
being an invalid object.

In SSIS, I do not get any columns back to use as outputs.

Any suggestions?

Yes, I have too experienced issues with temp tables in SSIS. Some things that
worked before SP1 stopped working after SP1 and I had to find workarounds.
Setting the RetainSameConnection property of the connection to True can
sometimes help, as can using global temporary tables rather than local
temporary tables.

In your case, the easy workaround is to place all the code of your stored
procedure in the OLE DB Source component. You set the Data access mode to SQL
Command, and paste the code of your stored procedure in the SQL Command text
box (replace the exec MyStoredProc with the code inside it). When you click
Preview, you will see all your columns. You should still set the
RetainSameConnection property of the connection to True. You may also need to
set the ValidataExternalMetadata property to False on downstream components
if they will be validated before the temporary table data becomes available.


Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk




[quoted text, click to view]