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
Insert into SAP_CPI Values ('0000000001','This is data from SAP CPI'
Case 4: Insert data ok
Case 5 : Delete data
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.