If you are using SQL Agent together with SSIS and if you have a batch job solution with hundreds of interdependent SQL Agent steps spread across dozens of servers, you may have run in to some maintenance problems.
For complex batch solutions, a Business Process Automation software can be of great help. In this blog post I’ll give a brief introduction to Automate BPA Server from Network Automation.
Some of the problems you may encounter when running complex batch solutions with SQL Agent are:
* You have no overview of the overall batch processing steps.
* One job failure causes the whole batch process to stop.
* You only get a notification after something have failed.
* You don’t know where to put a new job step because of complex interdependencies.
* You don’t know how a job step or a sequence of job steps is started. They may be started from another job, but which one?
* When will the job / job step finish? Are we late? How long does a job step usually take to run?
* Variables and parameters needs to be set in more than one location.
* Missing execution history.
* No event driven execution, only calendar driven.
The solution to many of the above problems is to use a centralized batch orchestrating server that manages a batch processing workflow.
Automate BPA Server
Automate BPA Server from Network Automation is, somewhat simplified, made from two parts.
The first part is Automate, which is a product on its own. With Automate you can automate business processes with the help of more than 400 predefined actions:
The actions are added to the task by drag and drop, and each action has a dialog where settings are specified. This way, rather complex solutions can be built without any coding. There is for instance a stored procedure action to use when you want to run a stored procedure or a SQL Agent job, and there is a “cmd” action to use when you want to run a SSIS package.
Dynamic content is managed with the scripting language built into Automate, which has a lot of built-in functions and of course access to all local and global variables.
The other part of Automate BPA Server is the server part. The BPA Server is used to organize the above tasks in a workflow. Workflows works pretty much as expected. You can add different paths in the workflow depending on success, errors or conditions, you can execute parallel workflows which are later merged in a “wait-for-all-to-complete” component, and so on. There are also a number of triggers, or conditions, that are used to specify when a workflow will start or continue. Obviously there is an ordinary scheduler that executes the workflow on a time based schedule, but there are also more advanced variants such as a “file trigger” that executes a workflow when a file appears in a specified folder.
On each step in the workflow you’ll also set the client to execute on. Automate BPA Server uses installed clients on other machines to execute remote commands and other tasks.
You can add other workflows to an existing workflow, which is a great feature to hide away complex workflows by using master workflows. The picture below show how an example of how a workflow can look like:
To manage running workflows, there is the Dashboard which gives you the current status on executing workflows:
There is also an “outlook” style calendar which shows executed and scheduled tasks, and a Report option that allows you to view reports showing statistics about workflows, tasks and more.
A repository stores all tasks you create, which can be used for code reuse and templates.
Global and local variables are supported, so you can define a set of global variables in a master workflow which are then inherited by the sub- workflows and tasks.
There are many more features in Automate BPA Server which won’t be covered in this brief introduction. There is however a time limited demo version available at Network Automation so you can try it out for yourself.
