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,
Jenniferjennifer1970@.hotmail.com wrote:

> 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

I've never used a stored procedure in DTS, but i presume that if any
error occurs in SP, DTS won't catch it.
Try taking source from SP and execute it DTS.
Change:
exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E
with procedure code