Send SSRS subscription files to (Secured FTP) SFTP

We have set up subscription for customer which used to send mails to the customers periodically. Recently one of the customer requested us to upload the reports in secured FTP instead of sending it thru mail.

We had to look around for options to achieve this as SSRS does not support copying files to any other share (FTP/SFTP) other that windows share. If you try to put your FTP site folder in the subscription Windows File Share path, you will get an error

The path is not valid. The path must conform to Uniform Naming Convention (UNC) format

Many options are available like SSIS, .NET script etc but the issue is that we need to code to make it look similar to SSRS report (charts, gauges) etc. which requires an additional effort.

We figured out that the best option, considering the effort & turn around time, would be to combine the power of SSRS + scripting. We scheduled the report to run at specific time, say 10:00am GMT and delivered it to a windows share. We wrote a VBScript file to call the SFTP client (in our case PUTTY) to upload the file to SFTP. This script was scheduled using windows scheduler to run at 10:30am GMT.

Set objShell = WScript.CreateObject( “WScript.Shell” )
objShell.Run (chr(34) & “….Installation folder\PuTTY\pscp.exe” & chr(34) & “-sftp -l USERID  -pw PASSWORD ”  & chr(34) & NFile & chr(34) & ” SFTP SERVERNAME”)

The same approach can be used to password protect excel files

MFile = “SSRS SUBSCRIPTION FILE”

‘ Appending date at the end
NFile = “SSRS SUBSCRIPTION FILE” + CSTR(year(now())) + RIGHT(“0” & CSTR(month(now())),2) + RIGHT(“0” & CSTR(day(now())),2) + “.xls”
SET oXL = CreateObject(“Excel.Application”)
oXL.DisplayAlerts = FALSE
Set objWorkbook = oXL.WorkBooks.Open(MFile)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorkbook.SaveAs NFile,,”YOUR PASSWORD”
oXL.Quit