IICS: File Processor Connector - Transferring Files between a Local File System and an FTP or SFTP Server

The file processor connector is used to securely transfer files from one location to another, regardless of size and location.

We can use File processor connectors for file processing operations, such as transferring, archiving, unarchiving, encryption, decrypting, compressing, decompressing, moving or copying files. 

The File Processor Connector is not a native connector, to add a file connector go to Administrator Add-on-Connection  and search for "File Processor Connector" and click start a free trial for 30 days trial 

Confirm the license  menu 

We must create a File Processor connection to create tasks to process files. 

Go to Administrator->Connection to create File Processor Connection use Type: FileProcessor

You can configure a connection on the Connections page or in a wizard as you configure a task.

On the 
Connections
 page, click 
New Connection
 to create a connection.
The 
New Connection
 page appears.
The following image shows the New Connection page details:

1. 
Configure the following connection properties on the New Connection page:
Connection Property	Description
Runtime Environment	The name of the runtime environment where you want to run the tasks.
Source File Directory	The location that contains files you want to transfer.
Target File Directory	The location where you want to place the transferred files.
Select File	The files that you want to transfer. You can select files based on the fields.
File Pattern	The pattern of the files that you want to transfer. For Example, if you want to select a file based on a date pattern, you can specify the date format as DD/MM/YYYY in the file pattern field. Note: The file Pattern field is not applicable when you select  all  from the  Select File  connection property.
Days Calculation	Use days calculation to select files that are created or modified before the specified date or after the specified date. Select files based on Contains Date Pattern, and specify the  day's calculation  value so that you can select files that are modified before or after the specified date. Specify the value in terms of days. You cannot specify the value in terms of month and year. For example, if you select a file based on Contains Date Pattern, use the data filters to specify LastModDate as 02/02/2016 in DD/MM/YYYY format, and specify days calculation as -1. Files that are modified till 01/02/2016 are selected.
PassKey	The credentials to connect to FTP or SFTP server. For example, you can specify the password and passphrase of the FTP or SFTP server as passkey1 and passkey2 values.
Click 
Save
 to save the connection.
You should test the connection before you save the connection details. You can click 
Test Connection
 to verify if the connection is successful. 

Selection of Specific Files

When you perform a File Processor operation, you can select a single file or multiple files. The files are selected based on the fields that you configure from the Select File connection property. The following table describes the Select File fields to select a specific file or set of specific files.

Fields	Description
All	Selects all the files from the source directory.
Equals	Selects the files that are equal to the specified name in the file pattern property. For Example, if you specify sample.docx, the sample.docx file is selected.
Notequals	Selects all the files other than the file pattern that you specify. For example, if you specify, the file pattern as sample.txt, the sample.txt file is not selected.
Startswith	Selects files that start with the pattern that you specify. For example, if you specify the StartsWith value as a sample, all the files that start with the name sample are selected.
Endswith	Selects files that end with the pattern that you specify. For example, if you specify the EndsWith pattern as .txt, all the files that end with .txt are selected.
FileExtension	Selects the files that contain the specified file name extension. For example, if you specify the values as .txt in the file pattern, all the .txt files are selected.
Contains	Selects files that contain the specified pattern. For example, if you specify the contains pattern as a sample, all the files that contain sample in the file name are selected.
FileSizeGreaterThan	Selects files greater than the size specified in the file pattern. Specify the file size in KB. For example, if you specify the file size as 30, all the files greater than 30 sizes are selected.
FileSizeLessThan	Selects files that are less than the specified size. Specify the file size in KB. For example, if you specify the file size as 30, all the files with less than 30 sizes are selected.
FileSizeEqualsto	Selects files equal to the specified size. Specify the file size in KB. For example, if you specify file size as 30, all the files with 30 sizes are selected.
FileSizeNotEqualsto	Selects files not equal to the specified size. Specify the file size in KB. For example, if you specify file size as 30, all the files with a size that is not equal to 30 sizes are selected.
Starts with DatePattern	Selects files that start with the specified date pattern. For Example, if you specify the file pattern as DD/MM/YYYY, all the files that start with DD/MM/YYYY date pattern are selected.
EndsWithDatePattern	Selects file that ends with a specified date pattern. If you specify the file pattern as DD/MM/YYYY, all the files that end with DD/MM/YYYY date pattern are selected.
Contains Date Pattern	Selects files that contain the specified date pattern. For example, if you specify the file pattern as DD/MM/YYYY, all the files that contain DD/MM/YYYY date pattern are selected.

