SSIS to run SSRS Reports: works on local but "Network name could not be found" when run as agent job?

Question

Background info: We run a number (9 currently) of core SSRS reports, these are heavily parameterised, but the important ones are StartDate, EndDate, and Department. These are to be run on a Daily, Weekly, Monthly, Quarterly, Annual and Ad-Hoc (interactive) basis. The automated reports are to be saved to a particular folder in Sharepoint.

What I've done so far: Got all the reports into such a form where they will not 'wait' for parameters, but produce a report with just the same three parameters. These can thus be called with just their URL's:

http://server/ReportServer?/Folder/Report1&Department=DEPT1&ContactFromDate=01/01/2012&ContactToDate=31/01/2012+23:59:59&rs:Command=Render&rs:Format=PDF

http://server/ReportServer?/Folder/Report5&Department=DEPT8&ContactFromDate=01/04/2012&ContactToDate=06/01/2012+23:59:59&rs:Command=Render&rs:Format=PDF

..and so on.

There are about 20 departments, needing each of the 5 report types, so setting up Subscriptions isn't really an option, which is where SSIS comes in.

I've created a package that basically reads in report titles and departments from a SQL Table, populates ADO.Net Enumerators for each department/report then runs nested For..Each loops and attempts to fire off a WebRequest and save the resultant PDF to a sharepoint folder. This works fine on my developer PC, but when run as a job on the SQL Server (via a proxy set up in SQL Server) fails with a 'Network name not found' error.

This is my Visual Basic code:

//Microsoft SQL Server Integration Services Script Task
//Write scripts using Microsoft Visual Basic
//The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics


<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
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
    Dim loRequest As System.Net.HttpWebRequest
    Dim loResponse As System.Net.HttpWebResponse
    Dim loResponseStream As System.IO.Stream
    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
    Dim laBytes(256) As Byte
    Dim liCount As Integer = 1
    Try

        loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
        loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
        loRequest.ImpersonationLevel = Security.Principal.TokenImpersonationLevel.Impersonation 
        loRequest.Timeout = 600000
        loRequest.Method = "GET"
        loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
        loResponseStream = loResponse.GetResponseStream
        Do While liCount > 0
            liCount = loResponseStream.Read(laBytes, 0, 256)
            loFileStream.Write(laBytes, 0, liCount)
        Loop
        loFileStream.Flush()
        loFileStream.Close()
    Catch ex As Exception
    End Try
    System.Threading.Thread.Sleep(2000) 
End Sub

Public Sub Main()
    Dim strUrl, strUrlSP, strDestination As String

    --New destination for monthly
    strDestination = Dts.Variables("varDestinationPathSP").Value.ToString + "\" + Dts.Variables("varDepartmentName").Value.ToString + "\Monthly\" + Dts.Variables("varCrmReportTitles").Value.ToString + " " + Format(Now, "yyyyMM") + ".pdf"

    --New Url for Monthly reports.
    strUrl = "http://server/ReportServer?/Scheduled+Reporting/" + Dts.Variables("varCrmReportTitles").Value.ToString + "&Department=" + Dts.Variables("varDepartmentCode").Value.ToString + "&ContactFromDate=" + Format(Dts.Variables("varDatPreviousMonthStart").Value, "dd/MMM/yyyy").ToString + "&ContactToDate=" + Format(Dts.Variables("varDatPreviousMonthEnd").Value, "dd/MMM/yyyy").ToString + "+23:59:59&rs:Command=Render&rs:Format=PDF"
    strUrlSP = "http://intranet/ProjectBusinessSystems/ContactManagement/DocumentLibrary/Forms/AllItems.aspx?RootFolder=/ProjectBusinessSystems/ContactManagement/DocumentLibrary/Scheduled+Reports/" + Dts.Variables("varDepartmentName").Value.ToString + "/Monthly"

    strUrl = fnPadSpaces(strUrl)
    strUrlSP = fnPadSpaces(strUrlSP)

    --Set up the file path so the next step can copy to local drive (Testing purposes only)
    Dts.Variables("varFullReportPathToCopy").Value = strDestination
    Dts.Variables("varDestinationURL").Value = strUrlSP

    SaveFile(strUrl, strDestination)
    Dts.TaskResult = ScriptResults.Success

