DTS Package With Unexpected Issues

I've got a DTS package that runs an active-x script. The script is
simple - it runs a stored procedure and saves the results to a CSV
file. I kept getting this error message when trying to run it saying
that the recordset object I was using could not be used when closed.
Well, it didn't make a whole lot of sense to me as to why that was
happening, and it doesn't realte to my question except to give you a
sense of what I'm trying to do. After spending an inordinate amount of
time on that... I decided to just create a SQL Server connection
object and an Excel Object and then use a transformation to load the
query results. Simple enough, or so I thought. So in the
transformation object under the Source tab, I typed in the query to run
the Stored Procedure:

Declare @S nvarchar(30)
Declare @E nvarchar(30)
SET @S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'
SET @E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'
exec CTI_REPORT_Q_ACTIVITY_DETAIL @S, @E

And then I clicked on the preview button. I got the message that no
rowset was returned. In a way, that explains the issue with the
Active-X script. BUT, I know darn well it returns data. It returns
438 rows of data when I run this in Query Analyser.

So, here's my question....how could that be? Is there some issue that
DTS packages have with temporary tables? I do use a couple in the
Stored Procedure. Without having to post the stored procedure and
tables, etc. could someone let me know if they've run into something
like this before?

Thanks,
Jennifer