StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOdd Issue Setting a User Variable Data Type For Use In Scripting Task
text
Body
copied!<p>This is an extension of <a href="https://stackoverflow.com/questions/5108291/conditional-data-flow-based-on-source-csv-filename">this</a> 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:</p> <pre><code>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 </code></pre> <p>The error I am getting is:</p> <pre><code>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. </code></pre> <p>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.</p> <p><strong>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?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload