Capturing field value from Web Service and passing to SQL Transformation

[Former Member]

Hi All,

I'm working on the following scenario in Data Services (v4.2).

Web Service (Request) -> DS (expose RT job as service) -> SQL Query -> Response to Web Service

Web service is sending single field - like Vendor #. Now, the issue is that I need to capture this field and pass this to the SQL Transformation.

The hard-requirement is that I need to use specific SQL Query to get the values (it is nested actually). So, I need to find a way to capture the input field from Web Service and pass this to the SQL Transform query.

I believe there are two ways of doing it:

1. Using the script to declare a Global Variable

2. Using custom function to capture input field and assign it to GV

Unfortunately, I've not been able to get this to work. Need help to fix this issue.

Regards,

Guru

[former_member220897]

You would split your data flow into several steps (understand that RT jobs are not limited to a single Dataflow)

1. A data flow to read the message and output the value to a temporary file or a table

2. A script to read the value from the file/table into a variable

3. A data flow with an SQL query using a parameter which is passed from the variable.

[Former Member]

Thanks Andrey.

One follow-up question: Is it possible to read the value from the XML input (when consumer is passing the XML field to invoke the service) into variable directly, without having to temporarily store it in a file/table. This will avoid the issue of correlations when multiple files are passed at the same time.

[former_member220897]

Unfortunately, you cannot output a variable value from a dataflow directly.

However, remember that in order to pass multiple input messages at the same time you would have to configure multiple service instances in your RT service, as each instance will process messages sequentially. So to avoid correlation issues you just assign unique file names (or row ids) per each job instance so that they are not shared. Or you can use Memory datastore which is also not shared between job instances.

[Former Member]

Hi Aasavari,

Here are some answers to your queries:

- You're right. RT service is going to be consumed by an external entity, which will post a Vendor# in the form of XML message

- RT service has a RT job running in the background, which will take that Vendor# and return the response back

- The core of RT job lies in querying an Oracle DB to fetch some payment related details based on the Vendor# received from the RT service

- I am using the SQL transformation step in my data flow to execute the SQL query (nested query) on the Oracle DB (which I have already added as a datastore)

- If I hard-code the Vendor# in my SQL query, I am getting the detailed response back in the RT service

- The help required is to pass the Vendor# dynamically to the SQL query, captured during the RT service consumption by external entity

Here are the details of the RT Job/DataFlow:

Regards,

Guru

[aasavaribhave]

If I understand correct you send a web service request that contains a field like vendor # to a RT service.

The RT service has a RT job running in background that can retrieve and consume that field and return response back.

Where is sql query - are you referring to query transform \ SQL transform and is it in that RT job? When RT service receives the request, it receives the input field that you can unnest and retrieve its value from input request inside the job. Post your dataflow.