SSIS SQL Server agent launch job already running

598 views Asked by At

Is there any way to have a package (which will be a wrapper) run every minute in SQL Server agent, even if it is already running from a previous execution. It seems the SQL Server agent does not launch if already running. Is there a way to override this behaviour?

I wanted to do something such as

Wrapper.dtsx
   --> read from table of packages to run, and select next in line
   --> execute package task with the package dynamically set from previously 
       selection
   --> exit

ie

table has the following packages (assume some ranking will exist eventually)

   a.dtsx (say runs for 5 mins)
   b.dtsx (say runs for 4 mins)
   c.dtsc (say runs for 6 mins)

   12:01 am a.dtsx is executed
   12:02 am b.dtsx is executed
   12:03 am c.dtsx is executed

at the moment I can only get the following to occur

   12:01 am a.dtsx is executed
   12:06 am b.dtsx is executed
   12:10 am c.dtsx is executed
1

There are 1 answers

0
Ferdipux On

Hm, this SQL Jobs behavior is standard for MS SQL Server and cannot be altered. For your situation if you are on SQL 2012 and higher, you can use new SSIS Catalog with async execution. By using this your job will start package execution and quit; therefore you are free to start it in a minute. Disadvantage - job status will only show whether package been started and nothing on its outcome; you have to do execution monitoring yourself.
Switching to async package execution requires SSIS 2012+, establishing SSIS Catalog DB, switching your packages to Project deploy model. After all, create SQL Job to start package, specify all needed connections and parameters, save it. Then with context menu select Script Job as -> DROP and CREATE to -> New Query Editor Window. In the query text - locate substring

/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True  

and switch it to

/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";False  

Then run script updating your job.
This strange script manipulation is needed since by default SQL Job executes package synchronously and does not expose async option in user interface.