Introduction

• Users might be interested in specifying jobs and dependencies from Microsoft Excel® spreadsheets.
  • In a situation when business staff is in charge of specifying jobs then Excel® is a frequently used tool.
  • As a no-code approach this allows to specify jobs and dependencies by users without detailed know-how of JS7 - Workflows.
• The solution explained from the article creates jobs and workflows from Excel® spreadsheets ready for import into JS7.
  
  • The solution makes use of a PowerShell cmdlet for Linux and Windows.

Prerequisites

The following PowerShell modules are used for this purpose:

• ImportExcel PowerShell Module (3rd party) to read Excel® spreadsheets on Linux and Windows.
• Optionally the JS7 PowerShell Module to access the JS7 - REST Web Service API for automated import and deployment.
  • If the module is not used then users can manually import resulting workflows using the JOC Cockpit user interface.

Download

The following files are used for the solution:

• Sample Excel® spreadsheet: jobs.xlsx
• PowerShell cmdlet: New-JS7WorkflowFromExcel.ps1

Getting Started

Specify Jobs and Dependencies with Excel®

Download the jobs.xlsx Excel® spreadsheet file.

• The first worksheet offers a number of jobs and dependencies from the following columns:
  • NodeID: A unique identifier for the occurrence of a job. If the same job occurs a number of times then the Name column will hold the same job name, however, a unique value is specified for the NodeID column.
  • SuccessorID: Specifies the NodeIDs of jobs that are direct successors of the current job. If more than one job is specified then they will will be executed in parallel. NodeIDs in this column are separated by a space.
  • Instruction:  Makes use of the fixed value Job. This column is reserved for later support of further JS7 - Workflow Instructions.
  • Name: Specifies the job name.
  • Description: Optionally specifies a descriptive title for the job.
  • Agent, Subagnet Cluster: Specifies the name of a Standalone Agent that is configured in your JS7 scheduling environment. If an Agent Cluster is used then its name is specified as the Agent and the respective Subagent Cluster is specified, see JS7 - Agent Cluster.
  • Script: Specifies the job script that will be executed. Multiline input is allowed.
  • Error Handling: Specifies one of the following options for JS7 - How to apply error handling:
    • STOP: If the job fails then the order will be put to the FAILED state and will remain with the job. Failed orders require user intervention to resume, to suspend or to cancel the respective order.
    • IGNORE: Any job error is ignored and order processing continues, see JS7 - Try-Catch Instruction.
    • RETRY: The job will be retried in case of error for 3 times with a delay of 60s, see JS7 - Retry Instruction.
    • LEAVE: The order will leave the workflow with an unsuccessful History outcome, see JS7 - Finish Instruction.
  • Fail on stderr: Specifies that in case that the job will write output to the stderr channel the job will be considered to be failed, see JS7 - Job Instruction.
• Users can adjust column names, see JS7 - PowerShell Import from Excel Script - New-JS7WorkflowFromExcel.ps1.

Run the PowerShell Cmdlet

Download the PowerShell cmdlet: New-JS7WorkflowFromExcel.ps1

• Basically two arguments have to be provided:
  • -ExcelPath: Specifies the path to the Excel® spreadsheet file that holds the specification of jobs and dependencies.
  • -OutputDirectory: Specifies the directory to which workflow *.json files will be stored.

./New-JS7WorkflowFromExcel.ps1 -ExcelPath /home/sos/excel/input/jobs.xlsx -OutputDirectory /home/sos/excel/output/jobs

./New-JS7WorkflowFromExcel.ps1 -ExcelPath "C:\js7\excel\input\jobs.xlsx -OutputDirectory "C:\js7\excel\output\jobs"

Examples

Examples for advanced parameterization are provided as follows.

Create Workflows for specific Worksheets and Column Names

./New-JS7WorkflowFromExcel.ps1 `
    -ExcelPath /home/sos/excel/input/jobs.xlsx `
    -WorksheetName Workflow01 `
    -WorksheetColumns @{ 'nodeid' = 'NodeID'; 'successorid' = 'SuccessorID'; 'instruction' = 'Instruction'; 'name' = 'Name'; 'description' = 'Description'; 'agent' = 'Agent'; 'subagentCluster' = 'Subagent Cluster'; 'script' = 'Script'; 'errorHandling' = 'Error Handling'; 'failOnStderr' = 'Fail on stderr' } `
    -OutputDirectory /home/sos/excel/output/jobs `
    -WorkflowName EOD-Workflow `
    -Title "EOD Processing" `
    -AgentName primaryAgent

Create Workflows from a number of Excel® Spreadsheet Files.

Get-ChildItem *.xlsx | Foreach-Object { ./New-JS7WorkflowFromExcel.ps1 -ExcelPath $_.FullName -Title $_.BaseName -OutputDirectory /home/sos/excel/output/jobs }

Further Resources

• JS7 - PowerShell Import from Excel Script - New-JS7WorkflowFromExcel.ps1