Call Oracle UCM Web Service in SSIS (Read XML SOAP API)

Contents hide

1 Introduction

2 Prerequisites

3 About Oracle UCM Web Service

4 Step-By-Step

4.1 Read from Oracle UCM Web Service using XML Source

4.2 Loading Oracle UCM SOAP API data into SQL Server / Other Target

5 Conclusion

5.1 More from ZappySys Blog

Introduction

In this post we will learn how to access data from Oracle UCM Web Service (Middle layer for WebLogic) and load into SQL Server or any other target. We will use SSIS XML Source to achieve this result.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
(Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

About Oracle UCM Web Service

If you are not sure what is SOAP Web Service and how to call in SSIS, First read this article to understand general concepts of calling SOAP API in SSIS.

UCM Web Service offers calling various commands. You have to send XML Request and server will send you XML response with requested data. We will use Array Transformation Technique (Key / Value Pattern) to extract complex nested XML data.

Step-By-Step

Now let’s look at how to call Oracle UCM SOAP API in SSIS.

Read from Oracle UCM Web Service using XML Source

Drag Data Flow Task in SSIS Designer
Inside Data flow drag ZS SSIS XML Source from toolbox
First enter URL as below (Replace INSTANCE ID with your own instance)

https://YOUR-INSTANCE-ID.oraclecloud.com/idcws/GenericSoapPort

1

https://YOUR-INSTANCE-ID.oraclecloud.com/idcws/GenericSoapPort
Now Check Use Credentials and select new ZS-HTTP connection
Enter your User ID and Password to call Web Service using Basic Authentication. Click OK to save.
Configure HTTP Connection – Call Oracle UCM Web Service (SOAP XML)
On XML Source UI Select Content Type = XML (text / xml, charset=UTF-8)
Select Request Method as POST
Click Enter Raw Edit for HTTP Headers and enter below

SOAPAction: "urn:GenericSoap/GenericSoapOperation"

1

SOAPAction: "urn:GenericSoap/GenericSoapOperation"

Click on Edit Body and enter your Request Body in XML format.For example to call GET_SEARCH_RESULTS service to retrieve the search results for the passed query text you can submit Request Body as below.
NOTICE: String in Search Text was surrounded by backtick ( ` ). Single tick / double quote didn’t work in our case.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Body>
  <ucm:GenericRequest webKey="cs">
    <ucm:Service IdcService="GET_SEARCH_RESULTS">
      <ucm:Document>
        <ucm:Field name="QueryText">dDocTitle <starts> `MY_sites_201`</ucm:Field>
      </ucm:Document>
    </ucm:Service>
  </ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">

<soapenv:Body>

<ucm:GenericRequest webKey="cs">

<ucm:Service IdcService="GET_SEARCH_RESULTS">

<ucm:Document>

<ucm:Field name="QueryText">dDocTitle <starts> `MY_sites_201`</ucm:Field>

</ucm:Document>

</ucm:Service>

</ucm:GenericRequest>

</soapenv:Body>

</soapenv:Envelope>

Now Select Filter (Browse upto Fields under SearchResult node. Once you click OK try to edit your Expression so it looks like below. As you may notice we changed ResultSet[*] to ResultSet[?(@@name==’SearchResults’)]
This will ensure that we extract records from only SearchResult Node and not other nodes such as UserAttribInfo or EnterpriseSearchResults

$.env:Envelope.env:Body.ns2:GenericResponse.ns2:Service.ns2:Document.ns2:ResultSet[?(@@name=='SearchResults')].ns2:Row[*]

1

$.env:Envelope.env:Body.ns2:GenericResponse.ns2:Service.ns2:Document.ns2:ResultSet[?(@@name=='SearchResults')].ns2:Row[*]

Select Filter
Now go to 2D Array Tab. Select Key / Value Pattern Option.
Select Column Names / Value Filters as below. You can Use Browse Option to navigate to that field or just type below directly. Below expression will Transform Rows into Column. Foreach attribute name we will get one column.Column Name Filter => $.ns2:Field[*].@name
Column Value Filter => $.ns2:Field[*].#text
Now Click Preview to confirm it works
That’s it, you can now click OK to close UI.

Loading Oracle UCM SOAP API data into SQL Server / Other Target

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).

Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
Connect our Source component to Upsert Destination
Double click Upsert Destination to configure it
Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
Select Target Table or click NEW to create new table based on source columns
Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
Click OK to Save Upsert Destination Settings
That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task

Conclusion

In this post we saw how easy it is to consume XML SOAP Web Service in SSIS. We called SOAP Service for Oracle UCM Web Service and loaded data into target like SQL Server. You can download SSIS PowerPack and try many other scenarios like this using 70+ other SSIS Tasks /Components not mentioned in this article.