DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 906560 - Last Review: September 22, 2006 - Revision: 3.2

SUMMARY

When you use Microsoft SQL Server Business Intelligence Development Studio to create a Microsoft SQL Server 2005 Integration Services (SSIS) package, you can include a Script task in the SSIS package to execute a task.

For example, the task may write a collection of variable information to the Windows Application event log. You can create an SSIS package that contains a Data Flow task. This Data Flow task includes a Row Count transformation. You can use a Script task to write the data that was populated by the Row Count transformation to the Windows Application event log.

This article describes how to use a Script task to write information to the Windows Application event log.

MORE INFORMATION

This example assumes that you have created the following elements in the SSIS package:
  • A Data Flow task
  • A Script task
  • A connector from the Data Flow task to the Script task
  • In the Data Flow task, you created a Row Count transformation in the data flow.
When you execute the package, the Row Count transformation returns the row count data that you want to write to the Windows Application event log.

To do this, follow these steps in the SQL Server Business Intelligence Development Studio:
  1. When the Control Flow tab is active, right-click the design surface, and then click Variables. The Variables window appears in the left pane.
  2. In the Variables window, click Add variable to add a variable that is named mycount. By default, the data type of the new mycount variable is Int32.

    Note References to variable names are case-sensitive.
  3. Double-click the Data Flow task. The Data Flow tab appears.
  4. Double-click the Row Count transformation. The Advanced Editor dialog box appears.
  5. Set the value of the VariableName property to mycount.
  6. Click the Control Flow tab, and then double-click the Script Task. The Script Task Editor dialog box appears.
  7. Click the Script item in the left pane, and then change the value of the ReadOnlyVariables property to the following value:
    PackageName,StartTime,ExecutionInstanceGUID,mycount
    Note The PackageName, StartTime, and ExecutionInstanceGUID items are system variables. These system variables are used to write the package information to the Windows Application event log.
  8. In the Script Task Editor dialog box, click Design Script.
  9. Make sure that the following namespaces are included in your code before any other declarations:
    Imports System
    Imports System.Data
    Imports System.Math
    Imports System.Diagnostics
    Imports Microsoft.SqlServer.Dts.Runtime
  10. When a new Microsoft Visual Studio for Applications window appears, replace the following code sample with the code in the Main() method.
    	Dim varMyCount As Variable = Dts.Variables("mycount") '
    	Dim varPackageName As Variable = Dts.Variables("PackageName")
    	Dim varStartTime As Variable = Dts.Variables("StartTime")
    	Dim varInstanceID As Variable = Dts.Variables("ExecutionInstanceGUID")
    	Dim PackageDuration As Long
    
    	'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    	' Event log needs
    	'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    	Dim sSource As String
    	Dim sLog As String
    	Dim sEventMessage As String
    	Dim sMachine As String
    	'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    	PackageDuration = DateDiff("s", varStartTime.Value, Now())
    
    	sSource = "RowCountReporting from SSIS"
    
    	' We need the message posted to the Application event log.
    	
    	sLog = "Application"
    	sEventMessage = "Rows Processed: " & Chr(10) _
    	& "     case Rows:" + varMyCount.Value().ToString + Chr(10) _
    	& "=============================================" & Chr(10) _
    	& "The Package: " + varPackageName.Value().ToString _
    	& Chr(10) _
    	& "Started: " & varStartTime.Value().ToString _
    	& Chr(10) _
    	& "Current Time:" & System.DateTime.Now _
    	& Chr(10) _
    	& "=============================================" _
    	& Chr(10) _
    	& "Package Run Duration in seconds: " & PackageDuration _
    	& Chr(10) _
    	& "Execution GUID: " & varInstanceID.Value().ToString
    	sMachine = "."
    
    	If Not EventLog.SourceExists(sSource, sMachine) Then
    	    EventLog.CreateEventSource(sSource, sLog, sMachine)
    	End If
    
    	Dim ELog As New EventLog(sLog, sMachine, sSource)
    
    	ELog.WriteEntry(sEventMessage, EventLogEntryType.Information, 777, 2)
    
    	'###############################
    	Dts.TaskResult = Dts.Results.Success
  11. Close the Microsoft Visual Studio for Application window.
  12. In the Script Task Editor dialog box, click OK.
  13. Press F5 to execute the package.
When the package successfully runs, information that is similar to the following appears in the Windows Application event log:

Event Type: Information
Event Source: RowCountReporting from SSIS
Event Category: Disk
Event ID: 777
Date: 8/10/2005
Time: 11:30:51 PM
User: N/A
Computer: ComputerName
Description:
Rows Processed:
case Rows:3
=============================================
The Package: RowCountsInEventLog
Started: 9/14/2005 2:27:19 PM
Current Time:9/14/2005 2:27:19 PM
=============================================
Package Run Duration in seconds: 0
Execution GUID: {07406D68-487F-4733-BA3A-DA66A8AF7EFA}

For more information, see Help and Support Center at http://support.microsoft.com.

REFERENCES

For more information, see the following topics in Microsoft SQL Server 2005 Books Online:
  • Script Task
  • Programming the Script Task
  • How to: Set Task Properties Using a Task Editor
For more information about how to write to the Windows Application event log by using Microsoft Visual Basic, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/07347hdt.aspx (http://msdn.microsoft.com/en-us/library/07347hdt.aspx)

APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Keywords: 
kbsql2005ssis kbhowto KB906560
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support