Home arrow ASP arrow Page 4 - Creating DTS Packages With SQL Server 2000
ASP

Creating DTS Packages With SQL Server 2000


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.

Author Info:
By: Tim Pabst
Rating: 5 stars5 stars5 stars5 stars5 stars / 327
February 01, 2002
TABLE OF CONTENTS:
  1. · Creating DTS Packages With SQL Server 2000
  2. · What is a DTS package?
  3. · Creating a DTS package
  4. · Exporting the results set to a text file
  5. · Executing our DTS package in ASP
  6. · Conclusion

print this article
SEARCH DEVARTICLES

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 ActiveX script tasks property page

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:

'*****************************************

' Visual Basic ActiveX Script

'*****************************************

Function Main()

On Error Resume Next

dim strRecord

dim strEmailBody

dim objFSO

dim objStream

dim objMail

dim objResults

const OUTPUT_FILE = "c:\salesreport.txt"

const EXECUTIVE_EMAILS = "tim@devarticles.com;mitchell@devarticles.com"

const fsoForWriting = 2

set objFSO = CreateObject("Scripting.FileSystemObject")

set objMail = CreateObject("CDONTS.NewMail")

set objResults = DTSGlobalVariables("gResults").Value

set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)

'Loop through the records and output each one

'to a file.

while not objResults.EOF

strRecord = "Store #" & objResults.Fields(0).value & _

"sold " & objResults.Fields(1).value & _

"items with payment type " & DTSGlobalVariables("gPaymentTerm").Value

objStream.WriteLine(strRecord)

objStream.WriteBlankLines(1)

objResults.MoveNext

wend

'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.AttachFile OUTPUT_FILE

objMail.Subject = strEmailBody

objMail.Send

if err.count = 0 then

Main = DTSTaskExecResult_Success

else

Main = DTSTaskExecResult_Failure

end if

End Function


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:

The global variables list

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:

Our new DTS package in Enterprise Manager

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.
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials