SSIS脚本任务在Visual Studio 2010中不起作用，调用的目标引发了异常

Question

我正在使用SSIS脚本任务，但无论何时运行它，SSIS包都会失败，并给出以下错误：调用的目标引发了异常。

是否可能是因为我在Visual Studio 2008中使用了此脚本，并且我正试图在Visual Studio 2010中实现该包，所以出现了此问题。

这是我的代码：

enter code here ' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
<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

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' 
' To open Help, press F1.
Public Sub Main()
Dim file_stream As New FileStream(CType(ReadVariable("filepath"), String) + "AIR_FEE1.TRN", FileMode.Append)
Using w As New StreamWriter(file_stream, Text.Encoding.UTF8)
w.WriteLine("T|" + CType(ReadVariable("Count"), String))
End Using
Dim FName As String
Dim LFName As String
FName = CType(ReadVariable("filename"), String)
LFName = CType(ReadVariable("logfile"), String)
WriteVariable("StaticLogFileName", LFName)
WriteVariable("StaticFileName", FName)
Dim file_stream1 As New FileStream("StaticFileName", FileMode.Create)
file_stream.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
End Class

Answer 1

首先，"调用的目标抛出了异常">是在脚本任务执行期间发生错误时抛出的通用消息，请尝试调试代码以找到更精确的错误消息。

我认为您可以编写相同的脚本，而无需定义操作变量的函数：

Public Sub Main()
Dim file_stream As New FileStream(Dts.Variables("filepath").Value + "AIR_FEE1.TRN", FileMode.Append)
Using w As New StreamWriter(file_stream, Text.Encoding.UTF8)
w.WriteLine("T|" + Dts.Variables("Count").Value)
End Using
Dim FName As String
Dim LFName As String
FName = Dts.Variables("filename").Value
LFName = Dts.Variables("logfile").Value
Dts.Variables("StaticLogFileName").Value =  LFName
Dts.Variables("StaticFileName").Value =  FName
Dim file_stream1 As New FileStream("StaticFileName", FileMode.Create)
file_stream.Close()

Dts.TaskResult = ScriptResults.Success
End Sub

确保您已从脚本任务属性窗体中正确选择了ReadOnly Variables和ReadWrite Variables。

有用链接

• 在脚本任务中使用变量
• 3种方法-SSIS读写变量-脚本任务C#/VB.net
• 无法获取"；ReadWrite"；SSIS脚本组件中的变量值