Integrating with FTP
One of the integration options available in DemandCaster is post to FTP.
FTP, or "File Transfer Protocol" allows customers to post their DemandCaster data to a client specific FTP folder in the cloud. From there, DemandCaster will transform and load the data into the clients DemandCaster instance.
SFTP, which stands for SSH File Transfer Protocol, or Secure File Transfer Protocol, is a separate protocol packaged with SSH that works in a similar way over a secure connection. The advantage is the ability to leverage a secure connection to transfer files and traverse the filesystem on both the local and remote system.
In almost all cases, SFTP is preferable to FTP because of its underlying security features and ability to piggy-back on an SSH connection. FTP is an insecure protocol that should only be used in limited cases or on networks you trust.
Things to Consider:
- Which files are going to be uploaded, how often, and what time of day. This is critical since the clients post to FTP is done outside of the DemandCaster environment, the transformation and load on the DemandCaster side must be timed after the client post to FTP.
- The data loaded to FTP must NOT BE compressed (zipped)
- In addition, the method to update the data must be defined. For example, the client may choose to supply an initial historical upload of three to four years of customer orders and then subsequently upload shorter time frames of history. For example, the subsequent uploads may be the last three months of history on a monthly rolling basis with each new month indexing the history up one month. We prefer this brute force means of updating historical tables since it is faster to replace a chunk of history as opposed to finding and replacing any record that has been updated from the last upload.
The FTP Folder
DemandCaster support personnel will set up the folder and will provide an address, user name, password, and port. Once setup, the following information is provided:
- User Name
Setting up the FTP Upload
The client will setup the upload process with the proper data sets based on the login information provided.
The image below uses WinSCP (https://winscp.net/eng/index.php).
The information entered is as follows:
- File Protocol: SFTP
- Host Name: ftp.demandcaster.com
- Port: 22
- User name: Enter user name provided
- Password: Enter password provided
Setting up FTP in DemandCaster (completed by DemandCaster Engineer)
- The customer updloads unzipped data to FTP on a scheduled basis to a specific folder (i.e. inbound). The files should be not be archived/zipped. The DemandCaster Data Hub app then accesses the files using an absolute file system path.
- Create new Extraction from ETL - Extractions
- From extraction type choose "File System"
- Complete all fields - Name, Description, Path, File Name
- Name / Description - not important for the system
- File Name - name it with the same name of the file, but without extension - i..e Customers.txt should be only Customers
- Path ( this is the most important field ) - this field should contain the absolute path to the ftp folder of the client. The url is constructed in the following way:
- Absolute path to the FTP folder. It is always set to \\dc-lab-ftp01\e$\ftproot\ followed by:
- The next part is the name of the ftp folder created
- And finally the path to the file in the ftp - i.e. /Inbound/Customers.txt
- So the final Path value should be - \\dc-lab-ftp01\e$\ftproot\foldername(Replace with customer specific folder name\Inbound\customers.tx
- Important: uploaded files should not be zipped.
- In the transformation step choose "No transformation"