April 17, 2017 at 10:59 am
Once upon a time, 10+ years ago, someone from my organization decided they were going to use SSIS to extract some data from a source system, and it worked!
Time passed...
Today there are 90+ SSIS packages, each basically representing a given table of the source system. It continues to work, but dependencies have been introduced and are 'managed' via the SQL Agent step order. I am curious if most people solve the issue of dependencies by using a master package for execution and control of their packages, or are other options?
Regards
April 17, 2017 at 11:30 am
I don't have quite that many SSIS packages, but I do follow a similar methodology, using SQL Server Agent to coordinate multiple related or dependent packages into one "job". It has the benefits of stopping when one of them fails, being able to easily send an e-mail notice when it fails, and also breaking it down into steps allows someone to easily rerun the steps from the point of failure once the problem has been resolved. There isn't an easy way to run a SSIS package starting from the middle unless you are manually running individual tasks through BIDS or something, but that could be more error prone than running the SQL Agent job from a specific step in the middle.
April 17, 2017 at 1:26 pm
I think that the answer to this depends a lot on the process. If the process is mostly asynchronous with some (synchronous) dependencies, then it may make sense to use a master package, but if your process is mostly synchronous it probably makes more sense to use SQL jobs. And it's also possible to use a hybrid.
Other factors to consider are that it's usually much easier to modify an existing approach that to completely rewrite it from scratch and that the approach you take may depend on the expertise of the people that you currently have available.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 19, 2017 at 6:40 am
Chris Harshman - Monday, April 17, 2017 11:30 AMI don't have quite that many SSIS packages, but I do follow a similar methodology, using SQL Server Agent to coordinate multiple related or dependent packages into one "job". It has the benefits of stopping when one of them fails, being able to easily send an e-mail notice when it fails, and also breaking it down into steps allows someone to easily rerun the steps from the point of failure once the problem has been resolved. There isn't an easy way to run a SSIS package starting from the middle unless you are manually running individual tasks through BIDS or something, but that could be more error prone than running the SQL Agent job from a specific step in the middle.
Thanks Chris, my struggle with the Agent methodology is that it prevents parallel processing and handling dependencies. Any notifications we have come from within the packages not just from the Agent, so that is not a concern. We also have restarts handled via a control table that acts as a check point monitor so that we can run from the Agent Step and pick up where we left off without any manual intervention. Thanks for the reply
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply