April 12, 2017 at 11:55 am
Hi ,
i would like to know if there is a way i could export the results from a subscription in SSRS to a SFTP folder. I am aware this is not an out of the box solution and i have an instance of SSRS ( 2008 ) and they have ( my organisation ) purchased an unknown plugin which would post it to SFTP folder and now we are in the process of upgrading to 2016 SSRS and we could not find any support for the plugin and we could not trace the person who sold it to us . That being said , i had few ideas in mind as to how this could be implemented and planned to reach out to this forum for ideas. The requirement is as follows :
There are about 60 reports that are being exported to a SFTP folder and we would like to come up with a solution to use the same reports to be exported to SFTP folder . I believe we can use SSIS + Execute Script Task to get this working and .Net is not my strong suit . I thought may be we could use a SQL table which would have the name of the reports and the frequencies in which it has to be executed, SFTP server name and the destination folder. We should have just one script in the package which can fetch values from the table and decide how many reports should be executed at that particular time and then execute the reports and post the results to SFTP folder and may be wrire these events into a log table . we could then schedule this ssis package to run every day .
Let me know if there are any alternative thoughts.My limitations would be , i cannot use the windows file share on SSRS subscription since the organisation is a little skeptical about storing the files on a local machine and then using a script to move the files to SFTP server. The reason we wanted a table for this is for easy maintenance we dont have much resources working on power shell scripting etc.
Any suggestions would be welcome
Regards,
Savio
April 12, 2017 at 3:31 pm
Any reason not to set up a file share on a network drive with restricted permissions (IT department and SSRS account for example) that could store the reports and then dump them out to the SFTP and remove them when done?
Something similar to this:
http://www.sqlservercentral.com/blogs/whatilearntatwork/2011/07/14/send-ssrs-subscription-files-to-_2800_secured-ftp_2900_-sftp/
If you are dumping the reports to a table, you are likely losing all of the nice formatting and stuff that SSRS is good for. If you are just wanting to do a basic data dump to a file on an SFTP, is there any reason not to do the whole thing in SSIS? data flow task to get the data (via script task) and dump it in to an FTP destination?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 13, 2017 at 2:42 am
If you can't save the files to a file share first, then you're going to find this quite difficult.
What is the "real" reason your storing files, even temporarily, on your domain? Considering that someone who has access to the report(s) could get the data out that way, why would saving it to a fileshare make it any less secure. Considering you can give as few users/service accounts access to the directory as you'd like, it really shouldn't been seen as a "security problem".
On the note of SSRS -> File share ->SFTP I do have a few of these types of subscriptions set up. I use SSIS to achieve the process. The main problem is when you run an SSRS subscription, all you do is start it. You don't get a return upon completion of the subscription, just a confirmation that it has started. That means that you have to poll the database to check if it's done if you want it to happen in "real time". That's not to bad with simple and quck reports, they've probably saved off after 10 seconds or so, so you can check every 10 seconds or so. If the report is a bit more involved (say 5 minutes to complete), then this is far from ideal.
In that scenario you'd be better off have a process to export your files, and another to transfer them. If all the files are going to the same directory, and all to the same SFTP server, you could have an SSIS that runs every... 5 minutes, checks the directory, uploads any files it finds to the SFTP server, and then removes the local copy(s).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 7:34 am
bmg002 - Wednesday, April 12, 2017 3:31 PMAny reason not to set up a file share on a network drive with restricted permissions (IT department and SSRS account for example) that could store the reports and then dump them out to the SFTP and remove them when done?Something similar to this:
http://www.sqlservercentral.com/blogs/whatilearntatwork/2011/07/14/send-ssrs-subscription-files-to-_2800_secured-ftp_2900_-sftp/If you are dumping the reports to a table, you are likely losing all of the nice formatting and stuff that SSRS is good for. If you are just wanting to do a basic data dump to a file on an SFTP, is there any reason not to do the whole thing in SSIS? data flow task to get the data (via script task) and dump it in to an FTP destination?
Thanks for your reply . The admin who manages the server is not ready to put the files on a shared folder not even for a little amount of time , May be its hard to maintain for him . lol 🙂
Regarding the table that i was talking about , the table would contain just the report names and the formatting is still coming from SSRS . the table would be used from the code as a parameter to go and execute the report on that particular location and then export the results to a particular folder in SFTP . I have also considered the option of running the report query or stored procedure from SSIS as a data source and the result would be dumped to SFTP ( i am aware they have FTP component in SSIS but not SFTP , so this might need a bit of scripting to be done on SSIS ) . Correct me if i am wrong . And the constraint we have with SSIS is that we would have to spend a whole lot of time re writing all the reports in SSIS and we have about 60 reports under this category , This started as a simple exercise to migrate from 2008 SSRS to 2016 SSRS and is going out of budget already .
April 13, 2017 at 7:44 am
It all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to an SFTP server, or the admin's stubbornness to not allow people to save a document to a directory (which can then be deleted afterwards). 😉
On SSIS and SFTP, no there is no in built SFTP support (although there are third party tools, i haven't seen any (yet) for 2016). I, personally, use psftp and then use command line execute tasks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 7:49 am
Thom A - Thursday, April 13, 2017 7:44 AMIt all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to an SFTP server, or the admin's stubbornness to not allow people to save a document to a directory (which can then be deleted afterwards). 😉On SSIS and SFTP, no there is no in built SFTP support (although there are third party tools, i haven't seen any (yet) for 2016). I, personally, use psftp and then use command line execute tasks.
i agree this has to be decided based on the parameters u have just said. i am trying to get the admin convinced on the local file share so that i just have to change the subscription which would not take much time , but according to this solution , if we have 60 reports, do we still need 60 SSIS packages or just one to cater to all the reports? the destination folder would change for each report although they are on the same SFTP server .
Regards,
Savio
April 13, 2017 at 7:52 am
savioceg - Thursday, April 13, 2017 7:49 AMThom A - Thursday, April 13, 2017 7:44 AMIt all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to an SFTP server, or the admin's stubbornness to not allow people to save a document to a directory (which can then be deleted afterwards). 😉On SSIS and SFTP, no there is no in built SFTP support (although there are third party tools, i haven't seen any (yet) for 2016). I, personally, use psftp and then use command line execute tasks.
i agree this has to be decided based on the parameters u have just said. i am trying to get the admin convinced on the local file share so that i just have to change the subscription which would not take much time , but according to this solution , if we have 60 reports, do we still need 60 SSIS packages or just one to cater to all the reports? the destination folder would change for each report although they are on the same SFTP server .
Regards,
Savio
That depends, does every report have the same source and destination columns? If so, you could get away with dynamic source and destination locations, if not, you're going to need to build data flows for every one, as SSIS doesn't handle variable column numbers well (at all).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 8:13 am
Thom A - Thursday, April 13, 2017 7:52 AMsavioceg - Thursday, April 13, 2017 7:49 AMThom A - Thursday, April 13, 2017 7:44 AMIt all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to an SFTP server, or the admin's stubbornness to not allow people to save a document to a directory (which can then be deleted afterwards). 😉On SSIS and SFTP, no there is no in built SFTP support (although there are third party tools, i haven't seen any (yet) for 2016). I, personally, use psftp and then use command line execute tasks.
i agree this has to be decided based on the parameters u have just said. i am trying to get the admin convinced on the local file share so that i just have to change the subscription which would not take much time , but according to this solution , if we have 60 reports, do we still need 60 SSIS packages or just one to cater to all the reports? the destination folder would change for each report although they are on the same SFTP server .
Regards,
SavioThat depends, does every report have the same source and destination columns? If so, you could get away with dynamic source and destination locations, if not, you're going to need to build data flows for every one, as SSIS doesn't handle variable column numbers well (at all).
has anybody implemented this custom delivery extension ?
https://docs.microsoft.com/en-us/sql/reporting-services/extensions/delivery-extension/implementing-a-delivery-extension
do u think this could be done for SFTP ? i could not find much resources online for this and the post was for an older version of SSRS ( may be 2005 or 2008 , not even 2008 R2)
April 14, 2017 at 12:36 pm
savioceg - Thursday, April 13, 2017 8:13 AMThom A - Thursday, April 13, 2017 7:52 AMsavioceg - Thursday, April 13, 2017 7:49 AMThom A - Thursday, April 13, 2017 7:44 AMIt all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to an SFTP server, or the admin's stubbornness to not allow people to save a document to a directory (which can then be deleted afterwards). 😉On SSIS and SFTP, no there is no in built SFTP support (although there are third party tools, i haven't seen any (yet) for 2016). I, personally, use psftp and then use command line execute tasks.
i agree this has to be decided based on the parameters u have just said. i am trying to get the admin convinced on the local file share so that i just have to change the subscription which would not take much time , but according to this solution , if we have 60 reports, do we still need 60 SSIS packages or just one to cater to all the reports? the destination folder would change for each report although they are on the same SFTP server .
Regards,
SavioThat depends, does every report have the same source and destination columns? If so, you could get away with dynamic source and destination locations, if not, you're going to need to build data flows for every one, as SSIS doesn't handle variable column numbers well (at all).
has anybody implemented this custom delivery extension ?
https://docs.microsoft.com/en-us/sql/reporting-services/extensions/delivery-extension/implementing-a-delivery-extension
do u think this could be done for SFTP ? i could not find much resources online for this and the post was for an older version of SSRS ( may be 2005 or 2008 , not even 2008 R2)
Yes. In order to synchronize our firm users info to a online expense manage company (3rd party), we implement SFTP delivery using SSRS + SSIS with WinSCP. The package is in VS2008 version.
My SSIS package upload SSRS generated file to a SFTP server.
The kernel to make WinSCP to work is using "Execute Process Task"
Executable = C:\Program Files (x86)\WinSCP\WinSCP.com
Arguments = -script=C:\xxx.txt
Here is an example of the xxx.txt
option batch on
option confirm off
open sftp://username:password@servername:22
cd files
put "C:\folername\*"
close
exit
Make sure you install WinSCP on the SQL Server with SSIS installed.
Here is the WinSCP website, https://winscp.net/eng/download.php
Hope this helps~
February 24, 2023 at 2:29 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply