Integration between FTP - Sharepoint (Upload documents): Oracle Integration Cloud

In this article, we'll show how to pull the file from the FTP location and upload to the SharePoint repository.

To complete this article we should have followings:

• Sharepoint REST connection: Refer the article to create Sharepoint connection with REST API
• FTP connection: Refer the article to create the FTP connection

Below is the Sharepoint REST API which is used to upload the document in Sharepoint repository.

URL:

https://online.sharepoint.com/sites/<site_name>/ _api/web/GetFolderByServerRelativeUrl('<relative_patjh_of_folder>’)/Files/add(url='<doc_name>',overwrite=true)

For example:

https://online.sharepoint.com/sites/ devsite/_api/web/GetFolderByServerRelativeUrl('/sites/ devsite/ParentFolder/TestFolder')/Files/add(url='doc1.pdf',overwrite=true)

Headers:

Authorization: Bearer <access_token>

Accept: application/json; odata=verbose

Request Payload:

Upload file as binary in body

Use the above API in Oracle Integration cloud to upload the document.

• Create a scheduled integration
• Drop the FTP adapter and enter the name of the endpoint(ListFiles)

• Configure properties as below and click on the Next button
• Select Operation: List File
• Input Directory: Enter the directory from which files to be listed
• File Name Pattern: *

• Complete the wizard
• Drop For-Each activity just below the FTP adapter and configure properties as below
• Name: LoopOverTheFiles
• Repeating Element: Drop the repeated element of ListFile Response
• Current Element Name: CurrentFile

• Drop the FTP adapter(under For each) from where the file needs to read, enter the name of endpoint(ReadFiles) and click the Next button

• Configure properties as below and click on the Next button
• Select Operation: Read File
• Input Directory: Enter the directory from where the files to be read
• File Name Pattern: abc.txt

• Select No radio button from Do you want to define the schema for this endpoint? and click the Next button

• Complete the wizard
• Edit the ReadFiles mapper and do the mapping as below:
• CurrentFile->File->directory ------>  SyncReadFile->FileReadRequest->directory
• CurrentFile->File->filename  ------>  SyncReadFile->FileReadRequest->filename

• Drop the Sharepoint REST connection, configure below properties and click the Next button
• What do you want to call your endpoint: UploadDoc
• What is the endpoint's relative resource URI?: 

/sites/{site}/_api/web/GetFolderByServerRelativeUrl('{Folder Name}')/Files/add(url='{documentname}',overwrite=true)

• What action does this endpoint perform: POST
• Check the checkbox Add and review parameters for this endpoint
• Check the checkbox Configure a request payload for this endpoint
• Check the checkbox Configure this endpoint to receive the response

• Click the Next butto

• Select Raw radio button from Select the request payload format section and click the Next button

• Select JSON Sample and then click the <<< inline >>> link, then enter below sample json

{

    "d": {

        "__metadata": {

            "id": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')",

            "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')",

            "type": "SP.File"

        },

        "Author": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/Author"

            }

        },

        "CheckedOutByUser": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/CheckedOutByUser"

            }

        },

        "EffectiveInformationRightsManagementSettings": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/EffectiveInformationRightsManagementSettings"

            }

        },

        "InformationRightsManagementSettings": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/InformationRightsManagementSettings"

            }

        },

        "ListItemAllFields": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/ListItemAllFields"

            }

        },

        "LockedByUser": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/LockedByUser"

            }

        },

        "ModifiedBy": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/ModifiedBy"

            }

        },

        "Properties": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/Properties"

            }

        },

        "VersionEvents": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/VersionEvents"

            }

        },

        "Versions": {

            "__deferred": {

                "uri": "https://online.sharepoint.com/sites/devsite/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/devsite/ParentFolder/TestFolder/doc1.pdf')/Versions"

            }

        },

        "CheckInComment": "",

        "CheckOutType": 2,

        "ContentTag": "{ssss121212-9294-4332-9D09-BBsdds2232323},7,2",

        "CustomizedPageStatus": 0,

        "ETag": "\"{ ssss121212-9294-4332-9D09-BBsdds2232323},7\"",

        "Exists": true,

        "IrmEnabled": false,

        "Length": "1564522",

        "Level": 1,

        "LinkingUri": null,

        "LinkingUrl": "",

        "MajorVersion": 7,

        "MinorVersion": 0,

        "Name": "doc1.pdf",

        "ServerRelativeUrl": "/sites/devsite/ParentFolder/TestFolder/doc1.pdf",

        "TimeCreated": "2019-05-16T10:03:27Z",

        "TimeLastModified": "2019-05-16T10:03:27Z",

        "Title": "Test tilte Updated",

        "UIVersion": 3584,

        "UIVersionLabel": "7.0",

        "UniqueId": "2211de2-9294-1111-2222-ff45b5bc2e0f"

    }

}

