We are building our bill system printing process and part of the process is to generate a huge PDF to a file share with single page bills inside to print on our folding / stuffing appliance. Currently, there is a one time subscription created on the report server that is then executed from SSIS in the form of a "Execute SQL Server Agent Job Task" which will send a PDF to a file share defined in the subscription. We are noticing that this process can go wrong at many points and need to find a way to account for each potential problem. We first notice that SSIS is totally unaware of almost all failures of the report subscription except the most basic failures that the SQL Server agent is aware of like the subscription being faulty or the database being down; we can capture that easily enough; but it isn't sufficient. SSIS executes the SSRS subscription in seconds even though it might take 45 minutes or longer to render. After SSIS has called the subscription, we have noticed a few problems can happen. 1. A permissions issue can occur on the file share if your password expires (Failure writing file: A log on error occurred when attempting to access the file share. The user account or password is not valid.). SSIS is unaware. 2. A memory issue can cause the report to be aborted (Error: Thread was being aborted). SSIS is unaware. 3. You can get a nebulous "An error occurred during rendering of the report." SSIS unaware. 4. The file server can run out of space. (SSIS is unaware) All of these problems have seperate solutions and are not part of my question today; and I suspect there are more potential problems as well but as a production system we need to know when things go wrong so the on call person can resolve it. We first thought to do a WMI query on the file server to see if the file arrives on the server. That works, to an extent. You can set the file server to find out if the file has arrived, but you have to set a timeout value to the highest possible amount so that WMI waits long enough to know if the HUGE pdf is done. This isn't effective since some of the errors above can occur immedietly and you could end up waiting a very long time (your timeout period) to find out that the rendering probably went bad. We then thought to query SQL Server because we know the ReportServer.dbo.Subscriptions table can tell us if the report has had many of the errors above. However we noticed that the table is written to in SQL before the actual PDF arrives on the file server, sometimes in radically different amounts of time. So we aren't really certain that the file has arrived since SQL is telling us the file has saved before the file has even been finally saved. So does anyone know of a "best" way to programatically determine the success or failure of a SSRS PDF render to a file share? Our on call person needs as much time as possible to resolve this so that the rest of the unrelated schedule throughout the evening can finish.

If you need this amount of control over the rendering you may be better off not using a subscription to run the report but to write script that renders the report for you - that way you can embed some TRY / CATCH error blocks that tell you what is going on during the process Presumably you need SSIS to wait until render is complete anyway so the time taken to render shouldn't be an issue in terms of the SSIS job pausingRgds Geoff

Something in VB.NET or C#, take your pick because I assume you mean this would go beyond SQL SP's and SSIS's ability to track this?

As long as you have access to the web reference from within a script odule in SSIS I would imagine you could do it all in the script module there If the only reason for doing it in SSIS is to start up the subscription programatically then yes you're best off with a stand alone app The code is actually pretty simple - I have posted a sample for someone else recently so will just pop the same one up: As I mentioned - as long as you can set the appropriate references then there is no reason that this shouldn;t work inside an SSIS script module Public Sub Save_RS_As_File(ByVal strReportPath As String, ByVal strParamName As String, ByVal strParamValue As String, ByVal strDestination As String, ByVal strFileName As String, ByVal strOutputType As String) Dim rs As New ReportExecutionService() ' link to reportingservicesservice.asmx rs.url = "path to service location.asmx" ' Render arguments Dim result As Byte() = Nothing Dim reportPath As String = strReportPath Dim format As String = IIf(strOutputType = "PDF", strOutputType, "Image") Dim historyID As String = Nothing Dim devInfo As String If format = "PDF" Then devInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>" Else devInfo = "<DeviceInfo><Toolbar>False</Toolbar>><OutputFormat>JPEG</OutputFormat></DeviceInfo>" End If ' Prepare report parameter. Amend this area if you need to pass multiple parameters Dim ctr As Integer = 0 Dim parameters(ctr) As ParameterValue New ParameterValue() Dim credentials As DataSourceCredentials() = Nothing Dim showHideToggle As String = Nothing Dim encoding As String = "" Dim mimeType As String = "" Dim warnings As Warning() = Nothing Dim reportHistoryParameters As ParameterValue() = Nothing Dim streamIDs As String() = Nothing Dim execInfo As New ExecutionInfo Dim execHeader As New ExecutionHeader() Dim extension As String = "" rs.ExecutionHeaderValue = execHeader execInfo = rs.LoadReport(reportPath, historyID) rs.SetExecutionParameters(parameters, "en-uk") Try result = rs.Render(format, devInfo, extension, _ encoding, mimeType, warnings, streamIDs) Catch ex As SoapException End Try Try Dim stream As FileStream = File.Create(strDestination & "\" & strFileName & "." & strOutputType, result.Length) stream.Write(result, 0, result.Length) stream.Close() Catch ex As Exception End Try End Sub msgbox (ex.Message) msgbox (ex.Detail.OuterXml) parameters(ctr).Name = strParamName parameters(ctr).Value = strParamValue parameters(ctr) = rs.Credentials = System.Net.CredentialCache.DefaultCredentialsRgds Geoff

Hi, Why not write a small C# code that uses System.IO.Folderwatcher class. What happens in this case is that when program gets an event when the file is created. you can handle this event in code and then update a database table that you are already using. By this way the updates will be realistic. RegardsVenkatesh. S|MCTS(WCF, ADO.NET 3.5)|eMail: heman_1978@hotmail.com