"Exception has been thrown by the target of an invocation” from Script task

0

I'm receiving the following error when running a SSIS package that contains a Script Task, from a .bat file using DTEXEC. The .bat file is called from a Stored Procedure in MS SQL using "EXEC xp_cmdshell"

Error: Code: 0x00000001 Description: Exception has been thrown by the target of an invocation.

When I run the SSIS package from within Visual Studio, it works perfectly. When I run the .bat file manually, it works perfectly. The only time I receive the error is when I run the Stored Procedure.

Here is the script task:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics
Imports System.IO
Imports System.IO.FileInfo
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>
<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.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
    End Sub
    Public Sub Main()
        Dim url, destination, newname As String
        destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + Dts.Variables("FileName").Value.ToString + ".xpdf"
        url = "http://localhost/reportserver?/PDFExport/DEVOrderPDF&rs:Command=Render" + Dts.Variables("FileName").Value.ToString + "&rs:Format=PDF"
        SaveFile(url, destination)

        newname = Path.GetFileNameWithoutExtension(destination) + ".pdf"
        My.Computer.FileSystem.RenameFile(destination, newname)

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

I will add that when the Stored Procedure is ran, I see the file being created with the .xpdf extension, however the size is 0kb and unable to be opened when changing the extension to .pdf as it should be. It's almost like it partially runs, but doesn't complete when calling the SSRS url.

sql-server
visual-studio
reporting-services
ssis
asked on Stack Overflow Apr 5, 2018 by Peter

1 Answer

0

There are 3 things that will help you here.

  1. Don't eat the exception, it is your friend, you should return it.
  2. Always release you resources.
  3. Examine the SSRS error logs.

The SSRS error log will contain detailed information to help you fix the issue. The logs can be found at %ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles.

Fixing 1 will ultimately be the solution, however, you can't fix it if you cant see it.

Dim loFileStream = Nothing
Try
     Try
       loFileStream = New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
       loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
       loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
       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()
   Catch ex As Exception
       LogExceptionSomewhere("My process failed because of -->" + ex.ToString())
   End Try
Finally    
    If loFileStream<>Nothing Then    
        loFileStream.Close()
    EndIf
End Finally
answered on Stack Overflow Apr 5, 2018 by Ross Bush • edited Apr 5, 2018 by Ross Bush

User contributions licensed under CC BY-SA 3.0