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


‘ 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”


About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Send SSRS subscription files to (Secured FTP) SFTP

  1. dpk says:

    I am getting this while saving on Win File share
    Failure writing file ManagementSummary-DQ : The report server has encountered a configuration error

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s