| Groups | Blog | Home |
sql server dts : SP output to a text file using DTS
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
Here is an example of the text file: customername|address|city|state|zip|phone|item|shipdate|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. [quoted text, click to view] "Allan Mitchell" wrote:
> 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@discussions.microsoft.com> wrote in message > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > 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 >
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 [quoted text, click to view] "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 > | > | > Active Script task (sets the text file name) > | > | > DataPump 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@discussions.microsoft.com> wrote in message > news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > > > Here is an example of the text file: > > customername|address|city|state|zip|phone|item|shipdate|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: > > > > > > > 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@discussions.microsoft.com> wrote in message > > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > > > > 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 > > > > > > > > >
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 [quoted text, click to view] "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@discussions.microsoft.com> wrote in message > news:ED2C0858-85A6-4E48-99BE-277675DEC114@microsoft.com: > > > 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: > > > > > > > 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 > > > | > > > | > > > Active Script task (sets the text file name) > > > | > > > | > > > DataPump 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@discussions.microsoft.com> wrote in message > > > news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > > > > > > > > > Here is an example of the text file: > > > > customername|address|city|state|zip|phone|item|shipdate|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: > > > > > > > > > > > > > > > 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@discussions.microsoft.com> wrote in message > > > > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > > > > > > > > > > > > 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 > >
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 [quoted text, click to view] "Gary" <Gary@discussions.microsoft.com> wrote in message
news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > 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
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 | | Active Script task (sets the text file name) | | DataPump 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 [quoted text, click to view] "Gary" <Gary@discussions.microsoft.com> wrote in message
news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > Here is an example of the text file: > customername|address|city|state|zip|phone|item|shipdate|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: > > > > 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@discussions.microsoft.com> wrote in message > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > 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 > > > > >
:) I have finally managed to get the dts package to function correctly.
Thanks to all the help from Allan Mitchell. [quoted text, click to view] "Gary" 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: > > > 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@discussions.microsoft.com> wrote in message > > news:ED2C0858-85A6-4E48-99BE-277675DEC114@microsoft.com: > > > > > 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: > > > > > > > > > > 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 > > > > | > > > > | > > > > Active Script task (sets the text file name) > > > > | > > > > | > > > > DataPump 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@discussions.microsoft.com> wrote in message > > > > news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > > > > > > > > > > > > Here is an example of the text file: > > > > > customername|address|city|state|zip|phone|item|shipdate|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: > > > > > > > > > > > > > > > > > > > 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@discussions.microsoft.com> wrote in message > > > > > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > > > > > > > > > > > > > > > > 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.
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 [quoted text, click to view] "Gary" <Gary@discussions.microsoft.com> wrote in message
news:ED2C0858-85A6-4E48-99BE-277675DEC114@microsoft.com: > 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: > > > > 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 > > | > > | > > Active Script task (sets the text file name) > > | > > | > > DataPump 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@discussions.microsoft.com> wrote in message > > news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > > > > > > Here is an example of the text file: > > > customername|address|city|state|zip|phone|item|shipdate|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: > > > > > > > > > > > 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@discussions.microsoft.com> wrote in message > > > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > > > > > > > > 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. > > > > >
Use workflow
Have a look here Introduction to Workflow (http://www.sqldts.com/default.aspx?287) Allan [quoted text, click to view] "Gary" <Gary@discussions.microsoft.com> wrote in message
news:9A01363E-F29F-47C9-83F8-9138626D62E3@microsoft.com: > 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: > > > > 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@discussions.microsoft.com> wrote in message > > news:ED2C0858-85A6-4E48-99BE-277675DEC114@microsoft.com: > > > > > > 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: > > > > > > > > > > > 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 > > > > | > > > > | > > > > Active Script task (sets the text file name) > > > > | > > > > | > > > > DataPump 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@discussions.microsoft.com> wrote in message > > > > news:CEBD5ED2-BA7D-42D7-9D3B-71BF070D630A@microsoft.com: > > > > > > > > > > > > > Here is an example of the text file: > > > > > > customername|address|city|state|zip|phone|item|shipdate|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: > > > > > > > > > > > > > > > > > > > > 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@discussions.microsoft.com> wrote in message > > > > > > news:CB69A8F3-5F9F-4234-A45E-DC5FE6867056@microsoft.com: > > > > > > > > > > > > > > > > > > > > > > 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 >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |