Hi Shannon,
I didn't have the time to build you a complete system but this is an example of what I'm talking about. Details, some programmer's notes, and a run-able example are in the code that follows. Each job that you start will run asynchronously. You can start as many jobs as you'd like. Just remember that SQL Server does have some limits. If you start 75 import jobs all at once, you're might just peg I/O to the ceiling and CPU to the wall.
The "command" you pass to this proc could, in fact, be a stored procedure name and parameters that would do your imports for you using just a single proc and a cofiguration table (or direct commands... which ever you prefer).
DROP PROCEDURE dbo.CreateExecSelfDeletingTsqlJob
GO
CREATE PROCEDURE dbo.CreateExecSelfDeletingTsqlJob
/**********************************************************************************************************************
Purpose:
Creates a new job based on the input parameters and executes it.
This example stored procedure has no error checking but will identify if the job started. You can add in the error
checking that you think is necessary.
Programmer's Notes:
1. The job that is created (along with its history) is dropped upon completion whether successful or not.
This proc could be modified to drop the job and retain the history using sp_delete_job instead of having
it automatically drop. That way, you could test to see if the job completed successfully or not.
Usage Example:
DECLARE @IsJobStarted INT,
@JobID UNIQUEIDENTIFIER,
@Command NVARCHAR(MAX)
;
SELECT @Command = N'
IF OBJECT_ID(''tempdb.dbo.MyObjects'',''U'') IS NOT NULL
DROP TABLE tempdb.dbo.MyObjects;
SELECT * --for example only
INTO TempDB.dbo.MyObjects
FROM sys.Objects
;'
EXEC @IsJobStarted = dbo.CreateExecSelfDeletingTsqlJob
@pJobName = N'TestJob',
@pDatabase = N'Master',
@pCommand = @Command,
@pJobID = @JobID OUTPUT
;
--===== Show that the job exists
SELECT * FROM msdb.dbo.sysjobactivity WHERE job_id = @JobID;
PRINT @IsJobStarted;
PRINT @JobID;
--===== Show that the job completed and deleted itself
WAITFOR DELAY '00:00:01'
SELECT * FROM msdb.dbo.sysjobactivity WHERE job_id = @JobID
Revision History:
Rev 00 - 08 Dec 2012 - Jeff Moden
**********************************************************************************************************************/
--===== Define the IO for this procedure
@pJobName SYSNAME,
@pDatabase SYSNAME,
@pCommand NVARCHAR(MAX),
@pJobID UNIQUEIDENTIFIER = NULL OUTPUT,
@pServerInstance SYSNAME = @@SERVERNAME
AS
--===== This creates a self deleting job (see comment in code)
EXEC msdb.dbo.sp_add_job
@job_name = @pJobName,
@enabled = 1,
@notify_level_eventlog = 0,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 3, --Makes a self deleting job at job completion
@category_name = N'[Uncategorized (Local)]',
@owner_login_name = N'sa',
@job_id = @pJobID OUTPUT --You'll need this as part of the return
;
--===== This identifies which server instance to run the job on
EXEC msdb.dbo.sp_add_jobserver
@job_name = @pJobName,
@server_name = @pServerInstance
;
--===== This adds the job step. Note that the "command" could be retrieved
-- from a cofiguration table here and could include parameters. So could
-- the database name and the name of the job step (although 'Step 1' works).
EXEC msdb.dbo.sp_add_jobstep
@job_name = @pJobName,
@step_name = N'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_fail_action = 2,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = @pCommand,
@database_name = @pDatabase,
@database_user_name = N'dbo',
@flags = 0
;
--===== This just adds some stuff to the job like which step to start on.
-- It duplicates a lot of the stuff from above (like the auto-delete)
-- so you need to be sure to use the same settings here.
EXEC msdb.dbo.sp_update_job
@job_name = @pJobName,
@enabled = 1,
@start_step_id = 1,
@notify_level_eventlog = 0,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 3, --Makes a self deleting job at job completion
@description = N'',
@category_name = N'[Uncategorized (Local)]',
@owner_login_name = N'sa',
@notify_email_operator_name = N'',
@notify_netsend_operator_name = N'',
@notify_page_operator_name = N''
;
--===== Start the job
DECLARE @pIsJobStarted INT
;
EXEC @pIsJobStarted = msdb.dbo.sp_start_job
@job_name = @pJobName,
@server_name = @pServerInstance,
@step_name = 'Step 1'
;
--===== Return with status of if job is running
RETURN ABS(@pIsJobStarted-1)
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.