开发者

Odd Issue Setting a User Variable Data Type For Use In Scripting Task

This is an extension of this question. I have set up a User variable called "Type" and set its data type to Int16. In my Scripting task, depending on the outcome of a test of the contents of another User variable called "fileName", I am setting the value of User::Type to one of 3 values. All of these values are integers. The default value of User::Type is 0 and the 3 new values are 1, 2, and 3. The Script Task is the first element inside a Foreach Loop Container in my Control Flow. The code for the Script Task:

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    ' Created: Zack Bethem – AmberLeaf
    Public Sub Main()
        Dim fileName As String
        Dts.Variables.Item("User::Type").Value = 0
        If Dts.Variables.Contains("User::fil开发者_如何学JAVAeName") = True Then
            fileName = CStr(Dts.Variables.Item("User::fileName").Value)
            'System.Windows.Forms.MessageBox.Show(fileName)
            If fileName.Contains("0074000") = True Then
                Dts.Variables.Item("User::Type").Value = 1
            ElseIf fileName.Contains("0072000") = True Then
                Dts.Variables.Item("User::Type").Value = 2
            ElseIf fileName.Contains("0022000") = True Then
                Dts.Variables.Item("User::Type").Value = 3
            End If
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub
End Class

The error I am getting is:

SSIS package "ACS_ALL_Import .dtsx" starting.
Error: 0xC001F009 at ACS_ALL_Import: The type of the value being assigned to variable "User::Type" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::Type" cannot be applied.
Warning: 0x80019002 at ACS_ALL_Import: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ACS_ALL_Import .dtsx" finished: Failure.

If I change the data type of User::Type to any other item and also update the script to assign that kind of data type I still get this error. I have deleted this variable reference from the Scripting Task and deleted the variable itself from the package and then re-added it and still I am getting the same error.

Until I set the variable type to String. Then it worked. Is there some sort of other data type conversion that occurs behind the scenes that I am not able to see?


just seen your comment regarding this on the previous question. This line in your error:

Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::Type" cannot be applied.

indicates to me that may the issue is not with your Script task, but with the variable mapping in the For Each Loop Container. Are you setting the Type variable to a string, possibly erroneously, in that task?

Odd Issue Setting a User Variable Data Type For Use In Scripting Task


i had a similar situation. Just define another variable as filename for the 'for each loop',you dont need to use 'filename' in the script. define another package variable of int32 as 'count' for use in your script. This works!


Same problem, my mistake was that I was mapping too many columns in my Foreach loop as per grapefruitmoon's suggestion. I also had the wrong number of columns in my query so I had the wrong column name mapped to the variable I was using for my file name. Once I made the column number change in the mapping and removed the unrequired variables. The package worked.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