Step by step procedure to establish CI-DS to SFTP File server connection
I am super excited to share this blog with you all as this is my first blog. I have been working in the Cloud Integration technology for many years now and I want to share that knowledge I gained all these years with you all. I hope you will enjoy this blog!
Cloud Integration for Data Services or CI-DS (previously called as Hana Cloud Integration for Data Services (HCI-DS), then renamed to Cloud Platform Integration for Data Services (CPI-DS) and now called as CI-DS, so will be referred as CI-DS in this blog) offer multiple connectivity options to connect with different Source systems in an integration landscape. However, if the Source systems are not compatible with CI-DS, we go with the flat file approach as input to CI-DS interfaces where the On-premise system is non-SAP or the direct connectivity is not possible. Here we have options to place the input Flat files in the SAP Data Services (DS) Agent server as well, but some Customer has or wants a dedicated files server to place these files so that maintenance is easy and secured. We have few options like File Transfer Protocol (FTP), Secure File Transfer Protocol (SFTP), Azure Cloud Storage and Azure Data Lake Storage to achieve this. Here in this blog, I am explaining about the steps to configure the SFTP server connectivity so that it can be used as both a Source and Target. Here we are not using the conventional way to create the Flat File datastore and enable SFTP which is not supported anymore (refer SAP Note 3159237), but using the new ‘File Location’ datastore type. Since this is a new method of creating the datastore, I want to clear all the confusions and questions from your mind and that is the motive behind this detailed blog.
The initial requirement is to have the SFTP Server details and a valid credential to access the Server. Then we need the Host Public Key Fingerprint also. Next thing is having the root level path to the Folder in which you create your own folders. All these can be provided by the Basis team or the IT Support team who supports the SFTP server.
The Data Services Agent should be installed and configured and it should be in Active status. A folder should be created in the Agent server and added in the whitelisted Directories.
When we think about creating a datastore for files, we select the ‘File Format Group’ and define the file structures in the table. There is an option also to enable the FTP/SFTP connections in this datastore. However, to create the SFTP enabled datastore, SAP has provided a new datastore type in the recent releases called ‘File Location’.
So instead of selecting ‘File Format Group’, select ‘File Location’ and proceed with the datastore creation.
Enter the below details.
- Enter the name for the Datastore
- Select the Agent
- Select the Protocol as ‘sftp’
- Enter the Host name
- Enter the Host Public Key Fingerprint
- Select the Authorization Type as ‘Password’
- Enter the Username and Password
- Enter the Local Directory folder path from the Data Services Agent Server
- Enter the folder path where the files are placed from the root level (make sure to use the correct forward/backslash as this is different for Windows and Linux)
Now click on the Save button to create the datastore. Test the connection and make sure it is successful.
If there is an error on the Host Public Key Fingerprint, there is a way to find the correct one. Just remove it from the Connection details and save the datastore, then test the connection. In the error message, it will show the expected Host Public Key Fingerprint. Use this in the connection details and test again.
Now we have established the SFTP connection. But in this datastore, we don’t have the option to create the File Formats. So let’s go create another datastore to define that.
This time, select ‘File Format Group’ and proceed with the datastore creation. Enter the necessary details.
- Enter the name for the Datastore
- Select the Agent
- From the Location dropdown, select the File Location Datastore we have created in the previous step
- Save the datastore and test the connection.
- Create the File Formats under the ‘File Formats’ section.
Bingo! We have successfully created the SFTP datastore and we can use this as a Source as well as a Target.
Please check my other blog here to learn how to archive the files from SFTP server.
Advantages and disadvantages:
We must create two datastores to establish the SFTP connection. It provides a dedicated file server and maintenance will be easy. There is separation between files and the Data Services Agent server if Customer has concerns about the security. But during the execution, CI-DS picks the file and move it to the Local Data Services Agent folder to process it and a checkbox is provided in the dataflow to delete the file from local Data Services Agent folder once the execution is over. The disadvantage lists the design time view is not supported, so won’t be available during the development. So, if mapping is not straight, you might need to create another task which is sourced from the Data Services Agent folder to test your logic is working fine, then export/import this to SFTP datastore. Secondly, Lookup will not be supported from SFTP folder, so we have to use the Local Data Services Agent folder to place the lookup files.
After reading this blog, you will be able to create an SFTP datastore and successfully implement this in your integration. Hope this helps you understand the process to create SFTP datastore! Please share your thoughts, questions and feedback on this topic and follow my profile for exciting blogs on Cloud Integration for Data Services.