0

I am calling SSRS from SSIS Script task and stores the report in PDF format.

Below is my code in Script task:

 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.Timeout = 7200
        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
End Sub

Public Sub Main()
    Dim url, destination, Todaydate, FolderName, DestinationError As String
    Try

        Todaydate = Dts.Variables("TodayDate").Value.ToString

        FolderName = Dts.Variables("FolderName").Value.ToString

        destination = Dts.Variables("DestFolder").Value.ToString + "\" + Todaydate + "\" + FolderName + "\" + Dts.Variables("CurrentReport").Value.ToString + "_" + Dts.Variables("CurrentParamID").Value.ToString + "_" + Format(Now, "yyyyMMdd") + ".pdf"
        url = "http://server-name/ReportServer?/ReportPath/AUTOMATED/" + Dts.Variables("CurrentReport").Value.ToString + "&rs:Command=Render&Param=" + Dts.Variables("CurrentParamID").Value.ToString + "&rs:Format=PDF"   

        Dts.Variables("User::GeneratedPDFpath").Value = destination

        SaveFile(url, destination)

    End Try
    Dts.TaskResult = ScriptResults.Success

End Sub

This code works fine and generates report in PDF. But, sometimes it generates PDF file with size of 0 KB. When that PDF is opened, it displays an error for Corrupted File.

P.S. I have used Execute SQL task which stores a resultset. Using that resultset in Foreach Loop container, it initially created folder for today's date and stores the respective PDF in folder.

Would appreciate if someone could provide some help on this.

Kinchit Dalwani
  • 398
  • 4
  • 19
  • Why are you calling SSRS from SSIS? Why don't you use an SSRS Subscription? – Tab Alleman May 08 '18 at 13:21
  • Your catch block is not doing anything, so errors will silently be ignored. Could you log the error messages and see if there is a clue in there? – Mark Wojciechowicz May 08 '18 at 13:48
  • I have multiple SSRS reports to be generated and the resultset would be will be different based on day. This whole needs to be automated. so! @TabAlleman – Kinchit Dalwani May 09 '18 at 10:08
  • I do have included the error portion. But job/package doesn't fails. Its just that it execute successfully sometimes generating 0 KB file. @MarkWojciechowicz – Kinchit Dalwani May 09 '18 at 11:03

0 Answers0