0

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::fileName") = 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?

Improve this question

3 Answers 3

Reset to default
1

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?

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

No, I had not set the type in the Script Task explicitly. I did assign a value like: Dts.Variables.Item("User::Type").Value = 1 Which I thought would infer that it is an INT. But that was not the case. When I was using other User variables (3 of them, boolean - as seen in my other question) it was still giving the data type error. Does the Script Task always output a String type, maybe? This is my first trip down the SSIS rabbit hole deeper than just a Foreach Loop and some flat files.
I was thinking that the error was not in the Script Task at all; what you have there should work. But is the Type variable being amended in the Variable Mapping tab of the For Each task, as that seems to be what the error is indicating? (see the image added to the answer above)
I created the User::Type variable by making sure the Control Flow was selected (clicking outside of my Foreach in the blank space) and then using the Variables slide-out tool menu to enter the name and data type as well as the default value. I did not create the User::Type variable using the Foreach Loop Editor but it is in the Variable Mappings window and set to Index = 0 (I cannot edit the variable from here).
Can you remove the Type variable from the mapping in the For Each task? I'm assuming you don't need to map it there, and that you're mapping a variable for the file path?
I am mapping User::fileName in the Foreach Loop. If I remove the User::Type from the Variable Mapping in the Foreach Loop (it is still in the "global" variable slide-out menu) I get an error that the User::Type variable is not accessible in the scope of the Script Task.
|
0

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!

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.