Automation 1: Execute DTS from ASP/SProc

DTS is a wonderful tool in SQL Server. It allows you to automate importing, exporting, merging and transforming of data. This is great if you have access to the management console, what if you want to give the client a button to push?

Enter the following code into a new stored procedure.

CREATE PROCEDURE doPublish AS
exec master..xp_cmdshell 'dtsrun /Ssql1 /NPublish /E'

This will execute the DTSRun command line utility with the parameters

  • /S = SQL Server Host Name
  • /N = DTS Package Name
  • You can either use /E for “trusted connection” or /U/P to supply username and passwords

Once you have your new stored procedure, simply create an asp page that calls the stored procedure name as an SQL query, such as

cn.Execute("doPublish", , 1)

(Where cn is your database connection).

There you have it, DTS at the click of a button!

 

Downloading files to the browser with ADODB.Stream

This script demonstrates getting properties of a file, such as name and size, and using a response stream to send the file to the users browser.

VIEW SOURCE
http://www.aspalliance.com/chrisg/tools/view-downloadfile.asp

The script begins by calling the function “downloadFile” with the file we wish to download as a parameter, in this case getting it from the “file” request variable.

DownloadFile() first gets full path of specified file and checks to see if the file exists using the FileExists(strFilename) method of the filesystem object. It then gets the length of the file (the size) using the .size property of the file object.

Lines 19 and 20 show how we create the stream object and in line 23 we set the stream to binary. Line 42 uses the loadfromfile method to begin the process to read in the file, and after the headers have been set between lines 49 and 52, the file is output using Response.Binarywrite and the contents of the streams “read”.

Future improvements

I have fudged this script a bit by using a default content type of “octet”, you could improve this scripts functionality by specifying the corrent mime type.

Automation

One should always try to be more productive, even if it is only to speed up the dull bits so you have more time to do the fun stuff ;O)

The following three articles show how you can automate routine tasks with ASP, Windows Scripting Host (WSH) and SQL Server Data Transformation Services (DTS).

Each article stands on its own, but there is no reason why you could not mix and match as they are all complementary and execute in their own environments.

We start with DTS as this is the tool I have found most useful in automating workflow or moving/importing/exporting data at the push of a button ..

  1. Execute DTS from ASP/SProc
  2. Run command lines from ASP
  3. Call a web page from command line