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