SFTP to DB (Multiple line items)

In this blog, I am going to explain how to retrieve the data/payload from SFTP and send it to DB (PostgreSQL).

Step 1:

Establish connectivity with SFTP

Get the SFTP server, port, user name, password from the infra team / client team

In my example, 

Location: demo.wftpserver.com

Username: demo

Password: demo

FTP Port: 21

FTPS Port: 990

SFTP Port: 2222

Schedule it as per the requirement...

Time Zone is very important, if you want to schedule on particular time.

Step 2:

Establish connectivity with DB (PostgreSQL)

Refer my blog - how to establish the connectivity and sample queries..

JDBC - blog

Sender Side:

Employee XML

<?xml version="1.0"?>

<emprecords>

  <emprecord>

    <empid>4001</empid>

    <emplname>Paul</emplname>

    <empfname>David</empfname>

    <empage>20</empage>

  </emprecord>

  <emprecord>

    <empid>4002</empid>

    <emplname>PP</emplname>

    <empfname>Ramesh</empfname>

    <empage>20</empage>

  </emprecord>

  <emprecord>

    <empid>4003</empid>

    <emplname>DD</emplname>

    <empfname>Kiran</empfname>

    <empage>20</empage>

  </emprecord>

</emprecords>

Employee XSD

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="emprecords">

    <xs:complexType>

      <xs:sequence>

        <xs:element maxOccurs="unbounded" name="emprecord">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="empid" type="xs:unsignedShort" />

              <xs:element name="emplname" type="xs:string" />

  <xs:element name="empfname" type="xs:string" />

              <xs:element name="empage" type="xs:unsignedByte" />

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

Receiver Side:

Receiver JDBC - XSD

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="root">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="StatementName">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="dbTableName">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element name="table" type="xs:string" />

                    <xs:element maxOccurs="unbounded" name="access">

                      <xs:complexType>

                        <xs:sequence>

                          <xs:element name="empid" type="xs:unsignedByte" />

                          <xs:element name="emplname" type="xs:string" />

                          <xs:element name="empfname" type="xs:string" />

                          <xs:element name="empage" type="xs:unsignedByte" />

                        </xs:sequence>

                      </xs:complexType>

                    </xs:element>

                  </xs:sequence>

                  <xs:attribute name="action" type="xs:string" use="required" />

                </xs:complexType>

              </xs:element>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

Message Mapping:

Upload sender side xml XSD as source payload structure and Upload target side xml XSD as per target accepted structure...

Note: DB action should be specified like INSERT, UPDATE or DELETE..

Now, deploy the iflow and test it.

Note: Link all the steps and double check the connection parameters and ensure that the source and target systems are up and running, else you may encounter connectivity related exceptions.

Thanks for reading :-)