Adding SFTP functionality to your SSIS packages

Hi folks! This one’s not at all Centricity-centric, but it involves a task you may sometimes need to perform as part of your work with patient data. Let’s say your organization is part of an ACO, and that ACO requires regular uploads of data. In most cases, they’re going to want you to upload that data via SFTP. If the data is due monthly, you could run your script on the first of the month, export the data, then upload it manually via the FTP application of your choice. But what if you’re on vacation? What if you forget? What if the data needs to be uploaded daily? Are you really going to do this manual upload every single day? You could, but why would you want to? The more efficient method is to automate it via an SSIS package.

(Note: I’m going to assume you understand the basics of building a SQL Server Integration Services package. If you don’t, you really should learn; it’s an invaluable skill, and it’s a very intuitive tool. Keep reading and maybe you’ll pick up a few ideas!)

The problem is, SSIS doesn’t support SFTP out of the box. There’s an FTP Task in the toolbox, but no secure version. Your organization can purchase an SFTP add-on from a number of vendors, but they tend to be very expensive. I’m going to show you how to do it for free, and I’m going to hold your hand through every step of the process (like I wish someone had done for me).

Step 1: Download and install WinSCP
We’re going to use a free software package called WinSCP, and leverage its built-in command line utility. Click that link and download the software. Even if you never use this method, it’s a great FTP client. Install the software on both your development machine and the production machine.

Step 2: Create a log folder on both machines
After you’ve installed WinSCP, go into the application directory (should be C:\Program Files (x86)\WinSCP) and create a folder called “log”. This is where your SFTP log files will be generated. If you plan on running this package as a SQL Agent job, make sure your SQL Agent user account has full read/write access to the WinSCP folder.

Step 3: Create your SSIS package
I’m not going to walk you through this part, as it can take any number of forms. Essentially you want to generate your data and output it to the file format of your choice. It could be an Excel destination, or a flat file destination, or whatever. For the purposes of this post, we’re interested in what happens after the file has been created.

Step 4: Create a variable to hold the file name
In your SSIS package, create a variable that will represent the name of the file you generated. You usually need to do this in a variable, as most organizations will require some sort of date stamp in the file name. For the sake of this post, let’s assume you named the variable FileName.

NOTE: If you have to upload multiple files, you can go three different ways. You can set up a Foreach Loop Container and perform your SFTP push on each file individually. You can set up a different FileName variable for each file. Or you can use a wildcard (like *.txt) in the FileName variable. If you’re going with the wildcard option, just be sure that only the files in your source folder that you want to upload match that pattern. You may want to add a File System Task to the start of your package to clean up the folder first.

Step 5: Connect to the SFTP site in WinSCP
Using the credentials provided by the receiving organization, connect to the site in WinSCP. Next, go to the Session menu and select Generate Session URL/Code. In the dialog that appears, go to the Script tab and set the Format dropdown to Command-line. You’ll see a long line of code; this code is the key to the entire process, as it will tell WinSCP how to upload your files. Click Copy to Clipboard at the bottom of the dialog, and paste the code into your text editor of choice. You can close WinSCP.

Step 6: Edit the command-line code
The code WinSCP provides is meant to be a jumping-off point only. You need to tweak it to meet your specific needs.

The first thing you want to change is the path to the log file. Remember we created that folder in step 2? That’s where you want your log files to be sent. So at the start of the command line, where it says…

/log="C:\writable\path\to\log\WinSCP.log"

Edit that to read…

/log="C:\Program Files (x86)\WinSCP\WinSCP.log"

Next, go to the end of the line where you see “Your command 1″ and “Your command 2″. You’re going to replace these with your FTP commands. The first command should be to change the directory to the one indicated by the receiving organization. For example, they may tell you to drop off your files in the “/IN/PRODUCTION” directory. In this case you would change “Your command 1″ to read:

"cd /IN/PRODUCTION"

That puts you into the correct directory.

Next, add a command to put the file into the directory. Edit “Your command 2″ to read:

"put -nopreservetime -nopermissions " + @[User::FileName]  + ""

Trust me on that double-quote at the end for a moment, we’ll get there. I strongly recommend using the -nopreservetime and -nopermissions switches, as failing to set those will throw errors on some SFTP servers.

At this point, I recommend making a copy of what you have so far and pasting it into a new line. It’s really easy to screw up this next part, and you’ll be glad you made a copy so you can go back to the original and start over.

The next step involves “escaping” every quotation mark and backslash in the command-line string. You need to do this so you can use the string in an SSIS expression. (This will all make sense in a minute, trust me.) To do this, go through the line and place a \ before every ” or \. So this…

/log="C:\Program Files (x86)\WinSCP\log\WinSCP.log"

Becomes this…

/log=\"C:\\Program Files (x86)\\WinSCP\\log\\WinSCP.log\"

Do you see how the quotation marks both have a backslash before them? And where we had one backslash we now have two? This is critical and it’s really easy to get wrong. And just to make it more complicated, we have an exception to deal with. At the end of the line you have…

"put -nopreservetime -nopermissions " + @[User::FileName]  + "" "exit"

See the quotes to either side of the FileName variable? You do not want to escape those two quotes. We want SSIS to see them as real quotes so it will concatenate the file name into the line. So you should end up with this…

\"put -nopreservetime -nopermissions " + @[User::FileName]  + "\" \"exit\"

One last step, just enclose the entire line in quotation marks, putting one at the beginning and one at the end, like so…

"/log=\"C:\\Program\"exit\""

At this point you should be done, and we’ll find out how you did soon enough.

Step 7: Create an Execute Process task
Call the process something like “SFTP via WinSCP” and add it to the SSIS package somewhere after the file has been created. In the Process section of the Task Editor, set the Executable to C:\Program Files (x86)\WinSCP\WinSCP.com. Be sure to use the .com executable, not the .exe. The .com is the command-line interface.

Next go to the Expressions section. You want to create two expressions:
WorkingDirectory: This is where the file to be uploaded is located. You probably already have a variable set up with this information, so use that here. Otherwise you can hard-code the path if that’s appropriate. Be aware that WinSCP may not accept a UNC path here, so try to use a local path if possible.
Arguments: Okay, it’s the moment of truth. Go into the Expression Builder and paste in your edited command-line string. Click Evaluate Expression… if all went well, you should see your command line in the Evaluated value box. If not, you’ll get an error. It can be tricky figuring out from this error exactly where the problem is, but it can only be one of a few things:

  1. You didn’t encase the line in quotes.
  2. You failed to escape a quote or a backslash.
  3. You escaped one or both of the quotes around the FileName variable.

Go back to your text editor and start looking, because you’re stuck here until you find that error. Don’t be afraid to go back to the original line and start escaping those quotes and backlashes from the beginning. Take your time, you’ll get there.

Step 8: Test and implement the package
Now you’re ready to test. You’ll want to contact the receiving organization and let them know what you’re up to, so they’re not surprised when they see random files appearing. (They may ask you to put the files into a test directory; in that case, simply edit the “cd” part of your command-line string to point to the new target. Just don’t forget to set it back before you go live.) Test on your development machine first; what I like to do is have the WinSCP client open to the site, run the package, then refresh the WinSCP window to confirm that I can see the uploaded file. I can also delete them from the site if I don’t want the test files processed.

Finally, import the package to your SQL Server, create a SQL Agent job for it, and run the job. This is the real moment of truth. If the SFTP task fails, check the log file. It should tell you exactly what went wrong. If there’s no log file, you almost certainly have a permissions issue. Confirm that your SQL Agent user has full permissions to the WinSCP application folder and try again.

I hope this works out for you. It’s a bit of a process, but if you take your time and follow each step, you’ll get there. Don’t hesitate to ask questions, I’m here to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">