Home > SSIS, SSRS > Generate PDF report from SSIS

Generate PDF report from SSIS


One of my project, I have been asked to design a SSIS package to upload some data from a source ABC to SQL tables and at the end of the package execution we should create a PDF report from SSRS. In this blog post,  I am going to show you how to download PDF report from SSRS in SSIS.

A report is already created and deployed in the report server.

pic4

Implementation

Step 1: Create SSIS project and Add a script task to the package.

Step 2: Create HTTP connection manager and connect with Report Server

Create Connection

pic1Provide necessary connection information for HTTP connection

pic2Step 3: Add a script task. We are going to write VB.NET code to download PDF report

pic3Edit Script task and Add this code in the main function

pic5

This script contains ReportFileName and HTTP property ServerURL. These two are containing key values to generate PDF report.

ReportFileName variable contains the name of the file and location to store the PDF report.

ServerURL property contains report URL access address and report export format.

Report access URL is http://Servername/ReportServer?ReportName

Report export format “&rs:Command=Render&rs:Format=PDF&rc:Toolbar=False”. This Part is the additional information given in the URL to generate PDF report. We can use other report rendering formation such as Excel, HTML and etc., in here.

Step 4: Now, Execute this package

pic6On successful execution, a PDF report files must be created and stored in the given ReportFileName path.

PDF Report Output

pic7

Advertisements
  1. LC
    June 19, 2014 at 3:40 pm

    Hi,

    I follow what you’ve pointed out and the SSIS script task ran without any issue. However, I am having problem opening the PDF file. Below is the error message. I am using SharePoint 2013 integrated mode. Can you please provide some advise?

    Adobe Reader could not open “abc.PDF” because it is either not a supported file type or because the file has been damaged.

    • June 19, 2014 at 10:34 pm

      Hello lc

      It look like an empty PDF file is created using the script task but the report is not rendered. Please check the report for any problem.

      • Art
        July 10, 2014 at 2:06 pm

        I tried using the code as well however i am getting the sale message. I ran the report manually and it runs fine.

      • Art
        July 10, 2014 at 2:18 pm

        I also checked the file siz and it changed from 0 to 51 once the export was complete. This tells me the file is not empty and it generated a report. Any Idea why we are getting this error. thank you

  2. Peter
    April 13, 2015 at 11:43 am

    I am getting the same error as LC & Art. Has anyone figured out the solution?

  3. July 27, 2015 at 1:21 pm

    Thanks for this great blog post. I have been trying to export SSRS report hosted on Azure SharePoint instance (In sharepoint integrated mode) using SSIS and nothing works… so far I can only find this one http://zappysys.com/products/ssis-powerpack/ssis-report-generator-task/ but they don’t have example of sharepoint. Any help will be much appreciated.

  4. Cindy
    January 8, 2016 at 4:43 pm

    Hi,

    Your post is really useful. It runs find in my local machine. But when I run it in the server the SSIS cannot connect to the SSRS. What can be the issue?

    I need to schedule this SSIS package via job.

    Thanks in advance.

  5. Kanishk Verma
    January 15, 2016 at 7:44 am

    Thanks a lot, this was really helpful

  6. Mike O
    April 5, 2016 at 7:49 am

    Took a tweak, but I got it to work!. Thanks for this, made my life easier as I wanted to generate files from a report base on query call.

  7. Rick M
    May 2, 2016 at 7:46 am

    try to add the following to the end of the string clientConn.ServerURL to get the PDF :

    “&rs:Format=PDF”

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: