Page History

...

• ImportExcel PowerShell Module (3rd party) to read Excel® spreadsheets on Linux and Windows.
• Optionally the JS7 PowerShell Module to is used to access the 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.

...

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

...

Download the jobs.xlsx Excel® spreadsheet file.

• The first worksheet offers worksheets included with the spreadsheet file offer 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 holds the same job name, however, a unique value is has to be 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 branches. 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 Subagent 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.

...

• Job dependencies are designed along a Directed acyclic graph (DAG). This includes a few rules for input to the Excel® spreadsheet:
  • One or more initial jobs are required as the graph's root node(s). Initial jobs must not be specified as successors in any of the SuccessorID column values.
  • One final job has to be specified that does not indicate a successor jobs in job in the SuccessorID column.
  • The order of entries to the Excel® spreadsheet is not relevant.
• In a simplified way this a directed graph works with two columns:
  • The NodeID column specifies the unique identifier of the given occurrence of a job.
  • The SuccessorID column specifies one or more NodeIDs of successor jobs that are separated by a space.
    • If more than one NodeID is specified then this will fork processing of the indicated successor jobs to be performed in parallel branches.
    • When all NodeIDs specified with the SuccessorID column of a given job node indicate the same NodeID as the SuccessorID with their individual entries then they will be joined before the indicated successor job node.
    • Forking can include any number of branches in 
  x
  • • specified by NodeIDs in a SuccessorID column.
    • Forking can be nested preferably at not more than 15 levels.

Run the PowerShell Cmdlet

...

• 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.
• JS7 workflow files will be added to the js7-import.zip archive file in the current working directory.
  • A different location and file name can be specified using the -OutputArchive parameter.
  • Users can import the archive file into JS7 using the JOC Cockpit's Configuration view that offers the Import button. The archive file does not include a folder hierarchy but holds workflows from its root. When importing archive files with JOC Cockpit users can specify the target folder to which workflows will be added.
  • For details see JS7 - Inventory Export and Import.

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

...

Users who prefer to run the PowerShell cmdlet directly from the Unix Shell can add the call to the script PowerShell cmdlet and its parameters to an executable shell script like this:

...

• The Get-ChildItem Cmdlet will select any Excel® spreadsheet files in the current directory. Each Excel® file is pipelined to the PowerShell Cmdletcmdlet.
• This allows to create workflows from a larger number of Excel® spreadsheet files in a bulk operation.

...