private double GetJobStepDuration(JobStep jobStep)
{
const double DiagonalMovementCost = 1.41421356237;
switch (jobStep.Type)
{
case JobType.Harvest:
{
var res = (Resource)jobStep.Placeable;
return(res.GatherDuration * ResourceHarvestableAmount(res));
}
case JobType.StepMove:
return(jobStep.Location == Location ? 0 : jobStep.Location.X != Location.X && jobStep.Location.Y != Location.Y ? DiagonalMovementCost : 1);
case JobType.DropResources:
case JobType.EnterWorkplace:
case JobType.StepRetrieveResources:
return(0);
case JobType.Production:
return(ChosenWorkplace.ProductionChain.TimeRequired.TotalSeconds);
}
throw new InvalidOperationException();
}
internal static AgentJobStep ConvertToAgentJobStep(JobStep step, LogSourceJobHistory.LogEntryJobHistory logEntry, string jobId)
{
AgentJobStepInfo stepInfo = new AgentJobStepInfo();
stepInfo.JobId = jobId;
stepInfo.JobName = logEntry.JobName;
stepInfo.StepName = step.Name;
stepInfo.SubSystem = step.SubSystem;
stepInfo.Id = step.ID;
stepInfo.FailureAction = step.OnFailAction;
stepInfo.SuccessAction = step.OnSuccessAction;
stepInfo.FailStepId = step.OnFailStep;
stepInfo.SuccessStepId = step.OnSuccessStep;
stepInfo.Command = step.Command;
stepInfo.CommandExecutionSuccessCode = step.CommandExecutionSuccessCode;
stepInfo.DatabaseName = step.DatabaseName;
stepInfo.DatabaseUserName = step.DatabaseUserName;
stepInfo.Server = step.Server;
stepInfo.OutputFileName = step.OutputFileName;
stepInfo.RetryAttempts = step.RetryAttempts;
stepInfo.RetryInterval = step.RetryInterval;
stepInfo.ProxyName = step.ProxyName;
AgentJobStep jobStep = new AgentJobStep();
jobStep.stepId = logEntry.StepID;
jobStep.stepName = logEntry.StepName;
jobStep.stepDetails = stepInfo;
jobStep.message = logEntry.Message;
jobStep.runDate = step.LastRunDate.ToString();
jobStep.runStatus = (Contracts.CompletionResult)step.LastRunOutcome;
return(jobStep);
}
public override void Update(Job entity)
{
Job persistent = this.Load(entity.ID);
persistent.RowVersion = BitConverter.GetBytes(DateTime.UtcNow.ToBinary());
this.DbContext.Entry(persistent).CurrentValues.SetValues(entity);
this.DbContext.Entry(persistent).State = EntityState.Modified;
foreach (JobStep step in persistent.Steps.Where(s => entity.Steps.All(cs => cs.StepType != s.StepType)).ToArray())
{
persistent.Steps.Remove(step);
DbContext.Entry(step).State = EntityState.Deleted;
}
foreach (JobStep step in entity.Steps.Where(s => persistent.Steps.All(cs => cs.StepType != s.StepType)).ToArray())
{
persistent.Steps.Add(step);
}
foreach (JobStep step in persistent.Steps)
{
JobStep currentValues = entity.Steps.Single(s => s.StepType == step.StepType);
this.DbContext.Entry(step).CurrentValues.SetValues(currentValues);
}
}
static void Main(string[] args)
{
Server server = new Server(".");
// Get instance of SQL Agent SMO object
JobServer jobServer = server.JobServer;
Job job = null;
JobStep step = null;
JobSchedule schedule = null;
// Create a schedule
schedule = new JobSchedule(jobServer, "Schedule_1");
schedule.FrequencyTypes = FrequencyTypes.OneTime;
schedule.ActiveStartDate = DateTime.Today;
schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0);
schedule.Create();
// Create Job
job = new Job(jobServer, "Job_1");
job.Create();
job.AddSharedSchedule(schedule.ID);
job.ApplyToTargetServer(server.Name);
// Create JobStep
step = new JobStep(job, "Step_1");
step.Command = "SELECT 1";
step.SubSystem = AgentSubSystem.TransactSql;
step.Create();
}
public JobStepsActions(
CDataContainer dataContainer,
JobData jobData,
AgentJobStepInfo stepInfo,
ConfigAction configAction)
{
this.configAction = configAction;
this.DataContainer = dataContainer;
this.jobData = jobData;
if (configAction == ConfigAction.Create)
{
this.data = new JobStepData(jobData.JobSteps);
}
else
{
JobStep jobStep = GetJobStep(this.jobData, stepInfo.StepName);
this.data = new JobStepData(jobStep, jobData.JobSteps);
}
// load properties from AgentJobStepInfo
this.data.ID = stepInfo.Id;
this.data.Name = stepInfo.StepName;
this.data.Command = stepInfo.Command;
this.data.Subsystem = AgentUtilities.ConvertToAgentSubSytem(stepInfo.SubSystem);
}
/// <summary>
/// Converts a JobStep model to an AzureSqlElasticJobStepModel
/// </summary>
/// <param name="resourceGroupName">The resource group name</param>
/// <param name="serverName">The server name</param>
/// <param name="agentName">The agent name</param>
/// <param name="jobName">The job name</param>
/// <param name="stepName">The job step name</param>
/// <param name="resp">The JobStep model</param>
/// <returns>An AzureSqlElasticJobStepModel</returns>
private static AzureSqlElasticJobStepModel CreateJobStepModelFromResponse(
string resourceGroupName,
string serverName,
string agentName,
string jobName,
string stepName,
JobStep resp)
{
AzureSqlElasticJobStepModel jobStep = new AzureSqlElasticJobStepModel
{
ResourceGroupName = resourceGroupName,
ServerName = serverName,
AgentName = agentName,
JobName = jobName,
StepName = stepName,
TargetGroupName = new ResourceIdentifier(resp.TargetGroup).ResourceName,
CredentialName = new ResourceIdentifier(resp.Credential).ResourceName,
CommandText = resp.Action.Value,
InitialRetryIntervalSeconds = resp.ExecutionOptions.InitialRetryIntervalSeconds,
MaximumRetryIntervalSeconds = resp.ExecutionOptions.MaximumRetryIntervalSeconds,
RetryAttempts = resp.ExecutionOptions.RetryAttempts,
RetryIntervalBackoffMultiplier = resp.ExecutionOptions.RetryIntervalBackoffMultiplier,
TimeoutSeconds = resp.ExecutionOptions.TimeoutSeconds,
Output = CreateJobStepOutputModel(resp),
ResourceId = resp.Id,
StepId = resp.StepId,
Type = resp.Type,
};
return(jobStep);
}
/// <summary>
/// 创建步骤 2 ,开始Billing
/// </summary>
/// <param name="jb">Job 实例</param>
/// <param name="databaseName">数据库名</param>
/// <param name="strSQl">SQL命令</param>
public void CreateStartBilling(Job jb, string databaseName, string strSQl)
{
try
{
JobStep jbstp = new JobStep(jb, "开始Billing");
//数据库
jbstp.DatabaseName = databaseName;
//计划执行的SQL命令
jbstp.Command = strSQl;
//* 制定执行步骤
//成功时执行的操作
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
//jbstp.OnSuccessAction = StepCompletionAction.GoToStep;
//jbstp.OnSuccessStep = 3;
//失败时执行的操作
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
//创建 SQL 代理实例的作业步骤.
jbstp.Create();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 创建步骤 2 ,开始Billing
/// </summary>
/// <param name="jb">Job 实例</param>
/// <param name="databaseName">数据库名</param>
/// <param name="strSQl">SQL命令</param>
public void CreateStartBilling(Job jb, string databaseName, string strSQl)
{
try
{
JobStep jbstp = new JobStep(jb, "开始Billing");
//数据库
jbstp.DatabaseName = databaseName;
//计划执行的SQL命令
jbstp.Command = strSQl;
//* 制定执行步骤
//成功时执行的操作
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
//jbstp.OnSuccessAction = StepCompletionAction.GoToStep;
//jbstp.OnSuccessStep = 3;
//失败时执行的操作
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
//创建 SQL 代理实例的作业步骤.
jbstp.Create();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// loads the step details for editing
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void stepListBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (stepListBox.SelectedIndex != -1)
{
JobStep step = (JobStep)stepListBox.SelectedItem;
stepNameTextBox.Text = step.StepName;
stepFetchTextBox.Text = step.StepFetch;
stepDisablePlugins.IsChecked = step.DisablePlugins;
stepTypeComboBox.IsEnabled = true;
stepTypeComboBox.SelectedValue = "Create and update";
if (step.NtoNIntersectTable)
{
stepTypeComboBox.SelectedValue = "Intersect Table";
}
else if (step.UpdateOnly)
{
stepTypeComboBox.SelectedValue = "Update only";
}
else if (step.CreateOnly)
{
stepTypeComboBox.SelectedValue = "Create only";
}
}
else
{
stepNameTextBox.Text = string.Empty;
stepFetchTextBox.Text = string.Empty;
stepTypeComboBox.SelectedIndex = 0;
//updateOnlyCheckBox.IsChecked = false;
}
}
/// <summary>
/// 创建步骤 1 ,杀掉UTP进程
/// </summary>
/// <param name="jb">Job 实例</param>
/// <param name="databaseName">数据库名</param>
/// <param name="strSQl">SQL命令</param>
public void CreateUTPKill(Job jb, string databaseName, string strSQl)
{
try
{
JobStep jbstp = new JobStep(jb, "KillUTP");
//数据库
jbstp.DatabaseName = databaseName;
//计划执行的SQL命令
jbstp.Command = strSQl;
//成功时执行的操作
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
//失败时执行的操作
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
//创建 SQL 代理实例的作业步骤.
jbstp.Create();
}
catch (Exception)
{
throw;
}
}
private JobResult RunJob(Job job)
{
int currentJobStep = 1;
job.CurrentExecutionStatus = ExecutionStatus.Running;
while (currentJobStep > 0)
{
JobStep step = job.Steps.Find(x => x.Id == currentJobStep);
JobStepResult result = RunJobStep(step);
_db.LogJobStep(result);
if (result.IsError)
{
if (step.StepIdOnError > 0)
{
currentJobStep = step.StepIdOnError;
}
job.CurrentExecutionStatus = ExecutionStatus.Error;
}
else
{
currentJobStep = step.NextStepId;
}
}
job.CurrentExecutionStatus = ExecutionStatus.Finished;
//Log result
return(null);
}
private void TgGenerateJob()
{
if (_DwServer.JobServer.Jobs.Contains(TgJobNameExecStoredProcedureETL))
{
_TgJob = _DwServer.JobServer.Jobs[TgJobNameExecStoredProcedureETL];
_TgJob.Drop();
}
_TgJob = new Job(_DwServer.JobServer, TgJobNameExecStoredProcedureETL);
_TgJob.Create();
JobStep aJobStep = new JobStep(_TgJob, "Execute ETL");
aJobStep.DatabaseName = DwDbName;
aJobStep.SubSystem = AgentSubSystem.TransactSql;
aJobStep.Command = string.Format("Exec {0}", TgExecStoredProcedureETL);
aJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
aJobStep.OnFailAction = StepCompletionAction.QuitWithFailure;
aJobStep.Create();
JobSchedule SQLSchedule = new JobSchedule(_TgJob, "Execute ETL daily at night");
SQLSchedule.FrequencyTypes = FrequencyTypes.Daily;
SQLSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once;
SQLSchedule.ActiveStartTimeOfDay = new TimeSpan(0, 16, 0);
SQLSchedule.FrequencyInterval = 1;
SQLSchedule.ActiveStartDate = DateTime.Now;
SQLSchedule.ActiveEndDate = new DateTime(9999, 12, 30);
SQLSchedule.ActiveEndTimeOfDay = new TimeSpan(23, 59, 0);
SQLSchedule.Create();
_TgJob.ApplyToTargetServer(DwDbServerName);
_TgJob.Refresh();
}
private void LoadAVP_btn_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "c:\\Microscan\\Vscape\\Jobs";
openFileDialog1.Filter = "AVP Files|*.avp";
openFileDialog1.Title = "Select an AVP File";
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
m_Dev.StopAll();
m_RepCon1.Disconnect();
SetupManager1.RootStep = null;
while (m_Job.Count > 0)
{
m_Job.Remove(1);
}
m_Job = null;
m_Job = new JobStep();
m_Job.Load(openFileDialog1.FileName);
}
}
/// <summary>
/// loads the job from a file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void loadJobButton_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "XML file|*.xml";
openFileDialog1.Title = "Open job file";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
System.IO.StreamReader sr = new
System.IO.StreamReader(openFileDialog1.FileName);
string jobdata = (sr.ReadToEnd());
sr.Close();
XmlDocument xml = new XmlDocument();
try
{
xml.LoadXml(jobdata);
stepListBox.Items.Clear();
guidMappingGridView.Rows.Clear();
saveConnectionsCheckBox.Checked = false;
sourceTextBox.Text = string.Empty;
targetTextBox.Text = string.Empty;
XmlNodeList stepList = xml.GetElementsByTagName("Step");
foreach (XmlNode xn in stepList)
{
JobStep step = new JobStep();
step.StepName = xn.SelectSingleNode("Name").InnerText;
step.StepFetch = xn.SelectSingleNode("Fetch").InnerText;
step.UpdateOnly = Convert.ToBoolean(xn.Attributes["updateOnly"].Value);
stepListBox.Items.Add(step);
}
XmlNodeList configData = xml.GetElementsByTagName("JobConfig");
mapBuCheckBox.Checked = Convert.ToBoolean(configData[0].Attributes["mapBuGuid"].Value);
mapCurrencyCheckBox.Checked = Convert.ToBoolean(configData[0].Attributes["mapCurrencyGuid"].Value);
XmlNodeList mappingList = xml.GetElementsByTagName("GuidMapping");
foreach (XmlNode xn in mappingList)
{
string sourceId = xn.Attributes["source"].Value;
string targetId = xn.Attributes["target"].Value;
guidMappingGridView.Rows.Add(sourceId, targetId);
}
XmlNodeList connectionNodes = xml.GetElementsByTagName("ConnectionDetails");
if (connectionNodes.Count > 0)
{
sourceTextBox.Text = connectionNodes[0].Attributes["source"].Value;
targetTextBox.Text = connectionNodes[0].Attributes["target"].Value;
saveConnectionsCheckBox.Checked = Convert.ToBoolean(connectionNodes[0].Attributes["save"].Value);
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Could not parse job configuration data in {0}", openFileDialog1.FileName));
}
}
}
private void DisconnectJob()
{
try
{
if (vsDevice != null)
{
if (vsDevice.IsAnyInspectionRunning)
{
vsDevice.StopAll();
}
}
if (jobStep != null)
{
while (jobStep.Count > 0)
{
jobStep.Remove(1);
}
jobStep = null;
}
if (ConnectionEventCallback != null)
{
ConnectionEventCallback.Invoke(Enum_ConnectionEvent.DISCONNECTED_JOB, null);
}
}
catch
{
throw new NotImplementedException();
}
}
/// <summary>
/// loads the step details for editing
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void stepListBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (stepListBox.SelectedIndex != -1)
{
JobStep step = (JobStep)stepListBox.SelectedItem;
stepNameTextBox.Text = step.StepName;
stepFetchTextBox.Text = step.StepFetch;
stepTypeComboBox.SelectedValue = "Create and update";
if (step.UpdateOnly)
{
stepTypeComboBox.SelectedValue = "Update only";
}
if (step.CreateOnly)
{
stepTypeComboBox.SelectedValue = "Create only";
}
if (step.ManyMany)
{
stepTypeComboBox.SelectedValue = "Many to many";
}
//updateOnlyCheckBox.IsChecked = step.UpdateOnly;
}
else
{
stepNameTextBox.Text = string.Empty;
stepFetchTextBox.Text = string.Empty;
stepTypeComboBox.SelectedIndex = 0;
//updateOnlyCheckBox.IsChecked = false;
}
}
protected private JobStep GetJobStep(object input)
{
JobStep jobStep = null;
if (input is int jobStepId && _input.JobSteps.TryFind(x => x.ID.Equals(jobStepId), out JobStep jsId))
{
jobStep = jsId;
}
public void CreateJob(WAMSQLJob wamJob, string server = "localhost")
{
Server srv = null;
if (server == "localhost")
{
srv = ConnectToLocalDatabase(_username, _password);
}
else
{
srv = ConnectToRemoteDatabase(server, _username, _password);
}
if (srv != null)
{
try
{
Job job = new Job(srv.JobServer, wamJob.Name);
job.Description = wamJob.Description;
job.Create();
JobStep jobStep = new JobStep(job, wamJob.JobStepName);
jobStep.Command = wamJob.JobStepCommand;
jobStep.OnSuccessAction = StepCompletionAction.GoToStep;
jobStep.OnSuccessStep = 2;
jobStep.OnFailAction = StepCompletionAction.GoToStep;
jobStep.OnFailStep = 3;
jobStep.Create();
JobStep successStep = new JobStep(job, "ReportSuccess");
successStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Success',null)";
successStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
successStep.OnFailAction = StepCompletionAction.GoToStep;
successStep.OnFailStep = 3;
successStep.Create();
JobStep failStep = new JobStep(job, "ReportFailure");
failStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Failure','Not Available'";
failStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
failStep.OnFailAction = StepCompletionAction.QuitWithFailure;
failStep.Create();
JobSchedule jobSched = new JobSchedule(job, wamJob.ScheduleName);
jobSched.FrequencyTypes = wamJob.ScheduleFrequencyType;
jobSched.FrequencyRecurrenceFactor = wamJob.ScheduleFrequencyRecurrenceFactor;
jobSched.FrequencyInterval = wamJob.ScheduleFrequencyInterval;
jobSched.ActiveStartTimeOfDay = wamJob.ScheduleActiveStartTimeOfDay;
jobSched.Create();
} catch (Exception ex)
{
throw new Exception("Failed To Create Job: " + wamJob.Name + " - " + ex.Message);
}
}
else
{
throw new Exception("Failed To Connect to SQL Job Server");
}
}
/// <summary>
/// saves the job from a file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void saveJobButton_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "XML file|*.xml";
saveFileDialog1.Title = "Save job data";
saveFileDialog1.ShowDialog();
// If the file name is not an empty string open it for saving.
if (saveFileDialog1.FileName != "")
{
XmlDocument doc = new XmlDocument();
XmlElement elRoot = (XmlElement)doc.AppendChild(doc.CreateElement("ConfigDataJob"));
XmlElement elJobConfig = (XmlElement)elRoot.AppendChild(doc.CreateElement("JobConfig"));
elJobConfig.SetAttribute("mapBuGuid", mapBuCheckBox.Checked.ToString());
elJobConfig.SetAttribute("mapCurrencyGuid", mapCurrencyCheckBox.Checked.ToString());
XmlElement elSteps = (XmlElement)elRoot.AppendChild(doc.CreateElement("JobSteps"));
foreach (var item in stepListBox.Items)
{
JobStep step = (JobStep)item;
XmlElement elStep = doc.CreateElement("Step");
elStep.AppendChild(doc.CreateElement("Name")).InnerText = step.StepName;
elStep.SetAttribute("updateOnly", updateOnlyCheckBox.Checked.ToString());
elStep.AppendChild(doc.CreateElement("Fetch")).InnerText = step.StepFetch;
elSteps.AppendChild(elStep);
}
XmlElement elMappings = (XmlElement)elRoot.AppendChild(doc.CreateElement("GuidMappings"));
foreach (var item in guidMappingGridView.Rows)
{
DataGridViewRow dr = (DataGridViewRow)item;
if (dr.Cells["sourceGuid"].Value != null && dr.Cells["targetGuid"].Value != null)
{
string sourceId = dr.Cells["sourceGuid"].Value.ToString();
string targetId = dr.Cells["targetGuid"].Value.ToString();
XmlElement elMapping = doc.CreateElement("GuidMapping");
elMapping.SetAttribute("source", sourceId);
elMapping.SetAttribute("target", targetId);
elMappings.AppendChild(elMapping);
}
}
if (saveConnectionsCheckBox.Checked)
{
XmlElement elConnection = (XmlElement)elRoot.AppendChild(doc.CreateElement("ConnectionDetails"));
//elConnection.SetAttribute("source", sourceTextBox.Text);
//elConnection.SetAttribute("target", targetTextBox.Text);
elConnection.SetAttribute("source", _source);
elConnection.SetAttribute("target", _target);
elConnection.SetAttribute("save", "True");
}
StreamWriter sw = new StreamWriter(saveFileDialog1.FileName);
sw.Write(doc.OuterXml);
sw.Close();
}
}
public IJobTask Create(IContext db, JobStep step, ILogger logger)
{
m_logger = logger;
m_db = db;
m_taskStep = step;
this.Name = step.Name;
this.StepId = step.StepId;
return(this);
}
public void Delete()
{
JobStep jobStep = parent.Job.JobSteps[this.originalName];
if (jobStep != null)
{
jobStep.Drop();
}
}
/// <summary>
/// updates the step details
/// </summary>
private void updateStepButton_Click(object sender, RoutedEventArgs e)
{
var selectedindex = stepListBox.SelectedIndex;
if (stepListBox.SelectedIndex != -1)
{
//if fetchxml looks ok, update the step
if (isParseableXml())
{
JobStep step = (JobStep)stepListBox.SelectedItem;
step.StepName = stepNameTextBox.Text;
step.StepFetch = stepFetchTextBox.Text;
switch ((string)stepTypeComboBox.SelectedValue)
{
case "Create only":
step.UpdateOnly = false;
step.CreateOnly = true;
step.NtoNIntersectTable = false;
break;
case "Update only":
step.UpdateOnly = true;
step.CreateOnly = false;
step.NtoNIntersectTable = false;
break;
case "Intersect Table":
step.UpdateOnly = false;
step.CreateOnly = false;
step.NtoNIntersectTable = true;
break;
default:
step.UpdateOnly = false;
step.CreateOnly = false;
step.NtoNIntersectTable = false;
break;
}
step.DisablePlugins = stepDisablePlugins.IsChecked.HasValue && stepDisablePlugins.IsChecked.Value;
stepListBox.Items[stepListBox.SelectedIndex] = stepListBox.SelectedItem;
stepListBox.Items.Refresh();
stepListBox.SelectedIndex = selectedindex;
}
else
{
//otherwise alert the user and set focus back on the inputbox
MessageBox.Show("Input could not be parsed as XML.");
this.stepFetchTextBox.Focus();
}
}
else
{
//otherwise alert the user and set focus back on the inputbox
MessageBox.Show("No step selected");
}
}
/// <summary>
/// Creates a job step
/// </summary>
/// <param name="resourceGroupName">The resource group name</param>
/// <param name="serverName">The server name</param>
/// <param name="agentName">The agent name</param>
/// <param name="jobName">The job name</param>
/// <param name="stepName">The target groups name</param>
/// <param name="parameters">The target group's create parameters</param>
/// <returns>The created target group</returns>
public JobStep CreateOrUpdateJobStep(
string resourceGroupName,
string serverName,
string agentName,
string jobName,
string stepName,
JobStep parameters)
{
return(GetCurrentSqlClient().JobSteps.CreateOrUpdate(resourceGroupName, serverName, agentName, jobName, stepName, parameters));
}
private void UpdateExistingStep(JobStep existing, JobStep newValues)
{
existing.Category = newValues.Category;
existing.EndTime = newValues.EndTime;
existing.Name = newValues.Name;
existing.PercentComplete = newValues.PercentComplete;
existing.ProgressText = newValues.ProgressText;
existing.StartTime = newValues.StartTime;
existing.Status = newValues.Status;
}
public JobStepDetailsModel getStepDetails(string serverName, Guid jobID, int stepID)
{
JobStepDetailsModel step = new JobStepDetailsModel();
ConnectSqlServer connection = new ConnectSqlServer();
Server dbServer = connection.Connect(serverName);
Job job = dbServer.JobServer.GetJobByID(jobID);
JobStep jobstep = job.JobSteps[stepID - 1];
step.ServerName = serverName;
step.JobID = jobID;
step.StepNo = jobstep.ID;
step.StepName = jobstep.Name;
step.RunAs = jobstep.ProxyName;
step.Database = jobstep.DatabaseName;
step.Command = jobstep.Command;
switch (jobstep.OnSuccessAction)
{
case StepCompletionAction.GoToNextStep:
step.OnSuccess = "GoToNextStep";
break;
case StepCompletionAction.QuitWithSuccess:
step.OnSuccess = "QuitWithSuccess";
break;
case StepCompletionAction.QuitWithFailure:
step.OnSuccess = "QuitWithFailure";
break;
case StepCompletionAction.GoToStep:
step.OnSuccess = "GoToStep:" + jobstep.OnSuccessStep;
break;
}
switch (jobstep.OnFailAction)
{
case StepCompletionAction.GoToNextStep:
step.OnFailure = "GoToNextStep";
break;
case StepCompletionAction.QuitWithSuccess:
step.OnFailure = "QuitWithSuccess";
break;
case StepCompletionAction.QuitWithFailure:
step.OnFailure = "QuitWithFailure";
break;
case StepCompletionAction.GoToStep:
step.OnFailure = "GoToStep:" + jobstep.OnFailStep;
break;
}
return(step);
}
/// <summary>
/// updates the step details
/// </summary>
void UpdateStep()
{
if (stepListBox.SelectedIndex != -1)
{
JobStep step = (JobStep)stepListBox.SelectedItem;
step.StepName = stepNameTextBox.Text;
step.StepFetch = stepFetchTextBox.Text;
step.UpdateOnly = updateOnlyCheckBox.Checked;
stepListBox.Items[stepListBox.SelectedIndex] = stepListBox.SelectedItem;
}
}
static void ParseConfig(string filepath)
{
StreamReader sr = new StreamReader(filepath);
string jobdata = (sr.ReadToEnd());
sr.Close();
XmlDocument xml = new XmlDocument();
try
{
xml.LoadXml(jobdata);
_jobSteps.Clear();
_guidMappings.Clear();
XmlNodeList stepList = xml.GetElementsByTagName("Step");
foreach (XmlNode xn in stepList)
{
JobStep step = new JobStep();
step.StepName = xn.SelectSingleNode("Name").InnerText;
step.StepFetch = xn.SelectSingleNode("Fetch").InnerText;
step.UpdateOnly = Convert.ToBoolean(xn.Attributes["updateOnly"].Value);
_jobSteps.Add(step);
}
XmlNodeList configData = xml.GetElementsByTagName("JobConfig");
_mapBaseBu = Convert.ToBoolean(configData[0].Attributes["mapBuGuid"].Value);
_mapBaseCurrency = Convert.ToBoolean(configData[0].Attributes["mapCurrencyGuid"].Value);
XmlNodeList mappingList = xml.GetElementsByTagName("GuidMapping");
foreach (XmlNode xn in mappingList)
{
Guid sourceGuid = new Guid(xn.Attributes["source"].Value);
Guid targetGuid = new Guid(xn.Attributes["target"].Value);
_guidMappings.Add(new GuidMapping {
sourceId = sourceGuid, targetId = targetGuid
});
}
XmlNodeList connectionNodes = xml.GetElementsByTagName("ConnectionDetails");
if (connectionNodes.Count > 0)
{
_sourceString = connectionNodes[0].Attributes["source"].Value;
_targetString = connectionNodes[0].Attributes["target"].Value;
//Console.WriteLine(connectionNodes[0].InnerText);
}
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Could not parse job configuration data in {0} - exiting", filepath));
}
}
private JobStep GetJobStep(JobData jobData, string stepName)
{
JobStep jobStep = null;
if (jobData.Job != null)
{
const string UrnFormatStr = "Server[@Name='{0}']/JobServer[@Name='{0}']/Job[@Name='{1}']/Step[@Name='{2}']";
string serverName = this.DataContainer.Server.Name.ToUpper();
string urn = string.Format(UrnFormatStr, serverName, jobData.Job.Name, stepName);
jobStep = jobData.Job.Parent.Parent.GetSmoObject(urn) as JobStep;
}
return(jobStep);
}
public void moveStepDown(string serverName, Guid jobID, int stepID)
{
ConnectSqlServer connection = new ConnectSqlServer();
Server dbServer = connection.Connect(serverName);
Job job = dbServer.JobServer.GetJobByID(jobID);
JobStep newJobStep = job.JobSteps[stepID - 1];
StringCollection script = newJobStep.Script();
script[0] = script[0].Replace("@step_id=" + stepID, "@step_id=" + (stepID + 1));
job.JobSteps[stepID - 1].Drop();
dbServer.Refresh();
dbServer.ConnectionContext.ExecuteNonQuery(script);
}
private JobStep CloneStep(JobStep newValues)
{
JobStep existing = new JobStep();
existing.Code = newValues.Code;
existing.Category = newValues.Category;
existing.EndTime = newValues.EndTime;
existing.Name = newValues.Name;
existing.PercentComplete = newValues.PercentComplete;
existing.ProgressText = newValues.ProgressText;
existing.StartTime = newValues.StartTime;
existing.Status = newValues.Status;
return(existing);
}
/// <summary>
/// load data from and existing step
/// </summary>
/// <param name="source"></param>
private void LoadData(JobStep source)
{
this.alreadyCreated = true;
currentName = originalName = source.Name;
this.urn = source.Urn;
this.successAction = source.OnSuccessAction;
this.failureAction = source.OnFailAction;
this.originalId = this.id = source.ID;
this.subSystem = source.SubSystem;
this.failStepId = source.OnFailStep;
this.successStepId = source.OnSuccessStep;
this.cachedSource = source;
}
private void StartJobAt(object jobStep)
{
JobStep js = base.GetJobStep(jobStep);
if (js != null && (this.Force || base.ShouldProcess(string.Format(STEP_CAP, _input.Name, js.ID), START)))
{
_input.Start(js.Name);
}
else if (js == null)
{
throw new ArgumentException(jobStep + " is not a valid job step.");
}
}
private bool UpdateSQLJob(int nPeriod, bool bEnable, int nFreq, ref string err)
{
ServerConnection conn = null;
try
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLJob"].ConnectionString;
System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connstring);
conn = new ServerConnection(sqlconn);
Server server = new Server(conn);
// Get instance of SQL Agent SMO object
JobServer jobServer = server.JobServer;
Job job = jobServer.Jobs["EmailDispatch"];
if (job == null)
{
// Create Job
job = new Job(jobServer, "EmailDispatch");
job.Create();
//job.AddSharedSchedule(schedule.ID);
//job.ApplyToTargetServer(server.Name);
}
JobStep step = job.JobSteps["EmailDispatchProcess"];
if (step == null)
{
// Create JobStep
step = new JobStep(job, "EmailDispatchProcess");
step.Command = "EXEC P_EmailDispatchForSchedule";
step.SubSystem = AgentSubSystem.TransactSql;
step.DatabaseName = conn.DatabaseName;
step.Create();
}
JobSchedule schedule = job.JobSchedules["MinuteDispatch"];
if (schedule == null)
{
// Create a schedule
schedule = new JobSchedule(job, "MinuteDispatch");
schedule.ActiveStartDate = DateTime.Today;
schedule.FrequencyTypes = FrequencyTypes.Daily;
schedule.FrequencyInterval = 1;
if (nFreq == 0)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour;
else if(nFreq == 1)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
schedule.FrequencySubDayInterval = nPeriod;
//schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0);
schedule.Create();
job.AddSharedSchedule(schedule.ID);
job.ApplyToTargetServer("(local)");
}
else
{
if (nFreq == 0)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour;
else if (nFreq == 1)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
schedule.FrequencySubDayInterval = nPeriod;
schedule.Alter();
}
job.IsEnabled = bEnable;
job.Alter();
conn.Disconnect();
return true;
}
catch (Exception ex)
{
err = ex.Message;
if(conn != null)
conn.Disconnect();
return false;
}
}
/// <summary>
/// 创建步骤 1 ,杀掉UTP进程
/// </summary>
/// <param name="jb">Job 实例</param>
/// <param name="databaseName">数据库名</param>
/// <param name="strSQl">SQL命令</param>
public void CreateUTPKill(Job jb, string databaseName, string strSQl)
{
try
{
JobStep jbstp = new JobStep(jb, "KillUTP");
//数据库
jbstp.DatabaseName = databaseName;
//计划执行的SQL命令
jbstp.Command = strSQl;
//成功时执行的操作
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
//失败时执行的操作
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
//创建 SQL 代理实例的作业步骤.
jbstp.Create();
}
catch (Exception)
{
throw;
}
}
private void WriteJobSteps(List<BuildNode> OrderedToDo, bool bSkipTriggers)
{
BuildNode LastSticky = null;
bool HitNonSticky = false;
bool bHaveECNodes = false;
// sticky nodes are ones that we run on the main agent. We run then first and they must not be intermixed with parallel jobs
foreach (BuildNode NodeToDo in OrderedToDo)
{
if (!NodeToDo.IsComplete) // if something is already finished, we don't put it into EC
{
bHaveECNodes = true;
if (NodeToDo.IsSticky)
{
LastSticky = NodeToDo;
if (HitNonSticky && !bSkipTriggers)
{
throw new AutomationException("Sticky and non-sticky jobs did not sort right.");
}
}
else
{
HitNonSticky = true;
}
}
}
List<JobStep> Steps = new List<JobStep>();
using (TelemetryStopwatch PerlOutputStopwatch = new TelemetryStopwatch("PerlOutput"))
{
string ParentPath = Command.ParseParamValue("ParentPath");
bool bHasNoop = false;
if (LastSticky == null && bHaveECNodes)
{
// if we don't have any sticky nodes and we have other nodes, we run a fake noop just to release the resource
JobStep NoopStep = new JobStep(ParentPath, "Noop", "GUBP_UAT_Node", false, null, null, JobStepReleaseMode.Release);
NoopStep.ActualParameters.Add("NodeName", "Noop");
NoopStep.ActualParameters.Add("Sticky", "1");
Steps.Add(NoopStep);
bHasNoop = true;
}
Dictionary<string, List<BuildNode>> AgentGroupChains = new Dictionary<string, List<BuildNode>>();
List<BuildNode> StickyChain = new List<BuildNode>();
foreach (BuildNode NodeToDo in OrderedToDo)
{
if (!NodeToDo.IsComplete) // if something is already finished, we don't put it into EC
{
string MyAgentGroup = NodeToDo.AgentSharingGroup;
if (MyAgentGroup != "")
{
if (!AgentGroupChains.ContainsKey(MyAgentGroup))
{
AgentGroupChains.Add(MyAgentGroup, new List<BuildNode> { NodeToDo });
}
else
{
AgentGroupChains[MyAgentGroup].Add(NodeToDo);
}
}
}
if (NodeToDo.IsSticky)
{
if (!StickyChain.Contains(NodeToDo))
{
StickyChain.Add(NodeToDo);
}
}
}
foreach (BuildNode NodeToDo in OrderedToDo)
{
if (!NodeToDo.IsComplete) // if something is already finished, we don't put it into EC
{
bool Sticky = NodeToDo.IsSticky;
if (NodeToDo.IsSticky)
{
if (NodeToDo.AgentSharingGroup != "")
{
throw new AutomationException("Node {0} is both agent sharing and sitcky.", NodeToDo.Name);
}
if (NodeToDo.AgentPlatform != UnrealTargetPlatform.Win64)
{
throw new AutomationException("Node {0} is sticky, but {1} hosted. Sticky nodes must be PC hosted.", NodeToDo.Name, NodeToDo.AgentPlatform);
}
if (NodeToDo.AgentRequirements != "")
{
throw new AutomationException("Node {0} is sticky but has agent requirements.", NodeToDo.Name);
}
}
string ProcedureInfix = "";
if (NodeToDo.AgentPlatform != UnrealTargetPlatform.Unknown && NodeToDo.AgentPlatform != UnrealTargetPlatform.Win64)
{
ProcedureInfix = "_" + NodeToDo.AgentPlatform.ToString();
}
bool DoParallel = !Sticky || NodeToDo.IsParallelAgentShareEditor;
List<BuildNode> UncompletedEcDeps = new List<BuildNode>();
foreach (BuildNode Dep in FindDirectOrderDependencies(NodeToDo))
{
if (!Dep.IsComplete && OrderedToDo.Contains(Dep)) // if something is already finished, we don't put it into EC
{
if (OrderedToDo.IndexOf(Dep) > OrderedToDo.IndexOf(NodeToDo))
{
throw new AutomationException("Topological sort error, node {0} has a dependency of {1} which sorted after it.", NodeToDo.Name, Dep.Name);
}
UncompletedEcDeps.Add(Dep);
}
}
string PreCondition = GetPreConditionForNode(OrderedToDo, ParentPath, bHasNoop, AgentGroupChains, StickyChain, NodeToDo, UncompletedEcDeps);
string RunCondition = GetRunConditionForNode(UncompletedEcDeps, ParentPath);
// Create the job steps for this node
TriggerNode TriggerNodeToDo = NodeToDo as TriggerNode;
if (TriggerNodeToDo == null)
{
// Create the jobs to setup the agent sharing group if necessary
string NodeParentPath = ParentPath;
if (NodeToDo.AgentSharingGroup != "")
{
NodeParentPath = String.Format("{0}/jobSteps[{1}]", NodeParentPath, NodeToDo.AgentSharingGroup);
List<BuildNode> MyChain = AgentGroupChains[NodeToDo.AgentSharingGroup];
if (MyChain.IndexOf(NodeToDo) <= 0)
{
// Create the parent job step for this group
JobStep ParentStep = new JobStep(ParentPath, NodeToDo.AgentSharingGroup, null, true, PreCondition, null, JobStepReleaseMode.Keep);
Steps.Add(ParentStep);
// Get the resource pool
JobStep GetPoolStep = new JobStep(NodeParentPath, String.Format("{0}_GetPool", NodeToDo.AgentSharingGroup), String.Format("GUBP{0}_AgentShare_GetPool", ProcedureInfix), true, PreCondition, null, JobStepReleaseMode.Keep);
GetPoolStep.ActualParameters.Add("AgentSharingGroup", NodeToDo.AgentSharingGroup);
GetPoolStep.ActualParameters.Add("NodeName", NodeToDo.Name);
Steps.Add(GetPoolStep);
// Get the agent for this sharing group
JobStep GetAgentStep = new JobStep(NodeParentPath, String.Format("{0}_GetAgent", NodeToDo.AgentSharingGroup), String.Format("GUBP{0}_AgentShare_GetAgent", ProcedureInfix), true, GetPoolStep.GetCompletedCondition(), null, JobStepReleaseMode.Keep);
GetAgentStep.Exclusive = JobStepExclusiveMode.Call;
GetAgentStep.ResourceName = String.Format("$[/myJob/jobSteps[{0}]/ResourcePool]", NodeToDo.AgentSharingGroup);
GetAgentStep.ActualParameters.Add("AgentSharingGroup", NodeToDo.AgentSharingGroup);
GetAgentStep.ActualParameters.Add("NodeName", NodeToDo.Name);
Steps.Add(GetAgentStep);
// Set the precondition from this point onwards to be whether the group was set up, since it can't succeed unless the original precondition succeeded
PreCondition = GetAgentStep.GetCompletedCondition();
}
}
// Get the procedure name
string Procedure;
if (NodeToDo.IsParallelAgentShareEditor)
{
if (NodeToDo.AgentSharingGroup == "")
{
Procedure = "GUBP_UAT_Node_Parallel_AgentShare_Editor";
}
else
{
Procedure = "GUBP_UAT_Node_Parallel_AgentShare3_Editor";
}
}
else
{
if (NodeToDo.IsSticky)
{
Procedure = "GUBP" + ProcedureInfix + "_UAT_Node";
}
else if (NodeToDo.AgentSharingGroup == "")
{
Procedure = String.Format("GUBP{0}_UAT_Node_Parallel", ProcedureInfix);
}
else
{
Procedure = String.Format("GUBP{0}_UAT_Node_Parallel_AgentShare3", ProcedureInfix);
}
}
if (NodeToDo.IsSticky && NodeToDo == LastSticky)
{
Procedure += "_Release";
}
// Build the job step for this node
JobStep MainStep = new JobStep(NodeParentPath, NodeToDo.Name, Procedure, DoParallel, PreCondition, RunCondition, (NodeToDo.IsSticky && NodeToDo == LastSticky) ? JobStepReleaseMode.Release : JobStepReleaseMode.Keep);
MainStep.ActualParameters.Add("NodeName", NodeToDo.Name);
MainStep.ActualParameters.Add("Sticky", NodeToDo.IsSticky ? "1" : "0");
if (NodeToDo.AgentSharingGroup != "")
{
MainStep.ActualParameters.Add("AgentSharingGroup", NodeToDo.AgentSharingGroup);
}
Steps.Add(MainStep);
}
else
{
// Get the procedure name
string Procedure;
if (TriggerNodeToDo.IsTriggered)
{
Procedure = String.Format("GUBP{0}_UAT_Node{1}", ProcedureInfix, (NodeToDo == LastSticky) ? "_Release" : "");
}
else if (TriggerNodeToDo.RequiresRecursiveWorkflow)
{
Procedure = "GUBP_UAT_Trigger"; //here we run a recursive workflow to wait for the trigger
}
else
{
Procedure = "GUBP_Hardcoded_Trigger"; //here we advance the state in the hardcoded workflow so folks can approve
}
// Create the job step
JobStep TriggerStep = new JobStep(ParentPath, NodeToDo.Name, Procedure, DoParallel, PreCondition, RunCondition, (NodeToDo.IsSticky && NodeToDo == LastSticky) ? JobStepReleaseMode.Release : JobStepReleaseMode.Keep);
TriggerStep.ActualParameters.Add("NodeName", NodeToDo.Name);
TriggerStep.ActualParameters.Add("Sticky", NodeToDo.IsSticky ? "1" : "0");
if (!TriggerNodeToDo.IsTriggered)
{
TriggerStep.ActualParameters.Add("TriggerState", TriggerNodeToDo.StateName);
TriggerStep.ActualParameters.Add("ActionText", TriggerNodeToDo.ActionText);
TriggerStep.ActualParameters.Add("DescText", TriggerNodeToDo.DescriptionText);
if (NodeToDo.RecipientsForFailureEmails.Length > 0)
{
TriggerStep.ActualParameters.Add("EmailsForTrigger", String.Join(" ", NodeToDo.RecipientsForFailureEmails));
}
}
Steps.Add(TriggerStep);
}
}
}
WriteECPerl(Steps);
}
}
