0

I have an SSIS 2008 package that is reading data from multiple excel files, performing transformation and generating output in an excel file. I'm then using that output excel file as data source to call an SSRS 2008 report hosted on localhost through the same SSIS package as Script Task and exporting it as pdf and xls reports. The reports are getting generated but are of small size and corrupted. When I run the same report from BIDS 2008 and export it to pdf or xls, it works fine.

The report has one parameter. SSIS execute script task is contained in a foreach loop container and passes that parameter one-by-one to generate 30 odd reports.

Would appreciate if someone could provide some help on this.

Romanshu Goel
  • 103
  • 3
  • 9
  • What does 'small size' and corrupted mean? Do you mean when you open the PDF it throws an error? How are your SSIS generated reports delivered? Do you save them somewhere? You could try a different architecture where your SSIS package imports the data into a database, and SSRS generates and delivers reports from that database. Use the right tool for the right job! – Nick.Mc Apr 05 '17 at 07:19
  • The expected file size is approx 45KB which is what I get when I export the report run out of Report Manager. When the same report gets exported using SSIS package, it is around 20KB. I get the following error when opening pdf output - "Acrobat could not open 'xyz.pdf' because it is either not a supported file type or because the file has been damaged". To automate end to end process I'm using SSIS to automatically generate reports. I followed this link - https://www.mssqltips.com/sqlservertip/3475/execute-a-sql-server-reporting-services-report-from-integration-services-package/ – Romanshu Goel Apr 06 '17 at 00:28
  • You might want to investigate subscriptions in SSRS to automatically generate your reports. You might be able to trigger a subscription to generate the file in your folder. http://www.randallkent.com/2011/10/06/trigger-ssrs-subscription-manually/ With regards to troubleshooting report generation ins SSIS, are there any clues in the SSRS log? https://learn.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view. How long does the report take to run when you run it interactively? Maybe the timeout in your script is too short. – Nick.Mc Apr 06 '17 at 02:57

0 Answers0