October 20, 2009 at 3:10 pm
I have written a bunch of SSIS packages (8) to run as stand alones, but only because I'm a newbie. They share lots of variables, connections, parameters etc but at this point I would just a soon leave them as is. The run order is critical though and they can't run concurrently. What is the best way to schedule them? Should I make them into a single package that calls the others? I have not attempted to do this yet.
From reading related posts, I have surmised that the general consensus is to use SQL agent rather than windows scheduler for the scheduling. Any dissenters? My problem with this is that the box available to do the scheduling is running SQL Server 2000. I know it is possible to have 2000 and 2005 installed side by side, but it's a production box and I'm a little nervous. Can anybody offer assurance that if I install SS 2005 on the box running 2000 that I won't break anything?
Thanks in advance.
October 21, 2009 at 4:46 am
I have followed a different approach for the same requirement, but that was for loading delta to data warehouse.
I suggest that create a Master Package, and then execute all the packages one by one.
1) You can also create a small table to priority and name of the package(or connection string i.e. path of the package).
2) Read the same inside your master package and execute the packages using priority from the table.
Creating a master package is better from a scalability and integration perspective. There might be common variables that this master package can read and pass to all child packages which can read from parent package. This would help you to centralize the change.
October 21, 2009 at 8:21 am
thanks so much. Would you also recommend scheduling the master package using SQL Agent?
October 21, 2009 at 8:29 am
I would recommend scheduling one top-level package and not all the packages independently. By this way, you can also track and audit the execution of different packages (for ex. by making an entry into some audit table), which can be used easily for reporting (if required).
October 21, 2009 at 7:08 pm
This is for improvising your thoughts on implementing.
try enabling checkpoints so that you would also have a way to restart the Package executions
from the point of failure instead of restarting from the beginning.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply