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
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
and switch it to
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.