•  Select Other Media type and enter application/json; odata=verbose. Click on the Next button

• Edit the UploadDoc mapper and do the mapping as below:
• ReadFiles....->FileReference ----- > streamReference
• ReadFiles....->filename -----> TemplateParameters -> documentname
• Hard code the sitename as "devsite" 
• Hard code the foldername as "Shared%20Documents/TestFolder"

The integration is completed now. Activate the integration.

• Put some files on the SFTP location and Submit the integration

• See all the files should be uploaded into the Sharepoint repository. 

OAuth Custom Three Legged Security Policy in REST Connection: Oracle Integration Cloud

In the previous article, we have described the OAuth Custom Two Legged Security Policy in REST Connection. This article will show how to create a connection with REST APIs which uses the three-legged OAuth security policy to access the APIs.

For the demo purpose, we'll take an example of LinkedIn REST API which uses the three-legged authentication to authenticate the REST APIs.

As a pre-requisite, we have to register an application in the LinkedIn developer portal which can be accessed here. During registration of the application provide the Redirect URLs in the below format:

https://<ICS-HOST:PORT>/icsapis/agent/oauth/callback

Once the application is registered it will provide the client id and client secret.

Let's see how to create REST connection in oracle integration cloud which will use the three-legged authentication.

• Create a REST connection with name REST_LinkedIn_conn
• Click on the Configure Connectivity button and enter below details
• Connection Type: REST API Base URL
• TLS Version: TLS v1.2
• Connection URL: https://api.linkedin.com

• Click on the Configure Security button, enter below details
• Security Policy: OAuth Custom Three Legged Flow
• Authorization Request:

https://www.linkedin.com/oauth/v2/authorization?response_type=code&client_id=81y6xeb6x8kqyw&redirect_uri=https://<ICS-HOST:PORT>/icsapis/agent/oauth/callback&scope=r_liteprofile+r_emailaddress+w_member_social

• Access Token Request:

-X POST -H "Content-Type: application/x-www-form-urlencoded" -d 'client_secret=X0tiWP105Utde7iZ&grant_type=authorization_code&redirect_uri=https%3A%2F%2Ficsinstance-a1111.integration.us2.oraclecloud.com%3A443%2Ficsapis%2Fagent%2Foauth%2Fcallback&client_id=81y6xeb6x8kqyw&code=${auth_code}' "https://www.linkedin.com/oauth/v2/accessToken"

Note: Redirect URI should be encoded

• Refresh Token: NA
• $auth_code: code
• $access_token: access_token
• $refresh_token: refresh.[tT]oken
• expiry: expires_in
• $token_type: token.?[tT]ype
• access_token_usage: -H Authorization: Bearer ${access_token}
• Once all details are provided, click on the Provide Consent button

• After clicking on the Provide Consent button, it will take you the LinkedIn permssion page. Here provide consent, if consent is successful, a Access Allowed page will come up

• Go back to the REST connection page and click on the Test button. Once everthing is settled, the connection will be tested successsfully.

Access Sharepoint online REST APIs

SharePoint APIs are secured using the OAuth framework. So an access token is required to make rest calls to SharePoint APIs to pull/push data.

There are many ways to authenticate the SharePoint API:

• User Policy
• Add-In policy
• User + Add-In-Policy

In this article, we’ll how to register an Add-In on SharePoint online which will provide client id and secret. The client id and client secret will be used to authenticate the SharePoint APIs.

So Let’s go ahead and see how to register an Add-In and generate client id and secret

• .Enter below URL in the browser which will open the form to generate client id and secret

https://<sitename>.sharePoint.com/_layouts/15/appregnew.aspx

Replace the <sitename> with the actual Share Point site name

• On the App Information page enter below information and click Create button
• Client Id: Click on the Generate button which will generate the Client id
• Client Secret: Click on the Generate button which will generate the Client Secret
• Title: Any title of your choice
• App Domain: localhost
• Redirect URL: https://localhost

• Keep the client id and secret secure and Click on the Ok button

Using the above steps we have registered an Add-In, which has provided client id and secret

Below steps will be used to grant the permission to Add-In

• Enter below URL in the browser which will allow granting Add-In permission

https://<sitename>.sharepoint.com/_layouts/15/appinv.aspx

Replace the <sitename> with the actual Share Point site name

• Enter the client id in the App Id box which was generated in the earlier step and click on the Lookup button. This will search the registered Add-In and all information will be populated automatically
• Enter the below XML in the Permission Request XML text box

<AppPermissionRequests AllowAppOnlyPolicy="true">

    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Manage" />

</AppPermissionRequests>

Notice the Right attribute in which we have configured the value Manage which will help to read/write into the SharePoint site

• Click on the Create button
• Post clicking on the Create button the page will be redirected to the page where we have to Trust the add-in to manage the site

Now we have generated the client id and client secret. Let's try to call SharePoint API to create a folder on the SharePoint Site

There are three steps to call the SharePoint APIs:

• Generate the Tenant Id
• Generate the Access token
• Call the SharePoint API to create a folder

Generate the Tenant Id

• Enter below URL in the POSTMAN which is of type GET. Add Authorization header with value Bearer and hit the Submit button

https://<sitename>.sharepoint.com/_vti_bin/client.svc/

Once the request is submitted, switch the header tab of response and see the Tenant Id and resource value

Generate the Access token

• Hit below API in POSTMAN to generate an access token

URL:

https://accounts.accesscontrol.windows.net/<TenantID>/tokens/OAuth/2

HTTP Verb: POST

Headers:

Key	Syntax	Value
grant_type	client_credentials	client_credentials
client_id	ClientID@TenantID	A23dcc-313dd-2d1a-003f-11065ww1s11@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33
client_secret	Client secret	Abcde32tFg13+njytr4Khg+asgytwlkn12765nM=
resource	resource/SiteDomain@TenantID	00000003-0000-0ff1-ce00-000000000000/online.sharepoint.com@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33

Call the SharePoint API to create a folder

• Hit below API in POSTMAN to create a folder

URL:

https://<sitename>.sharepoint.com/sites/<site>/_api/web/folders

HTTP Verb: POST

Headers:

Key	Value
Authorization	Bearer <access_token>
Content-Type	application/json;odata=verbose
Accept	application/json;odata=verbose

Request Body:

{

                "__metadata":

                                {

                                                "type": "SP.Folder"

                                               

                                },

                                "ServerRelativeUrl": "/sites/<sitepath>/TestFolder"

               

}

Update Cloud Storage User password for Oracle API CS

There is a requirement where we need to update the storage cloud password in API CS. In the previous article, we showcased how to update the DBaaS Cloud storage password.

If the API CS is using the same DBaaS cloud storage the same needs to be updated in API CS also.

We'll show how to use the REST API to update the Credentials. Below is the REST service details to update the password:

URL:

https://psm-<cloud-account-name>.console.oraclecloud.com/paas/api/v1.1/instancemgmt/{identityDomainID}/services/APICS/instances/{service-name}/credentials/crednames/storagecred

Note:

cloud-account-name need to be replaced by could account name

identityDomainId: Replace by identity domain of API CS

