connect to an internal sftp server from outside private vpc (on prem)

Can you please take a look at this AWS Premium Support article - https://aws.amazon.com/premiumsupport/knowledge-center/aws-sftp-endpoint-type/

Your scenario comes under the 2nd column - Amazon Virtual Private Cloud (Amazon VPC) endpoint with internal access

As you can see on the "Access" row, it mentions the following "From within VPC and VPC-connected environments, such as an on-premises data center over AWS Direct Connect or VPN". This implies that with your configuration, you should be able to connect to the SFTP server using private IP addresses, as long as a network path has been set-up from your corporate network to the AWS VPC using either DirectConnect or IPSec VPN.

You have mentioned that you don't see any Custom Hostname and Endpoint in your AWS Transfer Family server configuration. However, you should see private IP addresses for your SFTP server created under the Endpoint Configuration section.

As per the recommendation of the above referenced Support article, "Use a Network Load Balancer in front of a VPC endpoint with internal access. Change the listener port on the load balancer from port 22 to a different port. This can reduce, but not eliminate, the risk of port scanners and bots probing your server, because port 22 is most commonly used for scanning. However, if you use a Network Load Balancer, you can't use security groups to allow access from source IP addresses."

You should be able to use clients such as Filezilla to FTP the files from your on-prem machines to the SFTP Server in AWS. For a list of supported clients take a look at this - https://docs.aws.amazon.com/transfer/latest/userguide/transfer-file.html