I really need a solution a.s.a.p. so I am stating my case from the stand
point of what it is I am trying to do and what I have done.
Overview:
When a person comes into our website there is a feature that allows them to
generate a text file of information based on who they are.

Initial action:
The way I implemented this was to pass their unique identifier to a stored
procedure and return the results to an asp page. This has worked fine for 2
years. Now there is an issue because the file is too large for IIS to buffer
the file for some people.

New development:
I have redesigned the asp page to detect the number of records that might
be downloaded by the user. If the number is under a predetermined threshold
then the current method of downloading stays in effect.
If the number is above the threshold then I save the unique id, date of
request and I create a filename and I insert this information into a table.

Objective:
I want to schedule DTS to read the unique id and filename from the table
and generate a text file with the results from the stored procedure.

Currently in place:
I have a package with a Execute SQL Task that Reads from the table and
populates the global variables with the unique id and the filename.
I created an Active X task thinking this is how to write a text file.
I have an Execute SQL Task that executes the stored procedure but I need to
pass the unique id and I need to output the results to a text file.

So what I need is a solution that is clear to understand for me, I am new at
DTS, and hopefully some step by step examples.

I realize this is asking a lot but I appreciate your time and effort in
helping me reach a working solution.

Thank you
Gary

As I have said earlier. What does the Text file look like? Why is it
that a datapump will not work for you? Are you presenting the info in
the file in a non row/column format?


allan



"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote

Here is an example of the text file:
customername|address|city|state|zip|phone|item|shi pdate|promiseddate
Mike Lamp|1343 west st.|WA|90780|1112223333|33|08132005|08202005
Mary Adams|5546 North Ave.|AT|87899|4443335555|21|09102005|09172005

That is basicly the format of the text file.
You asked "Why is it that a datapump will not work for you? "
I don't know what you mean or how to feed the results from the SQL task to a
datapump.
The file is downloaded as a zip file, if that helps.

Thanks in advance.

"Allan Mitchell" wrote:

Ok

So from your previous post I presume this

The ExecuteSQL task gets you the name of a file and you pass in an ID.
In your example I actually think that this returns all the data as well
which is not what we want here.


You will not be using the ExecuteSQL task to do the pump to the text
file.

Set up a pump to a text file by specifying the SP that returns the data
you want to have in the text file.
Use this article to see how you can pass in a parameter and variable
(id)

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)


You will also need to change the name of the text file and for that you
can use this

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)


You will use the Global variable values.


Your package will look something like this



ExecuteSQL task



Points to consider.

The ExecuteSQL task if it simply gets a Filename could be brought inside
the asp page and passed into the package through the object model

Active Script task. If this just sets the filename then again you can
do this in the asp code by manipulating the connections collection

The datapump task you could change the SourceSQLStatement to be what you
want here i.e. proc with variable value.


Allan




"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote

Here is what I have done so far.
1.)Create a connection to my sql 2k database
2.)Create a Text File(destination) connection to a text file located on my
local drive.
3.) Create the globalvariables at the package level called gvcompid, and
gvFilename
4.)Create a Execute SQL Task to read from the table in sql and popluate the
global variable (this works fine)
5.)Select the transform data task and select my db connection then my text
file connection and create the arrow.
6.) double click the transform task and enter my stored procedure that
extracts the database from the database. I used the ? and mapped the gvCompid
to it.
7.) I ran the package and the text file was created but with the fake name I
prpovided when I created the text file in the first place.
8.) Created an ActiveX script task and followed the example in the document
(http://www.sqldts.com/default.aspx?200)
This errors out with error "system cannot find file specified"
So, that is where I am at to this point.
I appreciate you help and look forward to completing this.

As far as moving things inside the asp page and that type of stuff, I would
much rather get this wotking and then come back and address those issues.
Gary


"Allan Mitchell" wrote:

Well you are nearly there

So the only part we are missing is #8

What is probably happening is that there is maybe a slight disrepancy
with the location.

After you have assigned the new text file connection name in the Active
Script task can you MsgBox out what the value was? This should help to
try and identifiy why there may be a problem.

The Ax script must go before the datapump


Allan

"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote

I get a blank msgbox. so the activex script is not running before the
transform. so how do I get it to run in the correct order?

Thanks
"Allan Mitchell" wrote:

I have finally managed to get the dts package to function correctly.
Thanks to all the help from Allan Mitchell.



"Gary" wrote:

Use workflow

Have a look here

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)


Allan

"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote