Run and Export SSRS Reports from SSIS (SQL Server 2005)

Few days before I finished SSIS package which produces lot of Excel sheets for regular use in company where I work. I am not so sure how much is useful to have sheet with 20 or 30 thousands rows (yes, that’s for regular work), but I made a tool and Server takes care about populating Excels.

So, customers are almost happy, but… as an addition to excels I had to made Summaries for every excel. I decide to made SSRS report with one parameter and run it trough SSIS and export it like PDF file in same folder where Excels are. Unfortunately, wasn’t so easy to find simple solution and that is reason for this post…

Picture of simplified solution:

ReportExport1

Execute SQL Task – collection of “Parameters” from database

Foreach Loop Container – Loop execution of report for every parameter from Sql Task (trough AdoEnumerator) saved in string variable

Script Task – render report in pdf and save it on local disk

Script task needs Credentials for  HttpWebRequest class. I’m using default, but it can be changed in SSIS configuration file.

Example package can be downloaded here ReportExport.zip – delete .doc extension (SSIS and SSRS project with sample report).

Advertisements
This entry was posted in SSIS. Bookmark the permalink.

46 Responses to Run and Export SSRS Reports from SSIS (SQL Server 2005)

  1. Dalamar says:

    Thank, work very well.

  2. Unknown says:

    Thanks man! It\’s a really cool solution for client who bought SQL Server STANDARD edition instead of Enterprize,

  3. Kris says:

    Thank a lot for sharing..

  4. krishna says:

    I am not able to open the downloaded file..

  5. Leif Hurst says:

    This is genius… been trying to figure this one out for a while now!

  6. zezaidali says:

    can this same thing be done with preinter as a delivery extension. I have been tryint to deploy the printer delivery extension sample for two days and my data driven subscription is not able to find the printer on the server and prints as .xps file on disk.

    I am thinking how can i modify the script to print the pdf generated to a specific netwrk printer .

    Please suggest.

  7. Mehdi says:

    Not sure why you have the file saved as .doc???
    But thanks for pointing us to the right direction.

    Thanks

  8. Mehdi says:

    Hi, I am getting an error on the production server.
    System.Net.CredentialCache.DefaultCredentials doesn’t work for me. Getting 404 Forbidden Error when trying to save the file.
    Any idea?
    Thanks

  9. ecsastroamit says:

    how can i pass parameters required in SSRS .rdl file in script component.

  10. Franco says:

    thanks

  11. Abhi says:

    i am not able to open the file.can anyone please help

  12. Matt says:

    You absolute legend!!!! Thanks a lot.

  13. Yik Wing says:

    Hi,
    Thanks for your codes and it worked perfectly in our SQL 2008 R2 environment. I am testing the codes in SQL 2014 and it was working when I ran the package locally from Visual Studio but it failed once I deployed it to our new SQL 2014 sever and have the following error message: “Exception has been thrown by the target of an invocation.”. Do you have any idea why it failed? I am suspecting the service account that use to run the package might not have permission to execute the SSRS report since it is a newly built machine. Is there a way I can change the credentials to not using the default credentials?

    Thanks,

    Yik Wing

    • Zoran Ivić says:

      Hi Yik,

      I uploaded version for SQL 2014.

      Please try to check in Job step properties “Use 32 bit runtime” option, and also try with different user for Server Agent Account.

      I just tested it on new machine: win 8.1, sql server 2014 (12.0.1524) and latest SSDTBI (VS 2013), sql server job: package source – file system, sql agent account – Network Service and works fine.

      Also try to run package with DTExecUI (double click on package.dtsx).

      Kind regards,
      Zoran

      • Yik Wing says:

        Hi Zoran,

        I tried to open the 2014 version but it failed with the following messages:

        The package failed to load due to error 0xC0010014 ‘One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. “This occurs when CPackage::LoadFromXML fails.

        These are what I saw from Error List:

        Error 1 Error loading Package.dtsx: The version number in the package is not valid. The version number cannot be greater than current version number. C:\Users\a_shumy\Desktop\reportexport2014\reportexport2014\ReportExportSSIS\Package.dtsx 1 1

        Error 2 Error loading Package.dtsx: Package migration from version 8 to version 6 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”. C:\Users\a_shumy\Desktop\reportexport2014\reportexport2014\ReportExportSSIS\Package.dtsx 1 1

        Error 3 Error loading Package.dtsx: Error loading value “8” from node “DTS:Property”. C:\Users\a_shumy\Desktop\reportexport2014\reportexport2014\ReportExportSSIS\Package.dtsx 1 1

        Error 4 Error loading ‘Package.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails. . C:\Users\a_shumy\Desktop\reportexport2014\reportexport2014\ReportExportSSIS\Package.dtsx 1 1

        Thanks again for your time.

        Yik Wing

      • Zoran Ivić says:

        Hi Yik,

        I belive that problem is in Visual Studio (BIDS/SSDTBI tool) version. Please, can u try to open project in latest vesrion: http://www.microsoft.com/en-us/download/details.aspx?id=42313

        Best regards,
        Zoran

  14. Yik Wing Shum says:

    Hi Zoran,

    Too bad I couldn’t install the latest version. I believe we are using the Visual Studio 2012 version.

    Yik Wing

  15. kiran says:

    Hi Zoran,

    Thank you very much for providing this much simple mechanism to exporting SSRS reports from SSIS package.

    I have tried this in SSRS 2008 R2, but the data is not getting exported into the output files(both PDF and Excel).

    Please help for resolving this issue.

    Thanks,
    Kiran

  16. Ankur Sharma says:

    I am getting following error message whenever I tries to open generated PDF :-
    “Adobe Reader could not open ‘(FileName.pdf)’ because it is either not a supported file type or because the file has been damaged”

    Kindly suggest

    • Zoran Ivić says:

      Hi,
      There is something wrong with script task (size of pdf is 0). Please check path to report.

      • Bonnie says:

        Hello Zoran Ivic. I actually have two slightly different script versions that I’m testing. Both scripts are copying a pdf file to its final destination, however when I try to open it, I get the same error as Ankur Sharma. I’ve checked the URL path and it’s pointing to the right location, and as mentioned it is copying as expected. Do you have any suggestions to get around this? Could it be a permissions issue? Thank you.

      • Zoran Ivić says:

        Hi Bonnie,

        2 lines in script task are most important:
        where to save file
        destination = Dts.Variables(“varDestinationPath”).Value.ToString + “\” + Dts.Variables(“varRSParameter”).Value.ToString + ” ” + Format(Now, “yyyyMMdd”) + “.pdf”

        url on report server
        url = “http://localhost/ReportServer?/ReportExport/Report – Get it from SSIS&rs:Command=Render&SSIS_Parameter=” + Dts.Variables(“varRSParameter”).Value.ToString + “&rs:Format=PDF”

        put for example msgbox(url) in your code to check what comes from variable and copy it to IE
        try to put http://localhost/ReportServer?/ReportExport/Report – Get it from SSIS&rs:Command=Render&SSIS_Parameter=PAR
        in IE to see is ti working
        if its not working then is something wrong with reportserver and reports example deployed from my VS solution)

        kind regards,
        Zoran

    • Bonnie says:

      Hello Ankur Sharma. I’m getting the same error. Were you able to find a solution? Thank you.

  17. Deblina Roy says:

    Hi ,
    I want to export SSRS report to Excel via SSIS in visual studio 2012. But i am getting the following error when i am opening the downloaded excel..:”Report Viewer Configuration Error”
    also there is written the following lines :
    “The Report Viewer Web Control HTTP Handler has not been registered in the application’s web.config file. Add to the system.web/httpHandlers section of the web.config file, or add to the system.webServer/handlers section for Internet Information Services 7 or later. ”

    where can i find web.config file in visual studio 2012 BIDS environment.?
    I am trying to solve this but unable to do so. To solve the problem i have installed Report Viewer runtime 2012 in my system. But still i am getting the error.

    Please help me!!

    Deblina

  18. Pingback: Run and Export SSRS Reports from SSIS (SQL Server 2012) | :: BI Blog Tryout ::

  19. Pingback: Run and Export SSRS Reports from SSIS (SQL Server 2014) | :: BI Blog Tryout ::

  20. Gilbert says:

    Hi,

    I have done the same logic in your demo above. Thanks for this.
    Its just that I am having problem with the exported file. By the way, I used the format: excel.\

    Here is the error.

    The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

    Any help will be greatly appreciated 🙂

    • Zoran Ivić says:

      Hi Gilbert,

      2 lines in script task are most important:
      where to save file
      destination = Dts.Variables(“varDestinationPath”).Value.ToString + “\” + Dts.Variables(“varRSParameter”).Value.ToString + ” ” + Format(Now, “yyyyMMdd”) + “.pdf”

      url on report server
      url = “http://localhost/ReportServer?/ReportExport/Report – Get it from SSIS&rs:Command=Render&SSIS_Parameter=” + Dts.Variables(“varRSParameter”).Value.ToString + “&rs:Format=PDF”

      put for example msgbox(url) in your code to check what comes from variable and copy it to IE
      try to put http://localhost/ReportServer?/ReportExport/Report – Get it from SSIS&rs:Command=Render&SSIS_Parameter=PAR
      in IE to see is ti working
      if its not working then is something wrong with reportserver and reports example deployed from my VS solution)

      kind regards,
      Zoran

  21. jamal ahmad says:

    unable to open download file

  22. Dave says:

    Hi Zoran, I tried a couple of solution and this one is the easiest to implement.
    Next for me would be scheduling this task in SQL Server Agent.

    Thanks for your excellent examples.

  23. garkbit42 says:

    Hi Zoran,
    I can’t quite get it to work. I have checked the url in the script works when pasted directly into IE, and it successfully prompts me to save the PDF, which opens and has the report I expect to see (with the correct details for the parameter provided).
    e.g.
    https://SqlServer/ReportServer_SSRS?%2fReports%2fRPT123+Patient+Note&rs:Command=Render&rs:Format=PDF&rc:Parameters=false&PatientIdentifier=P1234

    When I run it from the script using a stored procedure to list a set of Patient IDs it successfully loops through the list, creating a PDF in the correct destination folder for each patient record, with the ID and today’s date in the filename.

    However I can’t open the PDFs as they are invalid – merely 3kb in size and containing xml which looks like a username/password prompt:
    Username:
    Password:

    How do I store the necessary credentials for the SSRS user that is needed to render the url?

    Thanks,
    Ulen.

    • Zoran Ivić says:

      Hi Ulen,

      Please try to change

      loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials

      in script task to:

      ‘loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
      Dim creadentials As Net.NetworkCredential
      creadentials = New Net.NetworkCredential(“username”, “password”, “domain”)
      loRequest.Credentials = creadentials

      Hope this will help,

      Kind regards,
      Zoran

  24. Enrique O says:

    Thank you!!
    Works very well!!
    I’m wondering how can I do the same but with text file as source

    Regards

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 )

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