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:
'*****************************************
' 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 FunctionTo 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").ValueThe 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.
Next: Executing our DTS package in ASP >>
More ASP Articles
More By Tim Pabst
