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.
There are 3 things that will help you here.
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
User contributions licensed under CC BY-SA 3.0