monitoring-performance-counters-with-the-script-task.md

title	Monitoring Performance Counters with the Script Task
description	Monitoring Performance Counters with the Script Task
author	chugugrace
ms.author	chugu
ms.date	03/14/2017
ms.service	sql
ms.subservice	integration-services
ms.topic	reference
helpviewer_keywords	performance counters [Integration Services] SSIS Script task, performance counters custom performance counters [Integration Services] Script task [Integration Services], examples Script task [Integration Services], performance counters counters [Integration Services]
dev_langs	VB

Monitoring Performance Counters with the Script Task

[!INCLUDEsqlserver-ssis]

Administrators may need to monitor the performance of [!INCLUDEssISnoversion] packages that perform complex transformations on large amounts of data. The System.Diagnostics namespace of the [!INCLUDEmsCoName] [!INCLUDEdnprdnshort] provides classes for using existing performance counters and for creating your own performance counters.

Performance counters store application performance information that you can use to analyze the performance of software over time. Performance counters can be monitored locally or remotely by using the Performance Monitor tool. You can store the values of performance counters in variables for later control flow branching in the package.

As an alternative to using performance counters, you can raise the xref:Microsoft.SqlServer.Dts.Runtime.IDTSComponentEvents.FireProgress%2A event through the xref:Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel.Events%2A property of the Dts object. The xref:Microsoft.SqlServer.Dts.Runtime.IDTSComponentEvents.FireProgress%2A event returns both incremental progress and percentage complete information to the [!INCLUDEssISnoversion] runtime.

Note

If you want to create a task that you can more easily reuse across multiple packages, consider using the code in this Script task sample as the starting point for a custom task. For more information, see Developing a Custom Task.

Description

The following example creates a custom performance counter and increments the counter. First, the example determines whether the performance counter already exists. If the performance counter has not been created, the script calls the Create method of the PerformanceCounterCategory object to create it. After the performance counter has been created, the script increments the counter. Finally, the example follows the best practice of calling the Close method on the performance counter when it is no longer needed.

Note

Creating a new performance counter category and performance counter requires administrative rights. Also, the new category and counter persist on the computer after creation.

To configure this Script Task example

• Use an Imports statement in your code to import the System.Diagnostics namespace.

Example Code

Public Sub Main()  
  
    Dim myCounter As PerformanceCounter  
  
    Try  
        'Create the performance counter if it does not already exist.  
        If Not _  
        PerformanceCounterCategory.Exists("TaskExample") Then  
            PerformanceCounterCategory.Create("TaskExample", _  
                "Task Performance Counter Example", "Iterations", _  
                "Number of times this task has been called.")  
        End If  
  
        'Initialize the performance counter.  
        myCounter = New PerformanceCounter("TaskExample", _  
            "Iterations", String.Empty, False)  
  
        'Increment the performance counter.  
        myCounter.Increment()  
  
         myCounter.Close()  
        Dts.TaskResult = ScriptResults.Success  
    Catch ex As Exception  
        Dts.Events.FireError(0, _  
            "Task Performance Counter Example", _  
            ex.Message & ControlChars.CrLf & ex.StackTrace, _  
            String.Empty, 0)  
        Dts.TaskResult = ScriptResults.Failure  
    End Try  
  
End Sub  

  
public class ScriptMain  
{  
  
public void Main()  
        {  
  
            PerformanceCounter myCounter;  
  
            try  
            {  
                //Create the performance counter if it does not already exist.  
                if (!PerformanceCounterCategory.Exists("TaskExample"))  
                {  
                    PerformanceCounterCategory.Create("TaskExample", "Task Performance Counter Example", "Iterations", "Number of times this task has been called.");  
                }  
  
                //Initialize the performance counter.  
                myCounter = new PerformanceCounter("TaskExample", "Iterations", String.Empty, false);  
  
                //Increment the performance counter.  
                myCounter.Increment();  
  
                myCounter.Close();  
                Dts.TaskResult = (int)ScriptResults.Success;  
            }  
            catch (Exception ex)  
            {  
                Dts.Events.FireError(0, "Task Performance Counter Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);  
                Dts.TaskResult = (int)ScriptResults.Failure;  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }