Variables

Strongly Typed Variables

It would be great if variables could be strongly typed to ensure that mistakes are not made, and to allow for some additional functionality (such as formatting).  These strong types could be basic types, such as string, char, date, integer, double etc., but could also be more complex types, such as “path” (which is very common in running jobs),  and which could include their own set of behaviors.

Support Variable Formatting

We use a large number of date variables in the processing of our jobs – they are often used as placeholders in file names.  However, it seems that each of our suppliers had chosen a different date format.  As a result, we have a number of date variables that represent the same date, but using different formats, e.g. “DATE_Today_YYMMDD” and “DATE_Today_YY-MM-DD”.  It would be great to only have one “DATE_Today” variable, which would be strongly typed, and which would support formatting on the fly.  The system would update only one DATE_Today variable, and the jobs would specify the format in which they want that variable, e.g. “%DATE_Today.YYMMDD%”.  This would cut about 30 percent of our variables out.  The format could be seen as an extension of the variable.  This would apply not only to date variables, but also to integers, doubles etc.

Auto-Increment Variables

We need a job step that can automatically increment variable values.  This would eliminate the need for custom scripts to do this work.  Some core functionality:

·         Should be able to add or subtract value from the same variable or another variable. (e.g. JOB_LastImportSessionID = JOB_LastImportSessionID +1)

·         Should be able to assign the value of one variable to another (e.g. DATE_Yesterday = DATE_Today)

·         Should be able to use the system clock as a reference point.  (e.g. DATE_Today = “Now.ToDate”)

·         Should be able to execute as a stand-along job, or as a job response.

·         The step should be able to update execution instances of the variables (the value instance which is attached to a running job - which is useful for setting DATE_Now), job definition instance of the variables, and job group instances of the variables.

Variable Condition

It should be possible to define a job condition that depends on a particular value for a variable.  This would allow one job to set a mutex when it starts running, and have the other jobs use it as a condition that prevents them from starting. 

Variable Inheritance

In adTempus today, you can override variables at any level in the hierarchy, and even use those new values in deriving values for variables that are further up in the hierarchy.  The only thing you cannot do is reference the original value of the variable from the hierarchy level above.

As an example, we have a PATH_Logs variable that is defined at the top of the adTempus job hierarchy.  We’d like our log hierarchy to follow the hierarchy of our jobs, so a job that’s under “root\production\cosmos\” would have a PATH_Logs variable pointed to \\files\groups\techops\logs\production\cosmos.  In defining the variable value at each level, we’d like to override the definition for “PATH_Logs” at each level, but still reference the original value:

·         At the root level PATH_Logs is defined as “%PATH_TechOps%logs\”.

·         At the production job groups level, we’d like to define it as “%PATH_Logs%Production\”

·         At the cosmos job group level, we’d like to define it as “%PATH_Logs%cosmos\”

At each level in the above example, we are able to use the value from the prior level to define the new value for this level.  Currently we have to override the value at each level to make this work.

Facilitated Data Entry

Entering the variable values on the command lines and other text boxes is a bit of a challenge.  You have to type them exactly as they are spelled, and not forget to enclose in %.  Since we use variables quite a lot in our environment, it’s quite challenging to get the command line correct with all the spellings of the variables.  Some of our more extreme cases are some SSIS packages that take parameters that drive their behavior:

