0

Curious if this is feasible. I am currently in the process of building a number of SQL Server agent jobs. These jobs are just SFTP jobs that pass files between the servers of 2 different clients, making a pit-stop at my local server for some pre and post processing. Yes, this setup is from one standpoint unnecessarily complicated, but it is necessary from a security standpoint. All of these jobs have identical structure:

  1. SFTP a file from the client1server to the local server.
  2. Run an executable on the file
  3. SFTP the processed file to client2server.
  4. Wait a predetermined amount of time so that client2 can perform their query on the input.
  5. SFTP the response file from client2server to the local server.
  6. Run a second executable on the file.
  7. SFTP the processed response file back to client1server.

Pretty straight forward.

There are only a handful of values that change between each job: - Input/output file path on client1server - Input/output file path on client2server - Directory on local server

These jobs are not complicated, so If necessary I can just create them all by hand. It seems like an unnecessary amount of work though. I had the thought that maybe I could create a stored procedure that would generate the SQL script that creates the job, and that stored procedure could simple accept the variables that change from job to job. Is this feasible?

Broadly, heres what I'm thinking:

CREATE PROCEDURE create_ftp_interface_job
    @client1input_fp nvarchar(100),
    @client1output_fp nvarchar(100),
    @client2input_fp nvarchar(100),
    @client2output_fp nvarchar(100),
    etc...

AS


<SQL Script for creating SQL Server Agent jobs, with parameters inserted>


GO

I've tried an early version of this, and I seem to be having trouble referencing the variables I declare in the stored procedure definition inside of the SSA job script. I came here to ask if what I'm attempting is feasible, and I just have a run of the mill reference error, or if what I'm doing is not allowed.

Enlico
  • 23,259
  • 6
  • 48
  • 102
Canadian_Marine
  • 479
  • 1
  • 4
  • 10
  • 1
    But there are already sp's that do this. They're all built into `msdb`. – Thom A Feb 19 '20 at 20:34
  • 1
    You'll need to post the script you tried and be detailed about the issue. Note that if you script out an existing SQL Agent job you'll see awhole bunch of parameter malarkey, and it works correctly. – Nick.Mc Feb 19 '20 at 22:49

1 Answers1

0

You can use msdb.dbo.sp_add_job, msdb.dbo.sp_add_jobstep, msdb.dbo.sp_update_job, msdb.dbo.sp_add_jobschedule and msdb.dbo.sp_add_jobserver to create and manage SQL Agent Jobs programatically.

In fact there are even more stored procedures here which relate to managing SQL Agent Jobs.

3N1GM4
  • 3,372
  • 3
  • 19
  • 40