Tutorials » Creating DTS Packages With SQL Server 2000
Exporting the results set to a text fileClick 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:
'*****************************************
' 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 = "someone@somewhere.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:
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 (this needs to be changed to your email address in the code if you wish to see the results) 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.
