[SAP CPI] – HOW TO CONNECT SAP CPI TO SQL SERVER ON PREMISE USING CLOUD CONNECTOR

Hi guys, in this article I want to share How to connect SAP CPI to SQL Server on premise using cloud connector. For testing, we will use POSTMAN send XML file and insert data to SQL Server. This scenario use for in cases integration data from SAP with direct SQL Server. Let’s go.

Step 1 : SAP Cloud Connector Configuration

Add New TCP Connection to host with Port 1433 ( Port SQL is 1433)

Step 2 : Download data source configure

Step 2.1 : Download SQL Server driver

Click here to download SQL server driver and extract file

Step 2.2 : Upload/Deploy driver to SAP CPI

• Go to Integration Suite Application
• Click Monitor on left menu
• Below section Manage Security click JDBC Material

• Choose tab JDBC driver, click button Add

• Choose file Jre.jar in folder driver download in step 2.1. Then click deploy

• After deploy successful, we have

Step 2.3 : Create the JDBC Data source

• Go to Tab JDBC Data Source, Click Add

• for JDBC URL, it will be syntax : jdbc:sqlserver://<ServerName>:<Port>; Database =xxx

• Deploy it.

Step 3 : iFlow Configuration

This iFlow will have one sender adapter HTTPS, one receiver adapter is JDBC connection. When we input SQL Command on POSTMAN ( Ex : Select * from xxxx) We will have data from SQL. The same way, we can write SQL Command INSERT or UPDATE as well.

JDBC Data Source Alias is Name field in step 2.3

Step 4 : Test case

Because of using JDBC adapter with SQL Driver, so body request from client (POSTMAN) must be text with SQL Syntax.

Case 1 : Node name cannot empty

Body request	Select 1
Data Result	An internal server error occured: The MPL ID for the failed message is : xxx
Log	com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: com.sap.it.rt.adapter.jdbc.exceptions.JDBCException: Error occurred while constructing the response nodes. : org.xml.sax.SAXException: Nodename cannot be empty.. The MPL ID for the failed message is : xxx
Cause	SQL Command, no column is defined.

Case 2 : Select OK

Request : Select A=1, B=2, C=3

Case 3 : Insert data with incorrect syntax SQL

Request

Insert into SAP_CPI Values ('0000000001','This is data from SAP CPI'

Response log

Case 4: Insert data ok

Case 5 : Delete data

Summary

In this article, I shared How to execute SQL script from SAP CPI to SQL Server on premise using SAP Cloud Connector. This is use for cases connect direct from SAP CPI to SQL Server, no need File server as SFTP.

But, I’m not recommend we use this way to integration data from SAP/ERP to third party. Because If SQL server get bad performance with any reason, It affect performance of CPI as well.

Thanks for you reading and any question kindly leave your comment on below this.

Thanks.

Joseph.