'************************************************************************ ' Copy all scheduled jobs from serverA to serverB ' Removes any disabled jobs from the target (that's the Clean part) ' Bill Wunder '************************************************************************ Option Explicit Function Main() Dim oSourceSQLServer, sSourceSQLServerName, oSourceCategory, oSourceJob, oSourceJobStep, oSourceJobSchedule Dim oTargetSQLServer, sTargetSQLServerName, oTargetJob Dim oCategory, oJob, oJobStep, oJobSchedule Dim iError, I, iMaxCount, bSkipThisOne sSourceSQLServerName = DTSGlobalVariables("Source SQL Server") sTargetSQLServerName = DTSGlobalVariables("Target SQL Server") If sSourceSQLServerName = sTargetSQLServerName Then Err.Raise vbObjectError + 513, "Connect Servers", "Source and target cannot be same." Exit Function End If Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer") oSourceSQLServer.LoginSecure = True 'trusted oSourceSQLServer.LoginTimeout = 15 oSourceSQLServer.Connect sSourceSQLServerName Set oTargetSQLServer = CreateObject("SQLDMO.SQLServer") oTargetSQLServer.LoginSecure = True 'trusted oTargetSQLServer.LoginTimeout = 15 oTargetSQLServer.Connect sTargetSQLServerName 'clear all disabled jobs from the target server 'count down because value is decrimented when a job is removed For I = oTargetSQLServer.JobServer.Jobs.Count To 1 Step -1 If oTargetSQLServer.JobServer.Jobs(I).Enabled = False Then oTargetSQLServer.JobServer.Jobs.Remove (I) End If Next 'make sure all the job categories on source exist on target For Each oSourceCategory In oSourceSQLServer.JobServer.JobCategories Set oCategory = CreateObject("SQLDMO.Category") oCategory.Name = oSourceCategory.Name oCategory.Type = oSourceCategory.Type On Error Resume Next oTargetSQLServer.JobServer.JobCategories.Add oCategory iError = Err.Number On Error GoTo 0 If Not (iError = 14261 Or iError = 0) Then Err.Raise iError End If Next ' add all the enabled jobs on Source to Target as disabled For Each oSourceJob In oSourceSQLServer.JobServer.Jobs If oSourceJob.Enabled = True Then bSkipThisOne = False Set oJob = CreateObject("SQLDMO.Job") oJob.Name = oSourceJob.Name On Error Resume Next oTargetSQLServer.JobServer.Jobs.Add oJob iError = Err.Number On Error GoTo 0 If Not (iError = 14261 Or iError = 0) Then Err.Raise iError Else ' don't mess with a job that is enabled and has an active schedule on target If (iError = 14261) Then If (oTargetSQLServer.JobServer.Jobs.Item(oJob.Name).Enabled) _ And (oTargetSQLServer.JobServer.Jobs.Item(oJob.Name).HasSchedule) Then For Each oJobSchedule In oTargetSQLServer.JobServer.Jobs.Item(oJob.Name).JobSchedules If oJobSchedule.Enabled Then bSkipThisOne = True End If Next End If If Not (bSkipThisOne) Then Set oJob = oTargetSQLServer.JobServer.Jobs.Item(oJob.Name) End If End If If Not (bSkipThisOne) Then oJob.BeginAlter oJob.Category = oSourceJob.Category oJob.Description = oSourceJob.Description oJob.Enabled = False oJob.EmailLevel = oSourceJob.EmailLevel oJob.EventlogLevel = oSourceJob.EventlogLevel oJob.NetSendLevel = oSourceJob.NetSendLevel oJob.OperatorToEmail = oSourceJob.OperatorToEmail oJob.OperatorToNetSend = oSourceJob.OperatorToNetSend oJob.OperatorToPage = oSourceJob.OperatorToPage oJob.Owner = oSourceJob.Owner oJob.PageLevel = oSourceJob.PageLevel oJob.StartStepId = oSourceJob.StartStepId 'targetserver seems flakey 'fails if not asigned and fails if try assign and already (local) with 14269 On Error Resume Next oJob.ApplyToTargetServer ("(local)") iError = Err.Number On Error GoTo 0 If Not (iError = 0 Or iError = 14269) Then oJob.CancelAlter Err.Raise iError Exit Function End If ' drop existing steps and add from source If oJob.HasStep Then For I = oJob.JobSteps.Count To 1 Step -1 oJob.JobSteps.Remove (I) Next End If For Each oSourceJobStep In oSourceJob.JobSteps Set oJobStep = CreateObject("SQLDMO.JobStep") oJobStep.Name = oSourceJobStep.Name oJobStep.StepId = oSourceJobStep.StepId oJobStep.Command = oSourceJobStep.Command On Error Resume Next oJob.JobSteps.Add oJobStep iError = Err.Number On Error GoTo 0 If iError <> 0 Then oJob.CancelAlter Err.Raise iError Exit Function End If oJobStep.BeginAlter oJobStep.DatabaseName = oSourceJobStep.DatabaseName oJobStep.DatabaseUserName = oSourceJobStep.DatabaseUserName oJobStep.SubSystem = oSourceJobStep.SubSystem ' These errors if step does not preceed action oJobStep.OnFailStep = oSourceJobStep.OnFailStep oJobStep.OnFailAction = oSourceJobStep.OnFailAction oJobStep.OnSuccessStep = oSourceJobStep.OnSuccessStep oJobStep.OnSuccessAction = oSourceJobStep.OnSuccessAction oJobStep.OutPutFileName = oSourceJobStep.OutPutFileName oJobStep.RetryAttempts = oSourceJobStep.RetryAttempts oJobStep.RetryInterval = oSourceJobStep.RetryInterval On Error Resume Next oJobStep.DoAlter ' retry if the database refrerenced by a job is currently loading ' these really should only be log restores since this job is threaded with the system backups ' busy wait sucks but shouldn't happen to often While (iError = 927) oJobStep.DoAlter iError = Err.Number Wend On Error GoTo 0 If Not (iError = 927 Or iError = 0) Then Err.Raise iError End If Next ' step ' drop existing schedules and add from Source If oJob.HasSchedule Then For I = oJob.JobSchedules.Count To 1 Step -1 oJob.JobSchedules.Remove (I) Next End If For Each oSourceJobSchedule In oSourceJob.JobSchedules Set oJobSchedule = CreateObject("SQLDMO.JobSchedule") oJobSchedule.Name = oSourceJobSchedule.Name oJobSchedule.Enabled = oSourceJobSchedule.Enabled On Error Resume Next oJob.JobSchedules.Add oJobSchedule iError = Err.Number On Error GoTo 0 If iError <> 0 Then oJob.CancelAlter Err.Raise iError Exit Function End If oJobSchedule.BeginAlter oJobSchedule.Schedule.BeginAlter oJobSchedule.Schedule.ActiveEndDate = oSourceJobSchedule.Schedule.ActiveEndDate oJobSchedule.Schedule.ActiveEndTimeOfDay = oSourceJobSchedule.Schedule.ActiveEndTimeOfDay oJobSchedule.Schedule.ActiveStartDate = oSourceJobSchedule.Schedule.ActiveStartDate oJobSchedule.Schedule.ActiveStartTimeOfDay = oSourceJobSchedule.Schedule.ActiveStartTimeOfDay oJobSchedule.Schedule.FrequencyInterval = oSourceJobSchedule.Schedule.FrequencyInterval oJobSchedule.Schedule.FrequencyRecurrenceFactor = oSourceJobSchedule.Schedule.FrequencyRecurrenceFactor oJobSchedule.Schedule.FrequencyRelativeInterval = oSourceJobSchedule.Schedule.FrequencyRelativeInterval oJobSchedule.Schedule.FrequencySubDay = oSourceJobSchedule.Schedule.FrequencySubDay oJobSchedule.Schedule.FrequencySubDayInterval = oSourceJobSchedule.Schedule.FrequencySubDayInterval oJobSchedule.Schedule.FrequencyType = oSourceJobSchedule.Schedule.FrequencyType oJobSchedule.Schedule.DoAlter oJobSchedule.DoAlter Next ' schedule oJob.DoAlter End If 'skip this one End If 'iError = 14261 Or iError = 0 End If ' source job enabled Next 'Job oSourceSQLServer.DisConnect Set oSourceSQLServer = Nothing oTargetSQLServer.DisConnect Set oTargetSQLServer = Nothing Main = DTSTaskExecResult_Success End Function