Transferring Files between a Local File System and an FTP or SFTP Server

Use File Processor Connector to transfer files from a local system to an FTP or SFTP server or from an FTP or SFTP server to a local file system. You can use the FTP_PUT and SFTP_PUT objects to upload files from a local system to an FTP or SFTP server. Use the FTP_GET and SFTP_GET objects to download files from an FTP or SFTP location to a local file system.

You are a website administrator and want to upload files from a local file system to an SFTP server. In this example, you can specify the hostname, password, and UserID as data filters.

Perform the following steps to create a 

synchronization

 task to upload files from a local file system to an SFTP server:

In 
Data Integration
, click 
New
Tasks
.
Select 
Synchronization Tasks
, and click 
Create
 to create a synchronization task.
The 
Definition
 tab appears.
Configure the following fields on the 
Definition
 tab:
Field	Description
Task Name	Name of the  synchronization  task. For example File_Process_SFTP_PUT
Description	Description of the  synchronization  task. Maximum length is 255 characters.
Task Operation	Select Insert.
Click 
Next
.
The 
Source
 tab appears.
Configure the following fields on the 
Source
 tab:
Field	Description
Connection	Select the connection you created. For example File_Process_SFTP_PUT
Source Type	Select Single.
Source Object	Select SFTP_PUT.
Click 
Next
.
The 
Target
 tab appears.
Configure the following fields on the 
Target
 tab:
Field	Description
Connection	Select the flat file connection.
Target Object	Click Create Target.
The target file displays the status of the files that are transferred.
Click 
Next
.
The 
Data Filters
 tab appears.
Select the filter object, filter field, and filter operator to create a data filter on the 
Data Filters
 page.
The following image shows the 
Data Filters
 page:

When you perform an SFTP_GET, SFTP_PUT, FTP_GET, and FTP_PUT operation, you must specify values to the hostname, password, and userID filter fields to connect to the FTP or SFTP server.
Click 
Next
.
The 
Field Mapping
 tab appears.
Click 
Automatch
 on the 
Field Mapping
 tab to map source fields to target fields accordingly.
Click 
Validate Mapping
 to validate the mapping.
Click 
Save
 and then 
Finish
.
From the 
Explore
 page, select the task and click 
Actions
Run
.
In 
Monitor
, you can monitor the status of the logs after you run the task.

SQL Queries on Branch, Customer, Loan, borrower, account & depositor

 

Q) Consider following database schema, Write the queries using SQL

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)

loan (loan_number, branch_name, amount)

borrower (customer_name, loan_number)

account (account_number, branch_name, balance)

depositor (customer_name, account_number)

  

-- a)        Find all customers who have both a loan and an account (eliminate duplicates if exists)

SELECT distinct customer_name from borrower

WHERE customer_name IN (SELECT customer_name FROM depositor );

 

-- b)        Find all customers who have a loan or an account or both (eliminate duplicates if exists)

SELECT customer_name FROM borrower

UNION 

SELECT customer_name FROM depositor;

 

-- c) Find all customers who have a loan but not an account (eliminate duplicates if exists)

SELECT DISTINCT customer_name FROM borrower

WHERE customer_name NOT IN (SELECT customer_name FROM depositor );

 

-- d)        Find all customer who have a loan or an account or both

SELECT customer_name FROM borrower

UNION ALL

SELECT customer_name FROM depositor;

 

-- e)        Find all customer who have both a loan and an account

SELECT customer_name FROM borrower

WHERE customer_name IN (SELECT customer_name FROM depositor);

----Demonstration

use databasename;

