Thursday, February 5, 2015

Automating the scheduled extraction of db data and FTP of it using SQL Reporting Services 2012

Our intranet team manages our relationship with Next Jump, an e-commerce company that manages employee savings programs for companies worldwide including 70% of the Fortune 500.

To login to their site, employee data must first be sent to Next Jump.  We could log into our intranet db server, open SQL, run a script, copy the data to Excel, then go to the vendor site to upload it...but I get bored by repetitive things so we've automated the process.

The following steps were necessary:
  1. Start SQL Reporting Service 2012
  2. Create a report project
  3. Subscribe to the report
  4. Implement a scheduled SFTP task 
Start SQL Reporting Service 2012

From the server you have SQL installed on, open SQL 2012 Reporting Services Configuration Manager and essentially clicking "Next" through each step.  You'll want to take note of the web service virtual directory, and the report manager site identification.  The default for them is:
  • http://<your server name>/Reports
  • http://<your server name>/ReportServer
At this point both of those URLs should resolve and give an indication that your Report Service is up and running.  If you are running into difficulties, this is a great article which helped me get it up and running: How To Configure SQL Server Reporting Services In Order To Deploy Reporting Services Reports In GP

Creating a Report Project
Getting this setup was relatively easy as I had done it previously, albeit on an older version of SQL (Creating a report from Oracle WebCenter Analytics db using SQL Reporting Services).
  1. From your Start button, open SQL Server Data Tools.  If this is your first time going through it'll ask you to select a format - choose Business Intelligence if you are just making reports.
  2. Select File > New > Project > Report Server Project, be sure to name the project at the bottom of the page.
  3. In the Solution Explorer right click Reports then choose Add > New Item > Report, be sure to name the report at the bottom of the page.
  4. Specify a data connection from the Report Pane by clicking New > Data Source.  Name and configure your source.
  5. Define your data set from the Report Pane by clicking New > Data Set.  Name the data set and enter your query.
  6. Add a table to your report by going to the toolbox, selecting Table and dragging it onto the Design area of your report.
  7. Add your data to the report table by expanding your data set in the Report Data pane and dragging/dropping your data elements into the table column headers.
  8. Toggle the report preview button to ensure your data connection is working and the table is displaying nicely
  9. Deploy your report to the web server by selecting Build > Deploy
I've greatly simplified the steps above.  For a more comprehensive explanation with pictures please refer to Create a Basic Table Report (SSRS Tutorial) on the Microsoft Developer Network.

Subscribe to the Report
Assuming a successful deployment, verify it by going to the virtual directory at http://<your server name>/Reports.
To subscribe to the report:
  1. From the home page select the report folder, then select the down arrow next to the report and select Subscribe.  This dropdown didn't appear for me in Firefox so I had to use IE.
  2. Select the Windows File Share delivery method and configure the report as necessary
  3. From the My Subscriptions link (top right of page) you can verify the subscription, when it was last run, and it's status.
Yay! The easy part is done!

Implement a scheduled SFTP task
From the brief research I did I came to the conclusion that FileZilla wouldn't work and that I would need to install http://winscp.net which has a command line option that would allow for scheduled SFTP.

The install is straight forward and there is plenty of documentation on how to use the tool from a command line or through other forms of programming.  What I found most useful was their page Schedule file transfers (or synchronization) to FTP/SFTP server.
  1. Start by installing WinSCP
  2. Open the client application and connect to your SFTP site.  You might want to save the connection for later testing.
  3. I'm on Windows 7, so I went to Admin Tools > Schedule Tasks then created a Basic Task
  4. For the action choose start a program ("C:\Program Files (x86)\WinSCP\WinSCP.exe")
  5. You'll need to point to a script file (.txt) which will open the FTP application, connect to the site, and transfer the file.  For the arguments area you'll need to point at this script using a command like: /script="\\<your share file path>\WinSCP_script.txt".  For testing you might want to prefix that with /console which will open a window to show you that it is working as intended.
  6. Next go and create the script file in the location you specified.  This is what mine looks like:
    # Automatically abort script on errors
    option batch abort

    # Disable overwrite confirmations that conflict with the previous
    option confirm off

    # Connect to SFTP server using a password
    open sftp://username:password@connectionSite.com/

    # File to upload
    put \\HQ\GeoffGarcia\Project\NextJumpExport\NextJump.xls

    # Closes all sessions and terminates the program
    close
    Run it all a few times to make sure it works without a hitch...and you're done!!!
    This might seem a little tricky if you haven't tried to create a SQL Report, use an FTP client, or write a script, but I hope this mini-guide will help you accomplish your goal!


Troubleshooting:
The second time I tried to implement this I couldn't get the console window to open or any other programs that I tried it with.  I deleted the task I had created and then recreated it and everything worked.