Script File:

Overview

Script task providing basic manipulation operations for SQL Agent Jobs. You can enable, disable, start, stop or drop a Job. You can also check if job is enabled or the running status of the job. This is the description of the task parameters:

Connection – SQL Server connection manager (Connection).
IsJobVariable – if true, Job name is contained in package variable. Otherwise it is explicitly specified (String).
JobVariable – package variable containing Job name (String).
Job – Job selection (String).
Action – action the task performs.
ResultVariable – package variable where result is stored.
JobStep – optional parameter for StartJob action. Step from, which to start execution.

Requirements

Depends on Microsoft SQL Management Objects (SMO) for Job manipulation.

Depends on CozyRoc Script Task Plus component for implementing parameters user interface.

Library references

System
System.Data
Microsoft.SqlServer.ScriptTask
Microsoft.SqlServer.ManagedDTS
CozyRoc.SSISPlus
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2008 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.Collections Imports System.ComponentModel Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Smo.Agent Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"Connection", "IsJobVariable", "JobVariable", "Job", "Action", "JobStep", "ResultVariable"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Main() Dim result As Integer Dim job As Job Try ' Get job object. job = GetJob_() Select Case Me.Action Case ActionsType.EnableJob job.IsEnabled = True Call job.Alter() Case ActionsType.DisableJob job.IsEnabled = False Call job.Alter() Case ActionsType.IsJobEnabled Call StoreResult_(job.IsEnabled) Case ActionsType.StartJob If job.CurrentRunStatus = JobExecutionStatus.Idle Then Call job.Start(Me.JobStep) End If Case ActionsType.StopJob If job.CurrentRunStatus <> JobExecutionStatus.Idle Then Call job.Stop() End If Case ActionsType.IsJobRunning Call StoreResult_(job.CurrentRunStatus <> JobExecutionStatus.Idle) Case ActionsType.DropJob Call job.Drop() Case Else Throw New ApplicationException("Unhandled job action.") End Select result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Call Dts.Events.FireError( _ 0, _ String.Empty, _ ex.Message, _ String.Empty, _ 0) End Try Dts.TaskResult = result End Sub ' Main ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Base properties. Call result.Add("Connection") Call result.Add("IsJobVariable") Call result.Add(IIf(Me.IsJobVariable, "JobVariable", "Job")) Call result.Add("Action") Select Case Me.Action Case ActionsType.IsJobEnabled, ActionsType.IsJobRunning Call result.Add("ResultVariable") Case ActionsType.StartJob Call result.Add("JobStep") End Select Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Please specify SQL Server connection.") End If If String.IsNullOrEmpty(m_job) Then Throw New ApplicationException("Please specify Job.") End If Select Case Me.Action Case ActionsType.IsJobEnabled, ActionsType.IsJobRunning If String.IsNullOrEmpty(Me.ResultVariable) Then Throw New ApplicationException("Please specify ResultVariable.") End If End Select result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function GetJobsList() As Object() Dim result As Object() Try result = GetJobsList_() Catch ex As Exception Call MsgBox(ex.Message) End Try GetJobsList = result End Function ' GetJobsList ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function GetStepsList() As Object() Dim result As Object() Try result = GetStepsList_() Catch ex As Exception Call MsgBox(ex.Message) End Try GetStepsList = result End Function ' GetStepsList #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Job")> _ <Description("Specifies connection to SQL Server.")> _ <Connection("SMOConnectionType")> _ Public Property Connection() As String Get Connection = m_connection End Get Set(ByVal value As String) If m_connection <> value Then m_connection = value Me.Job = String.Empty m_jobsList = Nothing End If End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Job")> _ <Description("Specifies if Job property references package variable.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property IsJobVariable() As Boolean Get IsJobVariable = m_isJobVar End Get Set(ByVal value As Boolean) If m_isJobVar <> value Then m_isJobVar = value Me.Job = String.Empty End If End Set End Property ' IsJobVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Job")> _ <Description("Select variable containing job name.")> _ <Variable()> _ Public Property JobVariable() As String Get JobVariable = m_job End Get Set(ByVal value As String) Me.Job = value End Set End Property ' JobVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Job")> _ <Description("Select job to process.")> _ <List("GetJobsList")> _ Public Property Job() As String Get Job = m_job End Get Set(ByVal value As String) If m_job <> value Then m_job = value m_stepsList = Nothing End If End Set End Property ' Job ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Description("Select task action.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property Action() As ActionsType Get Action = m_action End Get Set(ByVal value As ActionsType) m_action = value End Set End Property ' Action ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Description("Specifies package variable where you would like to store result.")> _ <Variable()> _ Public Property ResultVariable() As String Get ResultVariable = m_resultVariable End Get Set(ByVal value As String) m_resultVariable = value End Set End Property ' ResultVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Description("Specifies from which Job step to start execution (optional).")> _ <List("GetStepsList")> _ Public Property JobStep() As String Get JobStep = m_step End Get Set(ByVal value As String) m_step = value End Set End Property ' JobStep #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property SMOConnectionType() As String() Get SMOConnectionType = New String() {"SMOServer"} End Get End Property ' SMOConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get specified SQL Server. Private Function GetServer_() As Server If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Please specify SQL Server connection.") End If GetServer_ = CType(Dts.Connections(Me.Connection).AcquireConnection(Nothing), Server) End Function ' GetServer_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get specified Job. Private Function GetJob_() As Job Dim jobName As String Dim vars As Variables If Me.IsJobVariable Then ' Job name is specified in variable. Call Dts.VariableDispenser.LockOneForRead(Me.JobVariable, vars) Try jobName = vars(Me.JobVariable).Value.ToString() Finally Call vars.Unlock() End Try Else jobName = Me.Job End If If String.IsNullOrEmpty(m_job) Then Throw New ApplicationException("Please specify Job.") End If GetJob_ = GetServer_().JobServer.Jobs(jobName) End Function ' GetJob_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of jobs in specified SQL Server. Private Function GetJobsList_() As Object() Dim jobs As ArrayList = New ArrayList() Dim server As Server If m_jobsList Is Nothing Then ' Load specified server list of jobs. server = GetServer_() For Each job As Job In server.JobServer.Jobs Call jobs.Add(job.Name) Next m_jobsList = New Object(jobs.Count) {} Call jobs.CopyTo(m_jobsList) End If GetJobsList_ = m_jobsList End Function ' GetJobsList_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of job steps. Private Function GetStepsList_() As Object() Dim steps As ArrayList = New ArrayList() Dim job As Job If m_stepsList Is Nothing Then ' Load job steps. job = GetJob_() For Each jobStep As JobStep In job.JobSteps Call steps.Add(jobStep.Name) Next m_stepsList = New Object(steps.Count) {} Call steps.CopyTo(m_stepsList) End If GetStepsList_ = m_stepsList End Function ' GetStepsList_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Store provided value in specified ResultVariable. Private Sub StoreResult_(ByVal value As Object) Dim vars As Variables Call Dts.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try vars(Me.ResultVariable).Value = value Finally Call vars.Unlock() End Try End Sub ' StoreResult_ #End Region ' Internals #Region "Attributes" Public Enum ActionsType EnableJob DisableJob IsJobEnabled StartJob StopJob IsJobRunning DropJob End Enum ' ActionsType Private m_connection As String Private m_isJobVar As Boolean Private m_job As String Private m_action As ActionsType = ActionsType.EnableJob Private m_resultVariable As String Private m_step As String Private m_jobsList() As Object Private m_stepsList() As Object #End Region ' Attributes End Class ' ScriptMain

SQL Job Agent Task

Overview

Requirements

Library references

ScriptMain

PASS Summit '10