SFTP Connector - How to use in Application Integration

[shaaland]

I've gotten some questions from customers about how to use the SFTP connector with Application Integration.  We are working on getting the documentation updated, but while that is in progress, I wanted to share some of my own notes for how to use the SFTP connector, which may be useful to you.  I've also posted a couple of sample integrations, which you can upload to your Application Integration Instance to see how I'm using the SFTP Connection for the following use case:

• Poll and SFTP Server Folder for new files (every x minutes or hours)
• For each file in the folder that matches my Filter parameter, call the Get File child integration
• The Get File child integration takes a full filename as input to the API Trigger, and then downloads the File and returns the file contents in the API Response payload
• The Parent Poll folder integration can then take the file contents and process it (this is not included in this sample).

Prerequisites:

• SFTP Connection configured and Active in GCP Integration Connectors.

Resources:

• SFTP Connector Docs
  • Sample Docs
• SFTP Connector Reference README - (Instructions for how to use the samples in Application Integration, and some reference notes at the end)
• Sample Integrations in GitHub  

Let us know what you think!

[ChiragManh]

github link needs to be updated.

[shaaland]

Thanks @ChiragManh I've fixed the link in the original post.

Docs are updated as well!  

https://cloud.google.com/integration-connectors/docs/connectors/sftp/configure#examples 

[phertzog]

Hello,

Could you provide more details on the authentication part of the connector configuration.

I deployed a FileMage SFTP server (using port 2222 for the SFTP) and created a pem file for the user. The PEM file works when I use FileZilla to connect to the server. But when I configure the sftp connector I always get an "internal error when fetching status" status on the connector.

I tried to put the content of the PEM file in the secret (with the -----BEGIN RSA PRIVATE KEY----- comment lines) and made another try without the comment lines. But in both cases the connector fails to initialize. I put the username value, let the password blank and selected PEMKEY for the type.

The FTP connector works with the same FileMage server.

Not sure if I miss something in the configuration or if it is an issue between the connector and the FileMage server. Have you experience with this configuration?

Regards

Philippe

[shaaland]

The error: "internal error when fetching status" is usually a transitory error while the connection is starting.  Did you wait for some time (30-90 seconds) to see if it cleared up after refreshing your browser?  If you are getting this error as a persistent error, then there may be an issue that engineering needs to look into.  We need your GCP Project id and Connection name to debug this, but you shouldn't post it here.  Can you send it to me in a direct message?

Meanwhile, here are some steps you can try to rule out any issues:
1) Suspend the connection, wait for it to go to Inactive status, then Resume it.  Refresh your browser to get the new status after each step.  This basically restarts the connection runtime.
2) If that doesn't work, try creating a new connection, and you can delete the one in an error state
3) If that still doesn't work and you see the same error, then we need to look at it from engineering. Enter a GCP support ticket, if you are able, or send me a private message with your project id and connection name in error.

Thanks,

Scott

[jp-stamp]

Hi @shaaland, Thanks for your suggestions. I've tried all them but I'm encountering this error. When I checked the logs, it's giving a null pointer exception at `com.google.cloud.connectors.runtime.v1.TestConnectionResult$Builder.setDescription(TestConnectionResult.java:765)`

Already tried adding a description to my connection but that didn't solve the issue (first one I did, I did not include a description so I thought that was the issue).

I'm now exploring if changing the region would give me some results.

Regards,
Jocen

[Madhuvandhini]

Kindly raise a GCP support ticket and we can look into this issue. If that's not feasible, please drop me a  private message with connection name and project ID

[Jangid]

Can we set the destination to cloud bucket?

My use case is PULL the file from On Prem SFTP Server and copy to Google Cloud Bucket, appreciate if you provide available options.

Regards,
Manoj

[shaaland]

Yes, this is definitely possible, within the product limits.  
- We currently have a payload size limit of 10MB, so the file has to be within this size limit.

Here are some posts in the community with some details for you, and there are some samples that do this same use case that you can start from.  They are ascii only right now, but to do binary, there are some details in this community post:
- Using binary mode with GCS Connector: https://www.googlecloudcommunity.com/gc/Integration-Services/Application-Integration-GCS-Connector/m...
- Using binary mode with SFTP Connector: https://cloud.google.com/integration-connectors/docs/connectors/sftp/configure 
- Sample Integration : Upload and Download to/from GCS to/from SFTP - https://www.googlecloudcommunity.com/gc/Integration-Services/Sample-Integration-Upload-and-Download-... 

[Jangid]

Thank you for your response. File size is in between 10 MB to 100 MB, might be not right solution.

can you please help me to find the alternate solution in this case?

Regards,
Manoj 

[umkothari]

Hi, I am able to download and move .csv and .txt file but but integration got failed for .xlsx and .png and other files,Can you please help me with that. and also the limit of file size we can download from SFTP and Upload to cloud storage. Please help

[Madhuvandhini]

Kindly verify if binary mode is set as true while uploading xlxs and png. If still error is faced,  share the error details with us

https://cloud.google.com/integration-connectors/docs/connectors/cloudstorage/configure

Max file size limit is 10 MB while passing as payload in Integration

[jwhitehorne]

Is it possible to create a new folder in using the integration? I have been trying to create the folder but the integration pipeline succeeds but the folder does not get created.

[avindia]

Can I use SFTP Connector to regular transfer files on GCS Storage Bucket in my GCP Project to Third Party SFTP Server ?

[Madhuvandhini]

To transfer from GCS to SFTP, below steps can be followed

1)GCS connector can be used to download file

https://cloud.google.com/integration-connectors/docs/connectors/cloudstorage/configure#dwld

2)Use SFTP connector to upload file using upload action

https://cloud.google.com/integration-connectors/docs/connectors/sftp/configure#actions-exp

[avindia]

Thanks for your swift response on this. Solution proposed helps 

[simonmartyr73]

Hi everyone,

I am looking at a specific use case. 

Download files from an on-premise SFTP server (<2Mb total size), on a schedule/trigger using the SFTP integration connector.

Access to the on-premise server is only possible privately via a Google Cloud interconnect and Shared VPC model.

The SFTP server should be reachable via its FQDN.

The current Google documentation for this is not centralised and ambiguous.  It mentions needing service directory and DNS peering to be able to resolve the FQDN of target resource (I follow this).

However, to enable private connectivity from an integration connector to a target, you'll need a PSC service attachment and endpoint interface (Which I get).

However, the examples given only list simple solutions for Google Cloud targets e.g. Cloud SQL or self-managed VM in a GCP project, not for an on premise target.

How would I enable this connectivity where the target is a on-premise resource, such as an on premise SFTP server?  I have an idea of how to do it, but I'd expect a centralised, good end-to-end example of how to connect to a private private that sits outside of Google Cloud.

[shaaland]

I apologize for the delayed answer!

Please see the documentation here and here for how to set up private network connectivity using Private Service Connect (PSC).

Once you have connectivity to your Endpoint Attachment, the GCP network layer with your Interconnect or VPN will allow you to connect into your on-premises SFTP server. Note that you may need a Load Balancer and Hybrid NEG to establish the route.