We have a contractual requirement to send account statements to our customers. Statements are sent 60 days prior to a customer's annual start date. If a customer has more than one account, they receive one letter containing all accounts with us (and the appropriate account details). In the past we have used an MS Access report to render these statements in a PDF file which is sent to a third party for printing and mailing. Obviously using Access is a less than ideal process for us. It can't be easily automated (I've read about automating Access but also know that it is strongly discouraged by Microsoft and others.) We'd like to start using an SSRS report (2008 R2) which would be called using and SSIS package that is ran daily. I think SSIS is a better solution than using a subscription because we do not want to generate a PDF if there are no customers that should receive statements that day. I've seen several examples of using an SSIS script task to render a report. I used similar code (below) and it seems to work fine except when we run into days with a very large number of customers (Over 23,000). Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports ST_ce371681ca544bf4a4217241f6d22770 <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() Dim objRSExec As New ReportServer1.ReportExecutionService Dim objResult() As Byte Dim objStream As FileStream Dim strFileType As String Dim strFileExtension As String Dim strFullPathName As String Dim mnth As String Dim dy As String Dim yr As String mnth = Month(Now()) dy = DatePart(DateInterval.Day, Now()) yr = Year(Now()) strFileType = "PDF" strFileExtension = ".pdf" strFullPathName = "C:\Complete Mailing.pdf" With objRSExec .Credentials = System.Net.CredentialCache.DefaultCredentials .Url = "http://myserver/reportserver/reportexecution2005.asmx" .LoadReport("/Yearly Account Statements", Nothing) objResult = .Render(strFileType, Nothing, strFileExtension, Nothing, Nothing, Nothing, Nothing) End With objStream = File.Create(strFullPathName, objResult.Length) With objStream .Write(objResult, 0, objResult.Length) .Close() End With Dts.TaskResult = ScriptResults.Success End Sub End Class The below error is returned when the task fails on large datasets. Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: The operation has timed out at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) • at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ST_ce371681ca544bf4a4217241f6d22770.vbproj.ReportServer1.ReportExecutionService.Render(String Format, String DeviceInfo, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds) at ST_ce371681ca544bf4a4217241f6d22770.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Configuring the timeouts has not made a difference. The error is returned roughly 2 minutes after running the task. I'm wondering if an alternative would be to generate the report in smaller batches, but I'm not sure how to implement something like that. I'm also open to other ways of creating this file, so long as it can be automated. Thanks in advance.

It looks like you are running 1 report for all 23000 customers at once...is that the case? If so, does the report work if you simply call it from report manager & render to PDF?Rgds Geoff

Yes, you are correct. I should mention that the report query itself is a simple "SELECT * FROM TableX" statement. The appropriate data is prepopulated in TableX by a stored procedure (called by SSIS). When I run the report from the manager and render it as a PDF it runs fine, all 23000. It does take a few minutes (less than 10) but there are no issues with that. The file is a bit large (approx 40MB) but there are no graphics, just text.

Well from the error message it looks like a web service timeout Can you set the SSIS package to "break on error" or alternatively set a breakpoint and step through the code to see at which point the delay is occurring - whether it is on load report or render...Rgds Geoff

Of course, your simplest option may be to do a count of identifiers in the table to start with and if over a certain number, perform an iterative query to fetch records in smaller groupsRgds Geoff

Thanks Geoff. It looks like the delay occurs on render. I think I will try your suggestiong with an iterative query. Hopefully that alleviates the problem!

np - if it is a render issue then unfortunately not a lot you can do report side other than fetch smaller batches....The problem is that there are numerous timeout areas - SQL Server, SSRS, ASP.NET (Report Manager), IIS and SSIS so would be a fair amount of work to decipher which one (or ones) are causing the issue...Rgds Geoff

Yep, your method was faster than continuing to try and troubleshoot the render. I created a for loop task in SSIS that created a 1000 page batch PDF. Works great, except the CPU usage spikes to 100% and stays that way for about 20 minutes after all the batches are finished. That should hopefully be a non-issue as the SSIS package will be ran during off-peak times. Thanks for the help Geoff.