How to run a DTS Package from ASP

[OceanDesigner]

I have been reading the many post on exporting tables to Excel in this and the SQL Server Programming forums. I have found a number of ways to do this:

1) Read in the recordset, write the Excel file with vbscript, and then redirect to the new Excel file.
2) Read in the recordset, write the tables in html or xml and view via the Excel aplet.
3) Execute a prescribed DTS package and redirect to the newly written file.

Since the set of data elements I want to export will always be the same, I think using a DTS package is the most convenient method. So the only thing I need to figure out is how to actually execute the DTS package from a web page. To connect to the database, I use the following connection string.

'OPEN OLE DB CONNECITON
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=sqloledb;Data Source=Thomas;" &_
"Initial Catalog=Customer;User ID=jeff;Password=five5
conn.Open

I will call the DTS Package "ExportData". How do I modify this connection string and then run the DTS Package?

Thanks, Jeff

 

[TheCandyman]

Have you already found a DTS package you want to use? If so which one? We need to know so we can help with the code.

 

[OceanDesigner]

I don't think I understand the question. I am using the SQL Server DTS component. Is that what you are asking?

I came accross another post in the Visual Interdev forum (

http://www.tek-tips.com/viewthread.cfm?SQID=269465&SPID=117&newpid=117&page=1)

which attempts to answer a similar question, but it is not really at my level. It suggests I store a call to dtsrun in a stored procedure and call the stored procedure from the ASP. So this is what I have for now.

DTS Package called "ExportData"
I have not written a store procedure yet. I skipped that step and tried to write the command directly into my web page. The code on the web page is:

'OPEN OLE DB CONNECITON
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=sqloledb;Data Source=Thomas;" &_
    "Initial Catalog=Customer;User ID=jeff;Password=five5
conn.Open

'RUN DTS PACKAGE
conn.Execute(dtsrun /SBabylon /NExportData)

'CLOSE
conn.close
set conn = nothing

I am really feeling my way around this one. I am not real sure if my execute command makes any sense.

 

[Veep]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;252987