%EXE_DTExec32% /FILE %JG_FILE_HoldingSSISPackage% /SET "\Package.Connections[TotalReturnConnection].Properties[ServerName]";"%SRV_TotalReturn%" /SET "\Package.Connections[TotalReturnConnection].Properties";"%USR_TotalReturn%" /SET "\Package.Connections[TotalReturnConnection].Properties[Password]";"%PSW_TotalReturn%" /SET "\Package\Foreach Loop Container\Set TR Date Variable.Properties[ServerName]";"%SRV_TotalReturn%" /SET "\Package\Foreach Loop Container\Set TR Date Variable.Properties[UserName]";"%USR_TotalReturn%" /SET "\Package\Foreach Loop Container\Set TR Date Variable.Properties[Password]";"%PSW_TotalReturn%" /SET "\Package\Foreach Loop Container\Set TR Date Variable.Properties[LocationName]";"%DB_TotalReturn%" /SET "\Package.Connections[BaupostDataWarehouseConnection].Properties[InitialCatalog]";"BaupostDataWarehouse" /SET "\Package.Connections[BaupostDataWarehouseConnection].Properties[ServerName]";"%ENV_DataWarehouseServer%" /SET "\package.variables[piRefreshMode]";"1" /SET "\Package.Variables[User::pdStartDate].Properties[Value];%AS_OF_DATE%" /SET "\Package.Variables[User::pdEndDate].Properties[Value];%AS_OF_DATE%" /VLOG %LOG_FileNameAndPath%

Although it may seem a little crazy, it’s designed to allow the jobs be duplicated between “Development”, “QA”, “UAT” and “Production” without touching the job definition and without hard-coding user names, passwords, server names, database names, file paths and file names.  Although the above is an extreme case, variables are used extensively throughout all our jobs.

Therefore, it would be great to have the user interface provide some assistance in adding variables to the command line and elsewhere.  For example, anywhere you can use a variable the user interface could provide a right-mouse button click menu of variable names and formatting:

Variable Hiding

As mentioned earlier, we use a fair amount of variables and they save us a lot of time and errors.  We try to define most variables at the root level because our job group tree has “Development”, “QA”, “UAT” and “Production” as the 2^nd level down.  When promote a job from the testing regions to the production region, it’s useful to have the variables defined at the root, and then just provide overrides for each of the levels.  For instance, “PATH_Data” is defined as \\files\groups\techops\data at the root level.  However, the Development path immediately overrides this to be \\DevelopmentServer\Techops\Data.  A job that retrieves data for processing can start in the development group and pick up test files from a development location, and still be successfully duplicated and moved to the production region where it will pick up the same file off the production data share, all without re-defining and re-testing the job.

The downside to this approach is the large number of variables that show up in the list of variables at each level in the hierarchy.  It would be nice to be able to filter out variables in the screens to see only those that are relevant to this job or group.  I would suggest three filters:

·         Variables that have been defined\overrridden at this level.  This would be very useful for jobs, particularly when they are being executed ad-hoc.  We try to use “JOB” variables to indicate run-time parameters that the users can override (e.g. “JOB_FileName” – if they need to back-load a historical file).  Filtering the list of variables down to just those defined will make it much easier for the operators to find the variables they should touch. 

·         Variables that are being used at this level.  For a job, it would be variables (regardless of level) that are being used as part of this job, either as a condition, on the command line etc.  At the job group level, it would be variables that are used by sub-groups and jobs at this level.  It would be inclusive of the filter above.

·         All variables – which is useful when you need to pull in a new variable into a job being defined.

Console

Add Operations View

The current console works very well for maintaining jobs and job groups, running jobs ad-hoc and investigating specific jobs that are experiencing problems.  However, it’s much harder to get a sense of everything went on in the system, is going on in the system now, and will go on in the system later today.  The console needs an operations view that gives a 24+ hour window into the system.  The users would define the window of time they want to look at (maybe it is 8 p.m. yesterday to end-of-day today), and the system would show all the job instance that have run, or are scheduled to run, as well as their status.  This would also be a great way to show which jobs are waiting for conditions without having to drill into each running instance to read the log messages.  Operators should be able to run most of their day in this screen, re-running jobs that have failed, aborting jobs, acknowledging statuses etc.

In addition, since the operations view shows execution instances, the bottom half of the screen could be used to job execution details, including the log, status of each step and captured files, without having to pop-up the execution instance screen.

Conditions View

Today, in order to see what conditions a particular job is waiting for, you have to open the job instance and read through the job log.  For some jobs, particularly where there are multiple conditions, these messages are hard to sort through.  The system posts a new message to the log periodically to update this information.  Jobs that wait a long time for conditions to be met will have many of these messages, further compounding the problem.  We have one job that waits for 12 files up to 4 hours.  When the job fails, it takes over 6 minutes to open the history because the system has posted over 30,000 messages.

In addition, our extensive use of variables makes figuring out which file a job is waiting quite difficult.  It would be nice to have an easier way to see this in a grid layout:

Speed Issues

Most of the operations in the console need to speed up, with most dialogs taking 4+ seconds to open up.  There are a number of operations that are very slow and that appear to hang the console, such as opening a job group with lots of variables for the first time, or opening a job for the first time. 

The console also appears to ‘freeze’ periodically which we suspect is the periodic refresh of execution state.  Ideally this and other operations would occur on a background thread and not impact the user experience.

The reporting pieces of the system are un-acceptably slow, with many of the reports taking upwards of 4+ minutes to complete in our environment for relatively simple reports.

Installable Reports

It would be great if the system would allow for new reports to be created and deployed inside the application.

Enable Copy\Export From All The Grids

The grids currently support highlighting rows, but copying the content is not possible.

Grid Column Order & Add\Remove Columns

It would be nice to be able to add\remove columns from the display grids, and to be able to change the column order on a number of the grids.  The system should remember the users default selection each time the grid is re-visited.

Allow Multiple Windows

This is a source of constant frustration, particularly when trying to copy elements of a job.  We’d like to have the ability to open multiple jobs, job groups, variables etc. and keep the windows around.

Copy a Job Step

I would be great to be able to copy a particular job step and ‘paste’ it in another job.  Currently, you can only duplicate a job step inside the same job and there is no way to copy steps from one job to another.  It would be very useful, particularly for complicated steps, to simply copy and paste it between jobs. 

Even better, provide for shared job steps that can be re-used in different jobs.

Required Run-Time Values

It would be nice to be able to designate certain variables or job parameters as ‘user-supplied’. The user interface would prompt for these values when the job is inserted ad-hoc into schedule.  Some of these would have default values that the operator can override while others would have to be entered from scratch.

Time-Line View

It would be useful to be able to see all the jobs executed, in queue to execute, and schedule to execute on some type of time-line, that included estimates and actual run times.  For jobs that are in queue or scheduled to execute, the system would use some average duration to show the expected execution time, or use the job timeout value to estimate the duration (see below).  This could be further improved by showing the queues\agents that the jobs would run on.  This view would be useful to get a sense for any busy spots in the system, and to identify maintenance windows.

Resource Definitions

It would be great to see what jobs use which resources from the perspective of the resource itself.  For instance, with jobs use a particular executable, variable, or script.  This would make it considerably easier to identify the impact of changing a particular resource.

Duplicate To Different Path

As we promote a job from development, through testing, into production, we use the duplicate function.  Since all the values sent to the job are defined by variables, we can usually do this without having to change the job definition at all.  Right now, you can only duplicate a job to the same job group in which it resides, which forces you to give it a unique name.  It would be nice if you could duplicate a job to a different folder, eliminating the need for the rename.

Conditions

Waiting for conditions should not take up a queue slot

We had a situation where any one of 35 brokers could drop a file on our website for processing.  We wanted to process the files as soon as they came in, but needed to ensure that the files did not interfere with each other.  To ensure single access to the database for the processing file, we placed the 35 jobs on a single process queue.  Each job had a condition to wait for its file to show up.  We ran into an architectural problem with conditions in adTempus where a job that is waiting for conditions consumes a slot on the queue, and thus only one of the jobs was indeed waiting for the broker file. 

The request is for queues to be thought of as running queues, and that a job waiting for a condition has not yet been assigned its true queue.  It might be interesting to also be able to designate a queue for the evaluation of each condition.

E-Mail Arrival

We have a number of jobs that depend on an e-mail notifying us that some data is ready to be picked up.  It would be nice if we could designate an e-mail condition that opens a POP3 mailbox and looks for the arrival of a particular e-mail.  This would be coupled with an e-mail attachment extract step (see below).

Variable Conditions

See above in variables.

Conditions should be more easily seen in console

See above in console.

Core Features

Job Time Out

When defining a job, you should be able to set a time after which the job will be abandoned by the system.  This is to prevent run-away jobs (particularly ones on queues that limit resources), but also to warn operators when jobs are, over time, starting to stray from their assigned timeslot. 

Operating Conditions \ Expectations

In addition to monitoring time, it might be useful to monitor other resources, such as memory and CPU usage of jobs, and to set operating expectations for the same.  Reports could then be produced to show peak CPU\Memory usage at different times, or when jobs exceed their expected parameters, and to adjust the load on the agents accordingly.

Steps

SSIS Package Step

A step which is configured to execute SSIS packages would be extremely useful in our organization. We currently execute this using the command line runner, which involves building a rather lengthy command line (see above).  Having the UI support the running of SSIS packages (including supports for variables and property set statements) would greatly simplify this process.

User Defined Steps

It would be nice if the object model for the application allowed a developer to build a custom step type and install it inside the adTempus UI.  adTempus would define an interface that the custom steps would have to support, including methods for getting the user interface, and the property bag of values to be stored in the adTempus database.  This would allow a developer community to create plug-ins for adTempus.  Many of our jobs could then be defined as custom steps (for instance, we have a generic database extract tool VBScript – it takes a connection string, a file name, and a query, the results of which are pumped to a file).

E-Mail Attachment Extraction

This would be a custom step that extracts all the attachments in a particular e-mail file.  The job would have some definition to locate the right message, and some parameters for how to extract the files.  It should provide for extracting all files, or only some, and for renaming the files on the way out.

Running Jobs under Multiple User Profiles

On a number of occasions we’ve needed to run some steps in a job under different credentials than others.    For instance, we have a database extract job which runs under account A to connect to the database using integrated login, but has to run under account B in order to copy that file to its final destination.  Right now we run these as two chained jobs, but it would be nice to supply different credentials to different steps (as an option, not a requirement for every job).

PowerShell Step

Not 100% sure what we would do with it yet, but my admin staff swears by PowerShell, and one of your competitors (who won’t stop calling me!!) can’t stop talking about their PowerShell integration.

Variable Update Step

See above in variables.

Stop & Start Service

This job step should be enhanced to control this function on remote machines, rather than just the local machine.

FTP\SFTP\SSH Step

Many jobs have an FTP, SFTP or SSH component. It would be very useful if this could be done natively by the scheduler, including the management of security certificates and other credentials.

File\Directory Copy

A native file\directory copy step would be great.  Although calls to the shell can accomplish this, there are drawbacks with each.  With XCopy we have to give up any logging of the results (if ‘capture console’ is turned on xcopy fails for some reason).  “cmd.exe /C copy” works also but is somewhat slow, and doesn’t always deal well with sub-directories.

Run Shared Script

When running a shared script stored in the script library, it would be great if you didn’t have to write a little script to call the shared script.  Instead, the system could prompt for the parameters of the function in the shared script, and provide a user interface for supplying values.

PGP Encrypt\Decrypt

Some of our files are delivered encrypted with PGP, and others we have to deliver PGP encrypted.  It would be nice if adTempus would natively support PGP encryption\decryption, and storing the public\private keys for those operations (the same way it stores logon credentials).

Logging

Show Actual Command Line in Log

We’d like an option to show the actual command line that was run in the log.  This would be extremely helpful when troubleshooting jobs and when setting up jobs.  We often have to change the command line of a failing job to ‘cmd.exe /C echo <other parameters>’ in order to diagnose problems – it’s the only way we can see the fully resolved variables.

Distributed Scheduling

Redundant Masters \ Agents

The system should have a facility for setting up redundant masters for DR purposes.  These would take over as the primary master if they notice that the primary master goes down.  Likewise, agents should also have a redundant mirror.  Licensing should be adjusted so that said masters\agents are covered under existing license (or maybe a small premium) since the redundant masters\agents would only run jobs if the primary master\agent went down.

Taking Resources Off-Line

In order to facilitate patch deployments and other needs, it would be nice to be able to take agents off-line.  The best way to do this today is to take the queue off-line, but this is sometimes not suitable because some queues have multiple agents.  It’s also unclear what happens to scheduled jobs when their queue is un-available.  We’d like to keep the queue up and running, and just remove one agent.  Jobs would continue to go the queue, and would be dispatched to any available agent on the queue.  If there is no available agent on the queue, it will wait until an agent becomes available.

API

WCF or WebServices

Add a WCF or WebServices API to avoid distribution issues with the DLL’s.  This would radically simplify the use of adTempus calls from inside our custom applications.

Native .NET API

Eliminate the dependency on the COM layer that causes interop delays when using the API from inside .NET applications.

Deployments

Job Deployments

The deployment of executables, scripts, configuration files etc. to each of the agents is still a challenge.  It is even more challenging when considering that we’re using the same adTempus instance to manage both DEV, QA, UAT, and production environments.  We rely on side by side installation of the bits for each version of the utilities we want to run, and variables to keep track of which version is being used in which environment.  For example, we have a utilities package which is installed with an MSI:

·         For each utility in the package, we have a corresponding “EXE_” variable, whose definition reads like this: “%PATH_Utilities%SomeUtility.exe”. 

·         The “PATH_Utilities” variable is defined as “%PATH_BaupostProgramFiles%Utilities\%VER_Utilities%\”. 

·         The “VER_Utilities” variable is set for “1.00” on the production job group.

·         The “VER_Utilities” variable is set for “1.10” on the development job group, allowing the next generation of utilities to be deployed to the agents, without affecting production jobs.

·         The value of the “VER_Utilities” variable is updated on the production job group when the production jobs are ready to use the new version of the applications in the group.

There are several things that adTempus could do to facilitate the deployment of jobs in our environment:

·         Natively recognize some form of ‘deployment package’ that is to be replicated on each of the agents.

·         Natively recognize different versions of the applications and identify the version to be used when running a particular branch.

·         Provide versioning capabilities on shared scripts (currently we create new scripts with the version number e.g. “UpdateVariables2”, but this forces us to change all the job definitions as we promote the job).

·         Make it easier to provision a new agent into the system by automatically deploying the packages from the repository.  Right now this involves running installs on the agent for each of the components.

Patch Deployments

Having recently had to patch 4 different machines, it would be nice if adTempus provided some type of automated patch deployment – for instance, the patch would be installed into the console, and a deployment would be defined.  The agents would be responsible for executing the update on their own machines and restarting the agent using the new bits.

Agent Manifest Visible Through Console

It would be great if each time an agent starts, it sends a ‘manifest’ of some kind updating the MASTER as to its configuration (e.g. version of certain dll’s etc.).  Because some updates are only applied to the master, we always have to remote to each agent to get information about versions etc.

Notifications

When Jobs Taking Too Long

It would be nice to have the system be able to send notifications when jobs instances start exceeding their normal operating parameters.  This way, a job that is waiting for a file delivery can send a notification when it’s been waiting for 30 minutes, and operators can start researching before the job fails.  Right now, only the success or failure can serve as a trigger for the operators to start researching, and restarting a job chain can be complex after a failure.

Reporting

Jobs That Are Exceeding Their Operating Timeslot or Slowing Down

In conjunction with setting operating expectations of jobs (see above), it would be useful to be able to see jobs that are approaching their operating limits, or which seem to be slowing down.  This report could be used to identify jobs that might be in need of some optimizations, or whose time-window needs to be expanded.