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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages
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.
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)
- 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.
- 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
- 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.
XHTML1234567891011<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
- 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
- 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
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.