create table BRANCH (

branch_name varchar(20) primary key,

branch_city varchar(20),

assets int

);

create table CUSTOMER (

customer_name varchar(20) primary key,

customer_street varchar(20), 

customer_city varchar(20)

);

create table ACCOUNT (

account_number int primary key, 

branch_name varchar(20), 

amount int,

foreign key (branch_name) references branch(branch_name)

);

create table LOAN (

loan_number int primary key,

branch_name varchar(20), 

amount int,

foreign key (branch_name) references branch(branch_name)

);

create table DEPOSITOR (

customer_name varchar(20),

account_number int,

primary key(customer_name, account_number),

foreign key (customer_name) references customer(customer_name),

foreign key (account_number) references account(account_number)

);

create table BORROWER (

customer_name varchar(20),

loan_number int,

primary key(customer_name, loan_number),

foreign key (customer_name) references customer(customer_name),

foreign key (loan_number) references loan(loan_number)

);

create table EMPLOYEE (

employee_name varchar(20), 

branch_name varchar(20), 

salary int,

primary key(employee_name, branch_name),

foreign key (branch_name) references branch(branch_name)

);

INSERT INTO BRANCH VALUES 

("JP Nagar","Bangalore",1000),

("Charchgate","Mumbai",1500),

("Red Hill","Hyderabad",2000),

("Jaya Nagar","Bangalore",5000);

 

INSERT INTO CUSTOMER VALUES

("c1","s1","Bangalore"),

("c2","s2","Hyderabad"),

("c3","s3","Mumbai"),

("c4","s4","Kolkatta");

INSERT INTO ACCOUNT VALUES

(101,"JP NAGAR",5000),

(102,"Red Hill",3000),

(103,"Charchgate",2000);

INSERT INTO LOAN VALUES

(1,"JP NAGAR",3000),

(2,"Charchgate",1000),

(3,"Red Hill",5000),

(4,"Jaya Nagar",6000);

INSERT INTO DEPOSITOR VALUES

("c1",101),

("c3",103);

INSERT INTO BORROWER VALUES

("c1",1),

("c4",4);

-- Q) Consider following database schema, Write the queries using SQL

-- branch (branch_name, branch_city, assets)

-- customer (customer_name, customer_street, customer_city)

-- loan (loan_number, branch_name, amount)

-- borrower (customer_name, loan_number)

-- account (account_number, branch_name, balance)

-- depositor (customer_name, account_number)

-- a) Find all customers who have both a loan and an account (eliminate duplicates if exists)

SELECT distinct customer_name from borrower 

WHERE customer_name IN (SELECT customer_name FROM depositor );

-- b) Find all customers who have a loan or an account or both (eliminate duplicates if exists)

SELECT customer_name FROM borrower 

UNION  

SELECT customer_name FROM depositor;

-- c) Find all customers who have a loan but not an account (eliminate duplicates if exists)

SELECT DISTINCT customer_name FROM borrower 

WHERE customer_name NOT IN (SELECT customer_name FROM depositor );

-- d) Find all customer who have a loan or an account or both

SELECT customer_name FROM borrower 

UNION ALL 

SELECT customer_name FROM depositor;

-- e) Find all customer who have both a loan and an account

SELECT customer_name FROM borrower 

WHERE customer_name IN (SELECT customer_name FROM depositor );

SFTP : Windows Open SSH Server, Connecting through Linux Client Using Public Key

SFTP (SSH File Transfer Protocol) is a secure file transfer protocol runs over the SSH protocol. It supports the full security and authentication functionality of SSH.

SFTP has replaced legacy FTP as a file transfer protocol provides all the functionality with security and reliability.

SFTP also protects against password sniffing and man-in-the-middle attacks. It protects the integrity of the data using encryption and cryptographic hash functions, and authenticates both the server and the user.

OpenSSH Installation using .zip file

• Goto https://github.com/PowerShell/Win32-OpenSSH/releases
• Download the OpenSSH-Win64.zip file from the latest release
• Extract the zip file contents to the folder C:\Program Files\OpenSSH

