2

In the investment firm I work for, we have several daily data integration tasks that every night/early morning.

Most if not all are done using SSIS, and they are all scheduled to start at certain times. They work (as in the SSIS packages do their job).

However, we have serious problems when it comes to tackling with dependent processes. For example, if SSIS package is scheduled to run at 3AM does not get the FTP file from a vendor, then dependent processes that are set to run at 3:30AM and 4AM all fail.

Currently we have about 8 different inhouse applications/endpoints, with data coming from around 6 external vendors and the vendor list is beginning to grow and we expect it would around 20 or so in a year.

We don't want to go BizTalk route for financial/complexity/implementation difficulties.

I wanted to create a event-driven approach to this, where the SSIS/nightly processes are oblivious to each other, as they are, yet need subscribe to dependent parent processes to start/stop.

At a glance I feel NServiceBus would give us that flexibility. It lets us keep what we already have and work, but yet give an event driven mechanism.

I need some input here.

zorrinn
  • 45
  • 1
  • 6
  • I'm not too familiar with SSIS, but there are certainly options in SSIS as well. One option is to schedule SQL Job Agent tasks and have each package as a step, which then only executes when the other step is successful. Within SSIS you can also call other packages, so you might create one 'master' package that calls all other packages, one after the other. Would that not be a far simpler solution? – Dennis van der Stelt Apr 06 '17 at 07:28
  • Yes, there are such options in SSIS and we are using those. However, it does not offer flexibility. For example, I cannot just do a step by step execution. In some instances I have to run parallel. Also, your suggestion works when we have a few, but once we start growing it can get complex. And one master package control all other packages is prone to be risky. I'm not sold on this idea. Sorry. – zorrinn Apr 06 '17 at 07:51
  • Email me at dennis.vanderstelt@particular.net and we can come up with a solution. Stackoverflow isn't really a good medium for discussions like this. You can always post the answer here if you want to. – Dennis van der Stelt Apr 06 '17 at 12:08
  • I feel that you are more interested in a generic workflow tool that would allow you to create dependencies between tasks. Some open source scheduler would allow you to graphically create dependencies and handle errors to automatically retry when required. Have you looked at this type of solution? – XYZ123 Apr 14 '17 at 21:36

0 Answers0