End Sub

Public Function fnPadSpaces(ByVal StringToEncode As String, Optional ByVal UsePlusRatherThanHexForSpace As Boolean = True) As String

    Dim TempAns As String
    Dim CurChr As Integer
    CurChr = 1
    TempAns = ""
    Do Until CurChr - 1 = Len(StringToEncode)
        Select Case Asc(Mid(StringToEncode, CurChr, 1))
            Case 32 -- Replace Spaces
                If UsePlusRatherThanHexForSpace = True Then
                    TempAns = TempAns & "+"
                Else
                    TempAns = TempAns & "%" & Hex(32)
                End If
            Case Else -- Otherwise pass it through
                TempAns = TempAns & Mid(StringToEncode, CurChr, 1)
        End Select

        CurChr = CurChr + 1
    Loop

    fnPadSpaces = TempAns
End Function
End Class

It fails on the SaveFile(strUrl, strDestination) line, when it's generated the PDF but failed to write it to the sharepoint folder. The Infrastructure guys assure me the permissions are set to allow the account running the SSIS job to write to the folder, and when I run the job in Vis Studio on my development box it works all the time.

varDestinationPath is like:

\\sharepoint\ProjectBusinessSystems\ContactManagement\DocumentLibrary\Some Reports

where sharepoint is the server name, not an alias.

The actual (quite verbose) error message from the log file viewer is shown below:

Date        06/06/2012 13:52:30
Log     Job History (Scheduled Reports MONTHLY)

Step ID     1
Server      DevServer
Job Name        Scheduled Reports MONTHLY
Step Name       Run Package
Duration        00:00:04
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: WinFarm\crmreporter. Microsoft (R) SQL Server Execute Package Utility     Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  13:52:31  Error: 2012-06-06 13:52:34.46     Code: 0x00000001     Source: Script Task - Render Report      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.IOException: The network name cannot be found.       at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)     at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)     at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)     at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)     at ScriptTask_f689e2ad4d3f481a87e3678e2d746c39.vbproj.ScriptMain.SaveFile(String url, String localpath)     at ScriptTask_f689e2ad4d3f481a87e3678e2d746c39.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()  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  13:52:31  Finished: 13:52:34  Elapsed:  3.183 seconds.  The package execution failed.  The step failed.

Any ideas what I'm doing wrong or what I should be trying? If I need to give any more info just ask and I'll respond. I'm sure the script 99% complete but just fails on some credentials/permissions issue on the server I think. :/

UPDATE: I can confirm the code will write to a local area (C:\Temp) and also it will write to a folder on a network share (\someserver\some folder with spaces in\etc) so the issue is shifted somewhat to writing to a sharepoint folder. We run SSRS in native mode, so has anyone got any ideas what could be causing the "Network path not found" error when writing to a sharepoint folder from a SQL Server agent job running through a proxy?

Also tried moving the files via a File System task, but even this fails. :( Any ideas what the issue could be?

Bump. Potentially answering my own question here but after some investigation, we found the Win2008 server looking at Sharepoint isn't running the WebClient service. This means WebDAV isn't installed so it can't 'see' the sharepoint libraries as a network path (or map a drive to it).. Going to install these components and report back. But if anyone has further info on this it would be appreciated! Bit like a blind man in a maze at night at the minute. :)

Answer 1

You know that too deliver to SharePoint, SSRS must be configured for SharePoint integration mode. This is quite opposite than the "standard" configuration.

but that's not even the main point. When I read your scenario, I thought about subscriptions, then you said that they are not an option. Why? But whats even worst is that you basically came up with an implementation that does what subscriptions do. You run a report on a timely basis and save it to a folder. I do this all the time, with subscriptions.

OK, I really don't know your full scenario but two thing are very important here:

1 - how long can the users cope with non-live data? I mean, you don't expect reports this big (at least you made them sound really big on your description) to be "live". I'm pretty sure you could cache them for a couple of hours. Your first user will experience the delay but the next will benefit from the previous execution (as long as if they have the same parameters)

2 - you could create a subscription to preload the cache. I advise you to read the Preloading the Cache paragraph from here This feature is especially useful if you want to cache multiple instances of a parameterized report where different parameter values are used to produce different report instances. (seem like your case)