How to Integrate Workflow Output with a Third-Party Data Source Using PowerShell

JAMS Admins

In this article, advanced PowerShell users will learn how to integrate JAMS Workflow output with a third-party data source (e.g. Oracle).

How to Integrate with a Third-Party Data Source (e.g. Oracle)

You may use Workflow Job outputs to integrate with a third-party data source such as Oracle. Using the powerful tools we describe here, you have the potential to pull all manner of data from Oracle or even pass data from one JAMS Job or script to another.

Note: Before you proceed with the steps in this article, create a custom Workflow Activity and WFToolbox configuration file in the client and/or server directories. Follow the instructions in the article, How to Create a Custom Workflow Activity Using PowerShell.

The first way to integrate with a third-party data source is using a PowerShell Script WorkFlow Activity.

Select PSScript from the Workflow Toolbox category list by dragging it to the Activity Design area, below the existing Workflow Activity (e.g. Select-OracleData), to create a Sequence.
Define a Workflow Variable to capture the results from the initial Workflow Activity (e.g. Select-OracleData). Select the Variables tab in the Activity panel. Enter a name for the new variable and select the variable type.
To find the relevant Variable Type, select Browse for Types from the drop-down. Using the example above, type System.Collections.ObjectModel.co, which will populate below the Name field as System.Collections.ObjectModel.Collection with additional drop-down options. Select Browse for Type from the drop-down.

Type PSObject, and select System.Management.Automation.

The new Workflow variable populates under Variable Type, in the new Workflow Activity.

When you select the Workflow Activity name (e.g. Select-OracleData), the Properties panel displays the relevant properties for that Activity. Following the same example, the hovertext of the Results Property shows that it is an OutArgument that is a collection of PSObjects.
Type “result” in the Results field to match the variable you just created.
Click on PSScript to view Properties, and enter result in the Input field.
Enter your PowerShell script, adding the $input element. The following is an example of a PowerShell script you may enter.
When you finish entering your script, click Save and Close. Return to the Monitor and Submit your Workflow Job.
When the Workflow Job completes, double-click the Job from the Monitor view. The Log File tab opens, by default. You may need to click Refresh if you do not yet see the final output. Here is an example of the output from the above PowerShell script.
,

Return to Top

How to Use the Workflow Activity Outside of PowerShell

You also have the option to extract the data from the PowerShell object, without using PowerShell, by using the Microsoft ForEach activity.

From the Workflow Activity Toolbox, search for the Microsoft ForEach activity and drag it into the Sequence below the Select-OracleData activity. With the ForEach activity highlighted, update its TypeArgument Property, which is found in the Properties panel to the right. From the TypeArgument drop-down, select the collection System.Management.Automation.PSObject. In the ForEach activity, type result as the VB expression. These entries tell the Sequence to run through a collection of PSObjects and specify the Workflow variable. Within the Sequence, you may also convert it to something more useful that you can use with another activity you created or pass it to a JAMS Job Parameter. For example, you can pull out the DataRow from the PSObject, so it can be used outside of a PowerShell context. Another example might be if your PowerShell Workflow Activity returned an integer, you could convert it from a PSObject to the underlying integer, so that the value could be passed to a Job parameter with the SubmitEntry activity.
To add the variable dataRow, select the Variable Type drop-down and browse for System.data.datarow. Select DataRow from the results.
From the Workflow Activity Toolbox, search for the Assign activity and drag it to the body of the ForEach container. PowerShell wraps variables within the PSObject variable type. When you run the PowerShell script, it returns a collection of data rows as a PSObject. The Assign activity extracts the data row from the PSObject.
In the Assign activity, type dataRow in the To field and CType(item.BaseObject, System.Data.DataRow) in the VB Expression field. Workflows use VB as the scripting language. The VB expression here converts the variable type. Item represents the PSObject. BaseObject represents the data row. The Workflow Job editor uses the VB language with the “Strict” option. That option restricts implicit data type conversions. In this example, because we are using a "Strict" option, we need to hard-cast it rather than use it as a data row.

Note: More information about the "Strict" option is documented by Microsoft, here.
The remaining expression elements tell the activity to convert it to a data row object. The Assign Activity converts the expression into a data row.
To write a message to the log, search for WriteLine in the Workflow Activity Toolbox and drag it below the Assign activity, within the Sequence container. In the Text field, enter the VB expression “User Name” + dataRow(“UserName”).ToString(). Here, the VB expression creates a log called User Name, parses-in the data row object, and specifies the column name as UserName. Because the Strict option is on within the script, WriteLine casts the expression to a string to be explicit in defining what the object should do.
Submit and run the Workflow Job to verify the message is present in the Log.

Return to Top

How to Create a Custom Workflow Activity in Visual Studio

Open Visual Studio. Create a new Visual Studio project and make it a Class Library.
From the Solution Expert panel, under CustomProcessingActivities, delete the default Class that comes with the project (e.g. Class1.cs). Add a new Class titled, PerformDynamicCleanup.cs.
The PerformDynamicCleanup activity can be used when another process or executable produces output files that need to be cleaned. The purpose of creating this Activity is to take a path and archive or delete the output files. When you add the PerformDynamicCleanup code, include using statements as well as the following elements:
- Activity Properties: Defines activity properties that will be accessible within the Workflow editor. OutputDirectory points to the Workflow and Archive directories. CanInduceIdle, if set to True, indicates that the Activity will pause during the execution until the user takes action. ArchiveDirectory: Defines the directory where files should be moved to.
- Protected Override Void Execute: This method determines the execution action of the Activity. A bookmark allows the user to pause execution until the user takes action or a message from another Activity tells the execution to continue. In this example, we use CreateBookmark to indicate “Waiting for user action.”
- Private Void CreateBookmark: Within this method, workflow context is taken from the NativeActivity to create a bookmark. When you name the bookmark, choose a unique name that includes the Activity’s ID which is automatically generated. BookmarkCallback is the name of the callback that executes when the Activity receives the data and response for this bookmark.
- JAMSTrackingRecord: This section is specific to JAMS, allowing you to change the color of the Activity and specify responses. For example, Warnings are orange, Satisfactory Activities are green, and so forth. You will also include the bookmark name in the JAMSTrackingRecord. Choose Text (string) or Select (drop-down) for ResponseType. If you choose Select, enter ResponseValues such as Delete and/or Archive as comma-separated entries. Next, specify the State. For this example, we entered BookmarkWait to indicate we are waiting on a user action.
- Send the Tracking record: This instruction sends the tracking record to the JAMS Scheduler.
Note: If you see line errors, you may need to add References to Workflow Assemblies.
With regard to the BookmarkCallback, ResumeActivity, when the Bookmark resumes, the script executes this method. If you want to take additional actions, you may define additional blocks within the switch statement. Use the workflow context to retrieve the current value of the activity properties. For this example, we use context.GetValue(OutputDirectory) and context.GetValue(ArchiveDirectory). These assume the user sent data that this script is now pulling-in using GetValue. Enter Console.WriteLine($”Received cleanup action: {cleanupAction}”) to write a message to the Job’s Log file to indicate that the cleanup action was successful.
The purpose of the default block within the switch statement is to handle unexpected response data. This can happen if you are resuming the Bookmark from an external application or PowerShell. If using the Client, we provide the response options defined earlier, and this section does not apply. The DeleteOutputFiles and ArchiveOutputFiles methods check the specified directory and either delete the output files or copy them to the archive location.
Now that the Activity is created, select Rebuild Solution from the Visual Studio menu, Build. Navigate to the output directory to locate the assembly files (.dll and .pdb extensions).
Copy the two files and paste them into the Client and Scheduler directories located at C:\Program Files\MVPSI\JAMS\ by default. If you run the Workflow on an Agent, place these files in the Agent directory as well.
Now that the assemblies are in the appropriate locations, create a WFToolbox configuration file to pull in this Workflow Activity. An example title for this file is WFToolbox.FileProcessing.config.
Open the config file in Notepad ++ or other XML editor, and write your configuration settings. The header and footer are standard for JAMS config files. Add a Categories section as shown in the image below. The Name you define here will be the name of the new, expandable category that gets created in the JAMS Workflow Toolbox menu. The AssemblyName should match the name of the project you created in Visual Studio (e.g. CustomProcessingActivities). Here, you will also enter the specific Toolbox items. The TypeName is the name of the project plus the name of the class which is the name of the Activity, in this example. Here is a sample of how the content within a WFToolbox.config file should read:
Save the new configuration file. Open JAMS and create a new Workflow Job. First, you will notice the new Workflow Category you added in the WFToolbox.config file (e.g. Custom File Processing). Under the new Workflow Activity, notice the new function you added (e.g. PerformDynamicCleanup).
Drag the PerformDynamicCleanup Activity into a new Sequence. Copy the Properties to the PerformDynamicsCleanup Activity Properties panel. Because these are InArguments, you can also pull Properties in using Workflow Variables.
Save and run the Workflow. View the output message. Then, verify the Workflow Activity is bookmarked (i.e. the Activity is highlighted).
The Workflow Activity remains highlighted until the user takes action. Right-click on the Activity and select Available Actions. Notice, the Delete and Archive options you added display.
To interact with PowerShell, follow the Workflow setup above, and submit the Workflow. Open PowerShell and enter the Get-JAMSBookmark command line. Specify the Entry Number (i.e. “310”) which returns a Dictionary.
Add the Bookmark value by specifying the Bookmark Data Key (i.e. “Delete”). Hit Enter to view the Dictionary. Next, enter Send-JAMSEntry and Entry Number (i.e. “310”) to send the Bookmark response to a JAMS Job.
You will notice the Workflow Activity is highlighted green to indicate JAMS acknowledges the Bookmark action.
Verify the log file to ensure the expected action took place (i.e. Delete).