Export to Excel using DTS

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)

what is the best way to do this? DTS, BCP

thanks
FOCELBrett will be along shortly to offer up a much cleaner solution using BCP and two lines of code (one of which will call a stored procedure to mix up a margarita). In the meantime, may I humbly offer up:

You can create a package with a global variable indicating which SP to run. Have three transformations defined in the package and determine which transformation to execute in a VBScript (using the global variable).

I'm not sure what will happen to the other transformations (the two that are not executed). That may require more testing...

You can look for more hints at http://www.sqldts.com.

Regards,

hmscott