• Open a command prompt as Administrator and use the following command to change to openssh directory
• cd "C:\Program Files\OpenSSH"
• Run the following command 

    powershell.exe -ExecutionPolicy Bypass -File install-sshd.ps1

• Now the sshd and ssh-agent windows services should be installed. This can be seen in the services.msc window
• Change the startup type to Automatic from Manual and start both the services. Since we have set the startup type as automatic, both the services will start automatically upon system startup.

• Create the appropriate firewall policy to expose the SFTP port 22 to local or remote systems if required
• Now SFTP server accepts connections using username and password authentication

Setup SFTP server in newer versions of windows

• Click windows button and search for “manage optional features”
• Click on “add a feature” and search for OpenSSH server and install it
• Now Open SSH server and OpenSSH Authentication agent services should be installed in the services.msc window
• You ca right click and change the start up type of both the services as automatic if you want the services to start upon system start up
• Create the appropriate firewall policy to expose the SFTP port 22 to local or remote systems if required
• Now SFTP server accepts connections using username and password authentication

Downsides of password based authentication in SFTP

• OS user credentials of the server operating system are to be shared with the SFTP client which is not desirable
• OS user password is to be changed to change the password of SFTP client
• OS user password will be transmitted over the network

Benefits of using public key based authentication in SFTP

• This type of authentication is more robust and secure
• SFTP client need not know the OS user password
• Multiple clients can use different private keys for a single OS user
• Private key can be changed easily from time to time without changing the user’s OS password
• Access of SFTP client can be easily revoked by just removing the client’s public key from the authenticated list, without locking out or modifying the OS user account

Setup public key based authentication in Linux

• Open a terminal window and run ssh-keygen command. Press enter till the execution is complete as shown in the image below

• During the key generation process, password protection can be set to private key by entering a passphrase as shown in the above image. This ensures additional protection in case the private key is in wrong hands
• The public key will be saved as /home/<username>/.ssh/id_rsa.pub (home directory of user and /root for root directory)  and private key will be saved as /home/<username>/.ssh/id_rsa (home directory of user and /root for root directory)

Place the public key in the SFTP server

• Use following scp (secure copy) command to copy the public key in the server directory (The default directory is C:/ProgramDate/ssh) as shown in the screenshot
• scp .ssh/id_rsa.pub  <username>@<sftp server ip or hostname>:/C:/ProgramData/scp  

Copying Public Key in authorized_key

• Method 1 : If you have direct access to Windows Server, than open command prompt go to C:/ProgramData/ssh and use type command to copy/append the public key in administrators_authorized_key

• Method 2: 
• If you don't have direct access to Windows Server than Login to server with SSH  from Linux as shown below to copy the public key in administrators_authorized_key as shown bellow. 

• After Entering the Password it will show the windows login as shown below screen shot

• Goto C:/ProgramData/ssh directory and enter following command as shown in the screenshot

Change the Permission of the administrators_authorized_keys file using following command directly from 

• Use following command to allow the access permission of administrators_authorized_key to only administrator and system users of windows operating system. 

icacls administrators_authorized_keys /inheritance:r /grant "Administrators:F" /grant "SYSTEM:F"

Enabling Public Key authentication 

• Goto Server C:/ProgramData/ssh folder open sshd_config file in notepad in admin mode and uncomment pubkeyAuthentication yes line and save the file. 

 

• Restart the Openssh services and try to login without password from the Linux box

• Now you are able to login have complete access on User Home directory 

• If you want to give access to specific folder, add following line at the of  sshd_config  file and restart the service.
• In this demonstration I have used SFTP_Root folder present in user home directory.

Match User <username>

ChrootDirectory ~/<directoryname>

X11Forwarding no

AllowTcpForwarding no

PermitTTY no

ForceCommand internal-sftp

PasswordAuthentication no

• It will give access SFTP_Root folder only

How to get the File from the server 

Method 1: Login to the sftp server and use get command to get the file as shown in the screenshot

Method 2: Directly download the file from the command prompt using sftp command as shown in the screenshot bellow

Thanks for reading this blog.