vba SSIS 使用脚本任务为变量赋值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18715705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS Assigning value to Variable using Script Task
提问by jeastham1993
I am trying to assign a value to a variable from a flat file in an SSIS package. The script i am using is below
我正在尝试从 SSIS 包中的平面文件中为变量赋值。我正在使用的脚本如下
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
#End Region
' This is the class to which to add your code. Do not change the name, attributes, or parent
' of this class.
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Dim StartDate As String
Dim FinishDate As String
Public Overrides Sub PreExecute()
MyBase.PreExecute()
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()
Me.ReadWriteVariables("StartDate").Value = StartDate
Me.ReadWriteVariables("FinishDate").Value = FinishDate
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
StartDate = Row.StartDate
FinishDate = Row.FinishDate
End Sub
End Class
The problem Im having is that sometimes the fields from the flat file could be blank, when they are blank I get an error:
我遇到的问题是，有时平面文件中的字段可能为空，当它们为空时，我会收到错误消息：
The type of the value being assigned to variable "User::StartDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
分配给变量“User::StartDate”的值类型与当前变量类型不同。变量在执行期间可能不会改变类型。变量类型是严格的，Object 类型的变量除外。
Does anyone have any ideas what could be causing the error?
有没有人有任何想法可能导致错误？
回答by Matt Gibson
I'm guessing that's because the value will be null when you're doing the assignment. SSIS doesn't support null values for its variables.
我猜这是因为当你做任务时，这个值将为空。SSIS 不支持其变量的空值。
What value do you wantyour variable to have when there are no values in the file? And is the problem occurring when there are no rows in the file, or when there isa row but the columns are empty for StartDate and EndDate?
当文件中没有值时，您希望变量具有什么值？当文件中没有行时，或者当有一行但 StartDate 和 EndDate 的列为空时，是否会出现问题？
If an empty string is what you want, try changing:
如果您想要一个空字符串，请尝试更改：
Dim StartDate As String
Dim FinishDate As String
to
到
Dim StartDate As String = ""
Dim FinishDate As String = ""
...to guard against the situation where there are no rows. In that case, your ProcessInputRow will never run, so you need to set up the strings as empty from the very beginning.
...防止出现没有行的情况。在这种情况下，您的 ProcessInputRow 将永远不会运行，因此您需要从一开始就将字符串设置为空。
For the situation where there are rows, you want to guard against setting the values to be null in ProcessInputRow:
对于有行的情况，您要防止在 ProcessInputRow 中将值设置为 null：
If Not Row.StartDate_IsNull Then
StartDate = Row.StartDate
End If
If Not Row.EndDate_IsNull Then
EndDate = Row.EndDate
End If
Though if you didn't tick "Retain null values from the source as null values in the data flow" on the Source of the file you're using, they should be coming through as empty strings anyway...
尽管如果您没有在您使用的文件的源上勾选“将源中的空值保留为数据流中的空值”，它们无论如何都应该作为空字符串出现...
回答by shree.pat18
If empty start and end dates are a valid scenario, you can use the 'ColumnName'_IsNull properties to guard against empty fields. In your code, you can add something like this:
如果空的开始日期和结束日期是有效的方案，您可以使用 'ColumnName'_IsNull 属性来防止出现空字段。在您的代码中，您可以添加如下内容：
if (String.IsNullOrEmpty(Row.StartDate))
{
Row.StartDate_IsNull = true;
}
else
{
StartDate = Row.StartDate;
}
