June 16, 2015 at 4:18 pm
Hi
I regularly have to run an SSIS package in SQL Server Agent 14 times, one for every year from 2002-2015 inclusive. This means I have periods of wasted time and the jobs take over a day to run.
Before running each job I need to change a value in a config table for the specific year e.g.
UPDATE SSISconfig
SET runYear='2002' etc, etc
I'd like to have each year running in sequence in order to avoid having to wait for a job to complete before running the next year. Each job runtime varies.
I've tried using EXEC dbo.sp_start_job in a script but this only works one job at a time.
Is there a way of scripting or using the steps in Agent to get the jobs to run sequentially?
Alternatively would it be possible to run all 14 jobs at the same time and pass the values for each year separately?
Thanks
June 26, 2015 at 1:32 pm
lenrigby (6/16/2015)
HiI regularly have to run an SSIS package in SQL Server Agent 14 times, one for every year from 2002-2015 inclusive. This means I have periods of wasted time and the jobs take over a day to run.
Before running each job I need to change a value in a config table for the specific year e.g.
UPDATE SSISconfig
SET runYear='2002' etc, etc
I'd like to have each year running in sequence in order to avoid having to wait for a job to complete before running the next year. Each job runtime varies.
I've tried using EXEC dbo.sp_start_job in a script but this only works one job at a time.
Is there a way of scripting or using the steps in Agent to get the jobs to run sequentially?
Alternatively would it be possible to run all 14 jobs at the same time and pass the values for each year separately?
Thanks
Create 1 job with 24 Steps and in each 2 steps execute the update statement and run SSIS package and Name the steps by year like "Update2002" and "RunSSIS2002".
July 14, 2015 at 9:47 am
Yes, this works - update config tables in one step then run package in next step and repeat with "go to the next step" as On Success Action until last step which is "Quit the job reporting success".
Many thanks for your help with this.
September 11, 2015 at 1:59 pm
An alternative to a 24 step package would be to have a ForEach iterator walk each of the yearly values, passing the value into the dataflow to work on the appropriate values.
You can still use a config table to store all of your years (and other parameters needed), and feed that to your ForEach iterator.
That would be cleaner than having repeated steps that you'd have to maintain.
-=Janrith
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply