I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging table if there is a newer file.

At this point, if it does import a newly added file, there is another accompanying SP that ETL's the records from staging into another production DB. The ETL (EXEC dbo.ETL) is happening inside the monitoring SP that runs every 5 minutes.

My main question is, can I have the ETL stored Procedure run independently of the monitoring SP? If an ETL SP takes longer than 5 mins, when the monitoring SP is called again, will it use another connection if the last call 5 mins ago is still running the ETL procedure?

IOW, I want to run a SP (SP #1) every 5 mins that calls other SPs (that may take more or less than 5 mins) without interrupting SP #1

  • You WILL need a way to test this situation regardless of how you want things to work. Guessing is not an effective path. But you have a single staging table, correct? What will happen when your import process starts while your ETL process is still using the staging table? Think carefully about this approach.
    – SMor
    Jan 31, 2021 at 17:33

2 Answers 2


A solution could be to build a simple SSIS package that runs the stored procedure you need; then call it from tsql in this way:

declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'folder'
 ,@project_name = 'project_name'
 ,@package_name = 'run_sp.dtsx'
 ,@execution_id = @execution_id output

 exec ssisdb.catalog.start_execution @execution_id

This call is asyncronous by default


Are you asking of you can call some other stored procedure asynchronously? No, not natively, so to speak. We usually have two options for this:

Execute an Agent job, using sp_start_job. Note that an agent job doesn't have parameters so if you want to pass data into this job, so to speak, you can use a table as a communication mechanism. Note that if the ETL is running (through a started Agent job) and your monitoring proc tries to start it again, then it won't be started. Agent will not start a job if it's already running.

Use Service Broker. This is queuing in SQL Server, so your "outer" proc can send a message to a queue, and you can have either a polling mechanism to poll the queue or activation (Service Broker kicks off something when a message is received).

Or did you mean something different? If so, please clarify. Your last sentence is a bit confusing since you essentially say that you want to run sp #1 without interrupting sp #1.

  • I appreciate the feedback. Sorry about my last sentence being confusing. Since a SQL Agent Job cannot be ran if its already running, I will look into other solutions. Thank you for your response
    – DBA_Kyle
    Jan 31, 2021 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.