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

DTS package to import a csv file on a web server

Im trying to schedule a dts package to import a database table from a csv
file.
In enterprise manager when i attempt to create the dts process, im not sure
what datasource should i be using to connect to my csv file that resides on
the web server?
where do i specify me server to connect to; eg: www.myserver.com\dbRight-click Local Packages and select New Package, Look on Connection and
select Text File (Source). Specify the file name location on the network.
Perhaps you will need to create a shared folder on your web server and give
permissions to the SQL Server service account.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> Im trying to schedule a dts package to import a database table from a csv
> file.
> In enterprise manager when i attempt to create the dts process, im not sur
e
> what datasource should i be using to connect to my csv file that resides o
n
> the web server?
> where do i specify me server to connect to; eg: www.myserver.com\db
>
>|||thanks ben
I think my biggest problem is working out what my UNC path to my web server
is to locate my text file
should it look something like this
\\syd2004\www.bradyaust.com.au\db\
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
> Right-click Local Packages and select New Package, Look on Connection and
> select Text File (Source). Specify the file name location on the network.
> Perhaps you will need to create a shared folder on your web server and
> give
> permissions to the SQL Server service account.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "bj" wrote:
>|||Go to your web server, select the folder containing the text file,
right-click and select Sharing and Security, Share this folder, give
Permissions (very careful here), maybe remove permissions to Everyone, Add
the SQL Server service account, etc.
Let us suppose you shared your folder as MyData, then SQL Server would be
able to access the file using \\webservername\MyData\MyTextFile.
Again, pay special attention to the permissions you give to the shared folde
r.
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> thanks ben
> I think my biggest problem is working out what my UNC path to my web serve
r
> is to locate my text file
> should it look something like this
> \\syd2004\www.bradyaust.com.au\db\
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
>
>