Sql-server – Nested Stored Procedure Connections

connection-poolingconnectionssql serverstored-procedurestransaction

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

Best Answer

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

Related Question