0

The goal is for an SSMS job to transfer a single file from an SFTP to a local drive. It will work off a server so it should have little-to-no 3rd party requirements.

PowerShell 5 has native FTP functionality so I want to work with that. This should be simple but I'm finding very little documentation or working examples on Google. The example below is derived from this SO post. When run, 'The URI prefix is not recognized' error is returned.

$source = 'ftp.companyname.com:/remotetestfolder/TestBlankFile.xlsx'
$target = 'C:\Users\....\...\Desktop\TestBlankFile2.xlsx'
$Username = 'myusername'
$password = Microsoft.PowerShell.Security\ConvertTo-SecureString -String 'mypassword' -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username, $password

# Download
Invoke-WebRequest -Uri $source -OutFile $target -Credential $credential -UseBasicParsing

Should I create separate variables for the remote directory and filename? Where do they go in?

Update: Right now, I manually retrieve files using the steps below. My goal is to automate these steps (preferably with only native Windows commands and processes)

  1. Open Filezilla.
  2. If a connection isn't already saved, create a connection to SFTP.companyname.com, add my username & password, and whatever other changes are needed.
  3. Click to connect to the FTP and then download the files I need.
PowerUser
  • 11,583
  • 20
  • 64
  • 98
  • There is no need to use FTP. The best way is to connect to the SQL Server directly and then output the results to a local file. You should be able to connect to SQL from a remote machine using Invoke-SqlCmd – jdweng Mar 16 '23 at 10:45
  • @jdweng The file transfer is from an SFTP to a network drive. The SSMS only holds the scheduled job. Can a SQL script pull down a file from an SFTP? – PowerUser Mar 16 '23 at 17:02
  • SFTP is Secure FTP files transfer. A SQL script is usually a stored procedure on a SQL Server. Yes a Powershell script can run a stored procedure on a SQL Server. You last question is very confusing. – jdweng Mar 16 '23 at 17:08
  • @jdweng, Sorry, let me rephrase. I suppose I don't understand your original suggestion. I'm trying to pull a file from an SFTP. How can Invoke-SqlCmd help me do that? – PowerUser Mar 16 '23 at 22:39
  • SFTP (Secure version of FTP) is a protocol for downloading files. So you have a service running on a remote machine that you can download files. SSMS is SQL Server Management Studio which is a database. SFTP and SSMS are completely different type of services. One is for files transfer and the other is for databases. You do not need SFTP. You can get your data directly from the SSMS. – jdweng Mar 17 '23 at 04:38
  • @jdweng , This is interesting how professionals can define things differently. When I say SFTP, I'm referring to the actual site/URL I go to where the files are stored, i.e. SFTP.companyname.com. Also, SSMS is not the actual database, but a collection of tools to interact with databases (mainly SQL Server which is a different product). I do not have direct access to this company's servers so I'm going thru their FTP service. Please see my update to my original post. – PowerUser Mar 17 '23 at 19:43
  • This makes no sense "The goal is for an SSMS job to transfer a single file from an SFTP to a local drive." Why would anybody use a database tool to transfer a file between two locations not in the database? – jdweng Mar 17 '23 at 20:30
  • @jdweng, it's convenient. SSMS jobs are easily scheduled & can run powershell commands. Also, all our other scheduled tasks are done the same way. Can you recommend an alternative? – PowerUser Mar 21 '23 at 18:33
  • A schedule task is just as easy : https://www.windowscentral.com/how-create-automated-task-using-task-scheduler-windows-10 – jdweng Mar 21 '23 at 18:51
  • Does this help? https://stackoverflow.com/questions/46660545/download-files-from-sftp-server-using-powershell – Patrick Mcvay Apr 18 '23 at 17:05
  • My understanding is that powershell does not support sftp. Which is probably because the Invoke-WebRequest uses the same backend as your browser and as far as I know browsers only support ftp not sftp. – Patrick Mcvay Apr 18 '23 at 17:07

0 Answers0