Monitoring Performance Counters with the Script Task
Applies To: SQL Server 2016 Preview
Administrators may need to monitor the performance of Integration Services packages that perform complex transformations on large amounts of data. The System.Diagnostics namespace of the Microsoft .NET Framework 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 FireProgress event through the Events property of the Dts object. The FireProgress event returns both incremental progress and percentage complete information to the Integration Services runtime.
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.
- Use an Imports statement in your code to import the System.Diagnostics namespace.
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
| Stay Up to Date with Integration Services|
For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:
Visit the Integration Services page on MSDN
For automatic notification of these updates, subscribe to the RSS feeds available on the page.