Service-name: Replace the name of service

Headers:

Name	Value
X-ID-TENANT-NAME	< identityDomainId>
Content-Type	application/json
Authorization	Basic <username>:<password>

Username & password must be of Cloud administrator

Body:

{

                "username" : "<cloud-storage-user>",

                "password" : "<cloud- storage-password>"

}

Response:

{

    "status": "submitted job"

}

Curl command

curl -X POST \

  https://psm-<cloud-account-name>.console.oraclecloud.com/paas/api/v1.1/instancemgmt/{ identityDomainId}/services/APICS/instances/{service-name}/credentials/crednames/storagecred \

  -H 'authorization: Basic sdsdsddsdWEEWE122121cdsdsd233DFfdfdfdffdffdfd=' \

  -H 'content-type: application/json' \

    -H 'x-id-tenant-name: -<cloud-account-name>’ \

  -d '{

                "username" : "<cloud-storage-user>",

                "password" : "<cloud- storage-password> "

}'

Or

curl -v -X POST -u ‘<cloud-admin-user>:<cloud-admin-password>' -H 'X-ID-TENANT-NAME:< identityDomainId >' -H 'Content-Type:application/json' -d '{ "username" : "<cloud-storage-user>", "password" : "<cloud-storage-password>" }' https://psm-<cloud-account-name>.console.oraclecloud.com/paas/api/v1.1/instancemgmt/{identityDomainId }/services/APICS/instances/{service-name}/credentials/crednames/storagecred

OAuth Custom Two Legged Security Policy in REST connection: Oracle Integration Cloud

In this article, we will demonstrate how to make the REST connection with the API, which is secured by OAuth2.0. We will look at the OAuth Custom Two Legged security policy and how it can be used to integrate with services that are protected using OAuth Client Credentials or OAuth Resource Owner Password Credentials.

Oracle Integration Cloud provides OAuth Custom Two Legged security policy which will be used to access the API which is secured via the OAuth framework.

In OAuth2.0, the client first obtains the access token by calling the /oath API which gives the access token along with refresh token, expires_in, etc. Using the access token, the client access the actual resource to pull/push the data.

Let’s use SharePoint APIs which are enabled via the OAuth2.0.

Oracle Integration Cloud uses the curl syntax. Below is the same curl command syntax:

-X POST -H "Content-Type: application/x-www-form-urlencoded" -d 'grant_type=client_credentials&client_id= A23dcc-313dd-2d1a-003f-11065ww1s11@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33&client_secret= Abcde32tFg13+njytr4Khg+asgytwlkn12765nM= https://abc.com/tokens/OAuth/2

Refer below table to understand the different options:

Option	Possible values	Description	Mandatory
-X	GET/PUT/POST	HTTP verb to generate the access token. It may differ API to API	Yes
-H	“<Key>: <Value>”	Will be used to pass headers	No
-d	‘Data as string”	Will be used to pass data in single quotes. Any quotes in the values should be escaped	No
URI	URI	Authorization service endpoint	Yes

To generate the access token for SharePoint, below data need to send in the same format as mentioned in the table

Key	Syntax	Value
grant_type	client_credentials	client_credentials
client_id	ClientID@TenantID	A23dcc-313dd-2d1a-003f-11065ww1s11@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33
client_secret	Client secret	Abcde32tFg13+njytr4Khg+asgytwlkn12765nM=
resource	resource/SiteDomain@TenantID	00000003-0000-0ff1-ce00-000000000000/online.sharepoint.com@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33

For example:

-X POST -H "Content-Type: application/x-www-form-urlencoded" -d 'grant_type=client_credentials&client_id= A23dcc-313dd-2d1a-003f-11065ww1s11@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33&client_secret= Abcde32tFg13+njytr4Khg+asgytwlkn12765nM=&resource=00000003-0000-0ff1-ce00-000000000000/online.sharepoint.com@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33 https://accounts.accesscontrol.windows.net/tokens/OAuth/2

Let’s see how to create REST connection in Oracle Integration Cloud with OAuth Custom Two Legged security policy

