How do I Trigger an On-Premise SQL Procedure from a d/EPM Process Engine Script

HI,

I want to trigger a workflow from a process engine (called by an end user on a front end form).

I plan to use the workflow to trigger an on-premise SQL server procedure.

I have looked at the IONPublishMessage option. This does work with a dataflow. However as the document I am sending is not a BOD - I am not able to "capture" it to trigger a workflow. (Activation policies has that option greyed out).

Any help much appreciated,

Regards Les.

Find more posts tagged with

Infor EPM Discussions

Comments

unknown

Hi,

This is how I was able to achieve my goal of launching a on-Premise SQL process from d/EPM

Use clicks on a action on d/EPM that runs a App Engine Process Script
The script creates a XML based message (I've adapted a basic one - a default existing document from the Data Catalogue for this purpose).
this message is sent to ION via IONPublishMessage with the existing BOD type created above e.g. "Sync.Workflow"
I've set up a document flow (not workflow) to route sync.workflow messages from the BI connector to the OnPremise DB connector
The stored procedure for saving the xml data on-premise is amended to look for set keywords and run different stored procedures accordingly. (this can also be set up as a trigger)

Regards - Les.

dtruchsess

Hi Les,

Is there somewhere I can find more details about your approach? And, do you have an example of the XML message that you created? I need to send some data back from d/EPM to my on-premise SQL database. I was able to export the data from d/EPM to the data lake, but I could only find an example to generate a JSON.

Thanks for your help,

Daniel

unknown

Hi Dan,

This is how I resolved it: (Feel free to improve on it. Its by no means a "official" route - but rather how I aligned the planets in my universe. )

Application Engine:

1 - Construct a BOD (Note: these would be XML format) using StringList datatype. I used the LoadTextMessage BOD using a sample as a starter - similar to one from the "Infor ION Technical Reference Guide" (Rel 11.1 - see section "Sample ProcessWorkflow to start a workflow" ) - obviously amended to suit my needs.

my example:

StringList rows = CreateStringList();

    Append(rows, "<LoadTextMessage xmlns="">schema.infor.com/.../2" ");
   Append(rows, "xmlns:xsi="">www.w3.org/.../XMLSchema-instance" ");
   Append(rows, "xsi:schemaLocation="schema.infor.com/.../2 ">schema.infor.com/.../LoadTextMessage.xsd" ");
   Append(rows, "xmlns:xsd="">www.w3.org/.../XMLSchema" releaseID="11.1" versionID="2.10.0">");
   Append(rows, "   <ApplicationArea>");
   Append(rows, "      <Sender>");
   Append(rows, "         <LogicalID>lid://infor.bice.bice</LogicalID>");
   Append(rows, "         <ComponentID>DEPM</ComponentID>");
   Append(rows, "         <TaskID>SendTextMessageBOD</TaskID>");

.... etc. Hope you get the idea.

at the end - convert to a string for the step #3

string sReturnValue = StringJoin(rows, "
");

2 - The data was encapsulated in the BOD's DataAreaTextMessagemessageText section. (I have also been able to send small tables by encapsulating the data using a CDATA tag). This area can be constructed here as XML and then deconstructed back to tabular format on the SQL end.

3. Use IONPublishMessage("Load.TextMessage", sReturnValue)

ION:

1. You need a BICE connector for d/epm and a stored procedure connector for SQL. Set up a document type of LoadTextMessage BOD (the BOD created in the previous section) that sends the BOD data as a parameter (data) to the destination SQL server.

EXEC [InforOS_WriteXMLData] @xmlData=';[Data]';

2. Set up the Data flow

Create a document flow from the BICE connector to the SQL environment using that LoadTextMessage Document.

SQL:

1. Create the stored procedure referenced in ION that accepts a XML parameter.

2. Reverse engineer the data section you encapsulated your data in the App Engine section. Could be a key-value data or a table. SQL should be able to read the XML natively. (I created a <meta> section in the text message to send codes - and SQL is able to launch different stored procedures based on those trigger "codes"

Finally - Activate all ION elements and run the App Engine Script.

Hope this helps. Good Luck.

Les.

P.S. I may have missed a point or two somewhere - feel free to reach out if you are stuck. Monitoring OneView traffic does help as it shows if there are any data issues.

Copyright © 2025 Infor. All rights reserved.