Recently, I had a problem to solve for a customer. I was surprised at how many different skills I had to use just to accomplish what seemed to be a simple task. I needed to get a file from one server to another as part of a SQL Server Agent job. The only means available to transfer between the two servers was FTP. FTP is an old protocol, something that I rarely need to work with, and I don’t believe I have ever had to automate transferring a file where using SSIS was not an option.

In a SQL Server Agent job step, you have some options for command line work. You can change the job step to the “Operating system (CmdExec)” type. This type of job step will only run one line and ignore the rest if you have added multiple lines. It is possible to concatenate multiple lines of code with the && symbols. I needed to run an FTP script that included a username and password. Concatenating FTP script lines didn’t work, but I found that I could launch an existing FTP batch file from a CmdExec step with this command:
FTP -s:<batch file path>

Another idea that I had was using PowerShell. PowerShell is the current standard when it comes to scripting in Windows, and there is also a PowerShell job step type in SQL Server Agent jobs. I found that I could launch FTP from PowerShell, but once FTP is launched, it becomes an FTP session. My hope was to just list all my FTP commands in the PowerShell job step, but it treats each command as a PowerShell command. The job step creates an FTP session, but further FTP commands error out.

Even though I didn’t want to create an FTP batch file, I was running out of ideas and time. I decided to use the CmdExec job step with concatenated lines to create the file right when it was needed, and then delete it when it was no longer required. I tried using the ECHO command to redirect the required commands to a file (> to create the file and >> to append to it). The file looked correct, but what I didn’t realize is that ECHO adds an extra space to the end of each line. Those extra spaces caused the username and password to be incorrect. OK, back to PowerShell. I tried the Set-Content and Add-Content commands to create my FTP batch file. I also ended up adding the -Encoding ASCII option to ensure that the file would be compatible with FTP. This time, the file worked!

Even if working with SQL Server is your job, it’s important to be able to do things like work with PowerShell and even DOS, and to learn about the operating system. It’s really important to make Bing or Google your best friend and to develop good troubleshooting techniques. What seemed like a simple task took me a couple of hours to get right. Of course, while writing this, I found that I may have been able to use the FTP URL instead of a batch file. If I get a chance to revisit this, I’ll try that method instead.