• Create a REST connection with name Oauth2Legged
• Click on Configure Connectivity button, configure below and click on the Ok button
• Connection Type: REST API Based URL
• TLS version: TLSv1.2
• Connection URL:  https://online.sharepoint.com

• Click on Configure Security button, configure below and click on the Ok button
• Access Token Request: -X POST -H "Content-Type: application/x-www-form-urlencoded" -d 'grant_type=client_credentials&client_id= A23dcc-313dd-2d1a-003f-11065ww1s11@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33&client_secret= Abcde32tFg13+njytr4Khg+asgytwlkn12765nM=&resource=00000003-0000-0ff1-ce00-000000000000/online.sharepoint.com@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33 https://accounts.accesscontrol.windows.net/tokens/OAuth/2
• $access_token: access_token
• $expiry: expires_in
• $token_type: token_type
• access_token_usage: -H Authorization: ${token_type} ${access_token}

Below is the sample response of SharePoint access token request

{

    "token_type": "Bearer",

    "expires_in": "28800",

    "not_before": "1557734767",

    "expires_on": "1557763867",

    "resource": "00000003-0000-0ff1-ce00-000000000000/online.sharepoint.com@ewdvf432-hdsa-4f80-b3a0-d9e31f11fd33",

    "access_token": "esdssdsd221212sdMSDSDshjkhkjhsddsdsnkjhkjdsdng1dCI6IkhCeGw5bUFlNmd4YXZDa2NvT1UyVEhzRE5hMCIsImtpZCI6IkhCeGw5bUFlNmd4YXZDa2NvT1UyVEhzRE5hMCJ9.eyJhdWQiOiIwMDAwMDAwMy0wMDAwLTBmZjEtY2UwMC0wMDAwMDAwMDAwMDAvZ2VucGFjdG9ubGluZS5zaGFyZXBvaW50LmNvbUBiZGVmOGEyMC1hYWFjLTRmODAtYjNhMC1kOWEzMmY5OWZkMzMiLCJpc3MiOiIwMDAwMDAwMS0wMDAwLTAwMDAtYzAwMC0wMDAwMsddsddsddssdswLWIzYTAtZDlhMzJmOTlmZDMzIiwiaWF0IjoxNTU3NzM0NzY3LCJuYmYiOjE1NTMewe@32323Mssd23232Mssd2Mzg2NywiaWRlbnRpdHlwcm92aWRlciI6IjAwMDAwMDAxLTAwMDAtMDAwMC1jMDAwLTAwMDAwMDAwMDAwMEBiZGVmOGEyMC1hYWFjLTRmODAtYjNhMC1kOWEzMmY5OWZkMzMiLCJuYW1laWQiOiJmN2I0ZmQzYy0zM2ZkLTRkMGEtODAzZi1kMzA2NTRkNDA2YTZAYmRlZjhhMjAtYWFhYy00ZjgwLWIzYTAtZDlhMzJmOTlmZDMzIiwib2lkIjoiNGY5MDYxYjQtZDc2OS00MjA1LTg0YTctYjhmOGE2MjEyOWI3Iiwic3ViIjoiNGY5MDYxYjQtZDc2OS00MjA1LTg0YTctYjhmOGE2MjEyOWI3IiwidHJ1c3RlZGZvcmRlbGVnYXRpb24iOiJmYWxzZSJ9 "

}

Refer below table, while configuring the options (Configure Security) in ICS connection:

Option	Default value	Value to be configured
$access_token	access.[tT]oken	This will be the variable name which is coming in the API response which holds the value of access_token
$expiry	expires_in	This will be the variable name which is coming in the API response which holds the value of expires_in
$token_type	token.?[tT]ype	This will be the variable name which is coming in the API response which holds the value of token_type
access_token_usage	-H Authorization: ${token_type} ${access_token}	How the access token will be used to access the protected resource. For example: Authorization: Bearer <access_token>

Test the connection from the Upper right corner. If everything is OK, the connection should be successful