none
Variable not found when package executes.".The element cannot be found in a collection."

    Question

  • This is a very simple tutorial demonstrating script task. 2 variables

    intvar int32 5

    strvar string 0

    And a script task with this code


    If Dts.Variables("intVar").Value > 10 Then
                Dts.Variables("strVar").Value = "Big"
            Else
                Dts.Variables("strVar").Value = "Small"
            End If

            MsgBox(Dts.Variables("strVar").Value)

    below is the error msg

    SSIS package "Package.dtsx" starting.
    Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
     ---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
       at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
       at ST_ce37649a37c146518fa69eff106c6625.vbproj.ScriptMain.Main()
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
    Task failed: Script Task
    Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) 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 "Package.dtsx" finished: Failure.
    The program '[2824] Package.dtsx: DTS' has exited with code 0 (0x0).

    Monday, June 17, 2013 8:59 PM

Answers

All replies

  • Must be a typo in variable name (not matching declaration)

    Arthur My Blog

    • Marked as answer by ghw123 Monday, June 17, 2013 10:35 PM
    Monday, June 17, 2013 9:11 PM
  • Thanks, it was typo script it was strVar variable is strvar.
    Monday, June 17, 2013 10:34 PM
  • Must be a typo in variable name (not matching declaration)

    Arthur My Blog

    then why does the genius ssis tool not tell me which variable is creating the problem ? :( this is so frustrating. sometimes i feel its better to avoid this etl sh!t and just use a programming language instead.

    Saturday, January 04, 2014 3:43 AM
  • Must be a typo in variable name (not matching declaration)

    Arthur My Blog

    there is one more reason for the problem - 

    I had the code

    DataColumn dc = null;
    dc = myDataTable.Columns["PERSON_NAME"];

    The column to be fetched was NAME and not PERSON_NAME...someone changed it in the db :(

    Saturday, January 04, 2014 3:51 AM
  • Hi All,

    another reason could be improper access of package variable value in the script task like in message box.

    Regards,

    Durga.

    Thursday, January 30, 2014 11:28 AM