Data Transformation Services (DTS) were added to SQL Server 7 and allow us to combine several data-related tasks into one common object. In this article Tim shows us how to create a DTS package with SQL Server 2000 that will access a database and email the results of a query to some sales executives. He also shows us how to execute and error trap DTS packages from within an ASP script.
Creating DTS Packages With SQL Server 2000 - Exporting the results set to a text file (Page 4 of 6 )
Click on the icon to add an ActiveX scripting task to our DTS package. When its properties page appears, you'll notice that we have a text box containing some strange looking VBScript:
The Main() function is executed whenever a call to our new ActiveX script task takes place. Inside this function, we can instantiate new COM objects using the CreateObject() method, work with variables, or do anything else that VBScript is capable of. Enter the following code for the Main function:
"items with payment type " & DTSGlobalVariables("gPaymentTerm").Value
'Create the body of the email
strEmailBody = "Good morning," & vbCrLf & _
"Please find attached this months sales " & _
"reports. If there are any problems, then " & _
"please email the sales department."
'Attach the file to an email and send it
objMail.To = EXECUTIVE_EMAILS
objMail.Subject = "Sales Report"
objMail.Importance = 2 'High
objMail.Subject = strEmailBody
if err.count = 0 then
Main = DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
To keep this article simple, I won't go into too much detail about the code inside of the Main() function. As you can see, however, we instantiate a new email message and FileSystemObject that allow us to write to files. Remember how we defined a global output variable earlier? This variable represents our record set, and in our code we use the following line to get the results of our query into a recordset object:
set objResults = DTSGlobalVariables("gResults").Value
The DTSGlobalVariables collection contains the input and output parameters that we have defined for our DTS package. All global variables are listed on the ActiveX script tasks property page in a list down the left:
We can add a reference to any of these global variables by simply double clicking on them. Once the VBScript code has extracted each record from the record set and wrote them into the c:\salesreport.txt file, we use a new CDO.NewMail object to attach that file to an email and send it to some "executives" email address (in this example it's me and Mitchell) using our IIS SMTP mail server.
Our ActiveX scripting task must return a value indicating whether it succeeded or failed. We have two possible choices: DTSTaskExecResult_Success, or DTSTaskExecResult_Failure. In our example we have used the err.count value to determine if any errors occurred. If they didn't, we return DTSTaskExecResult_Success.
On the other hand, if an error did occur, we return DTSTaskExecResult_Failure. There's one important thing I should mention here: Normally, if you caught an error in an ActiveX script task, you would fire off another scripting task to handle that error, maybe writing to a log file, etc. To keep our example simple however, we will simply return DTSTaskExecResult_Failure, indicating that our script task failed somewhere.
That covers actually creating our DTS package. Click OK to close all open property pages. The DTS designer will ask if we want to save our package. Enter "SalesPkg" as the package name and click the OK button. In Enterprise Manager you should see our new DTS package under the Local Packages leaf of the Data Transformation Services node of the pubs database:
If you right click on our new DTS package, you can go back into the DTS designer, execute it, or even schedule its execution. Let's now look at how we can use ASP script and some simple COM object instantiation to execute our DTS package programmatically.