JAMS Admins

In this article, advanced PowerShell users will learn how to create a PowerShell module to incorporate into a JAMS Job.

Create a PowerShell module to incorporate into a JAMS Job

1. Open C:\Program Files\WindowsPowerShell\Modules.
2. Add a New Folder with the name you want to use for your PowerShell module (e.g. SampleOracleModule).
3. Open PowerShell ISE to a blank tab.
4. Save the PowerShell Script Module file to the C:\Program Files\WindowsPowerShell\Modules directory using the same name with file extension .psm1 (e.g. SampleOracleModule.psm1).
5. In the saved SampleOracleModule.psm1 file, write the PowerShell script, and click Save.

   Below is a sample PowerShell script:

   

   Elements within the sample PowerShell script are as follows:

• Function is defined within the module and is exposed as a typical cmdlet in PowerShell (e.g. select-OracleData).
• Param represents all parameters you have access to on that cmdlet. $ServerName is the name of the Oracle server, $UserName is the login account name, $Password is the password to the Oracle server, $ServiceName is part of the Oracle connection string, and $Query is the query from which you want to obtain results.
• Begin represents code that runs to initialize the script before you begin processing. Here, you reference the Oracle Data Access assembly for the ability to create Oracle connections and commands. Within this section, you declare a Data Table using $resultSet to store the results from the query. You will then define the connection string to parse in the various Parameters (e.g. ServerName, ServiceName, UserName, Password). You will then create an Oracle connection object.
• Process allows you to run the cmdlet which opens an Oracle connection, define a command, and specify $Query as the command execute. The “Execute the command…” section runs the query, using a Data Adapter to fill the result Data Table that you defined earlier. The “Write results to the log file…” section outputs the results which display in the JAMS Job Log or through the pipeline output in PowerShell. The “Return results” section outputs results to the pipeline.
• End closes the Oracle connection.
6. Open PowerShell. When you open PowerShell, PowerShell automatically checks the \\WindowsPowerShell\ Modules directory to load the .psm1 file. At the C:\WINDOWS\system32> prompt, enter Import-Module and the file name (e.g. Import-Module SampleOracleModule) to pull in the parameters. Next, enter Select-OracleData. Hit Tab to have PowerShell auto-complete the command. Hit Enter. PowerShell pulls in the parameters you defined earlier (ServerName, UserName, Password, and so forth). The example below uses the Oracle Server Name is Orchid64.

   

7. Once you finish entering Parameters, hit Enter to run the query and view the Oracle results in a table. The sample results are shown below. You can incorporate this cmdlet into a JAMS Job. 

   

Incorporate the module into a JAMS Workflow Job

Once you create the PowerShell module to incorporate into a JAMS Job, you can take the module a step further by incorporating it into a JAMS Workflow Job.

1. Create a WFToolbox.config file inside your JAMS Client directory located at C:\Program Files\MVPSI\JAMS\Client by default. Right-click from within the directory, select New File, name the file, and change the file extension to .config (e.g. WFToolboxOracleSample.config). When you open a JAMS Workflow Job, JAMS checks your client directory for any file names that begin with “WFToolbox” and have a .config file extension.
2. 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 PSModuleName should match the name of the PowerShell module file you created (e.g. SampleOracleModule). Here is an example of how the content within a WFToolbox.config file should read:

   

3. 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. Sample Oracle Activities). Under the new Workflow Activity, notice the new function you added (e.g. Select-OracleData). You may add more functions by modifying the PowerShell module file.

Add a Workflow Activity Using a PowerShell Wrapper

If you decide not to create the new Workflow Activity using the above method, you may do so using the PowerShell Wrapper tool from within the Workflow Job.

1. Select PSWrapper from the Workflow Toolbox category list by dragging it to the Activity Design area. From the dialog box, select the module file you just created (e.g. SampleOracleModule) from the Module drop-down. The dialog box populates the Command field with the associated Command (e.g. Select-OracleData). Parameter Set defaults to _AllParameterSets.
2. The Properties panel, to the right of the Activity panel, populates with default Properties that come with PowerShell. Here, you will see the Properties you added in the PowerShell module file (e.g. ServerName, ServiceName, UserName, Password, Query).

   

   Note: Rather than enter each Property, you may also define Workflow Variables in the Activity panel which can then be referenced as the value in the Properties panel.

   

3. Save the Workflow Job with the newly defined Workflow Activities and Properties. Submit and run the Workflow Job. The expected results should be similar to what you saw in PowerShell.

See Also

Read more JAMS Support articles about Workflows.

How to Integrate Workflow Output with a Third-Party Data Source Using PowerShell (sequel to this article)