Creating SQL Agent job is failing with no errors

Question

I am trying to create a SQL Agent job with a command line step using C#. The web API is calling a class which contains the method, when stepping through the code no errors are thrown and all variables have values but the job is not created when looking at my SQL instance (I'm running a local instance of SQL Server 2016). I have 2 methods in the same class that I call; CreateJob() and AddCommandLineStep(). The source code looks like this at the moment:

The class methods

public SQLAgentJobHelper CreateJob(string JobName)
    {
        // Create Job 
        foreach (Job job in jobServer.Jobs)
        {
            if (job.Name.Equals(JobName))
            {
                job.Drop();
                break;
            }
        }
        server.ConnectionContext.BeginTransaction();
        job = new Job(jobServer, JobName);

        job.Create();
        job.ApplyToTargetServer(server.Name);

        server.ConnectionContext.CommitTransaction();           

        return this;
    }

public void AddCommandLineStep(string StepName, string Executable, string CommandLine, List<NameValue> paramList, bool isLast = false)
    {
        try
        {
            if (job == null)
                throw new Exception("First call CreateJob before adding steps");

            var jobStep = new JobStep(job, StepName);

            var paramString = CommandLine;
                paramList.ForEach(param => paramString += $"/{param.Name} {param.Value} ");
                jobStep.Command = $"\"{Executable}\" {paramString}";

            jobStep.SubSystem = AgentSubSystem.CmdExec;
            if (isLast)
                jobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
            else
                jobStep.OnSuccessAction = StepCompletionAction.GoToNextStep;

            jobStep.OnFailAction = StepCompletionAction.QuitWithFailure;

            jobStep.Create();

            lastJobStep = jobStep;
        }
        catch (Exception ex)
        {

        }
    }

The API calls

jobHelper.CreateJob(jobName)
jobHelper.AddCommandLineStep("Step 1", @"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTEXEC.exe", @"C:\SSIS\CopyDataView.dtsx", Enumerable.Empty<NameValue>().ToList());

I know about the empty catch and the paths will be replaced by variables, this is just a rough attempt at getting the code to function as required.

[UPDATE]

I can definitely say that the methods work, when doing unit testing I am able to create a job on the same SQL instance. After taking a more detailed look at this issue, I saw that there is approx. 30 projects in the solution. Is it possible that there is some dll referenced in another project that is causing the job creation to fail?

score 0 · Accepted Answer · answered May 14 '18 at 07:44

The API is now working as expected! In this case it was necessary to explicitly complete the API transaction. Using the TransactionScope namespace and the .Complete() method thereof, the code now resembles this:

   using (var txscope = new TransactionScope(TransactionScopeOption.RequiresNew))
   {
            try
            {
                using (SQLAgentJobHelper jobHelper = new SQLAgentJobHelper(ServerConnection))
                { 
                   jobHelper.CreateJob(jobName)
                   jobHelper.AddCommandLineStep("Step 1", @"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTEXEC.exe", C:\SSIS\CopyDataView.dtsx", Enumerable.Empty<NameValue>().ToList());
                }

              txscope.Complete();
              return Ok(1);
            }
            catch (Exception ex)
            {
                txscope.Dispose();
                return Ok(ex.StackTrace.ToString());
            }
    }

Creating SQL Agent job is failing with no errors

1 Answers1