Post reply

SSIS Assigning a value to a package variable

  • July 23, 2012 at 2:31 pm

    #264137

    Hi All

    Have a foreach loop that iterates through files. If an error it writes log and I simple want to keep a count of how many files failed. I created a package variable (package scope int32, value 0)

    In my error handler I have this (VB):

    Dts.Variables("User::ERR_ErrorCount").Value = Dts.Variables("User::ERR_ErrorCount").Value + 1

    I've checked the spelling and case several times and it's correct. (tried with and without "User::")

    Here's the error

    Error: 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.

    I've looke din BOL and online and the syntax seems to be correct

    It's driving me mad. Gotta be something real basic

    Any suggestions?

    Regards

    Mark

  • July 23, 2012 at 2:49 pm

    #1516478

    Hi Mark,

    Is your User::ERR_ErrorCount variable passed into your script as a ReadOnlyVariable or ReadWriteVariable.

    Thanks,

    Dave

  • July 23, 2012 at 2:50 pm

    #1516480

    Dave.Valentine (7/23/2012)

    Hi Mark,

    Is your User::ERR_ErrorCount variable passed into your script as a ReadOnlyVariable or ReadWriteVariable.

    Thanks,

    Dave

    Thanks Dave. Knew it was something really dumb!:-)

  • July 23, 2012 at 2:53 pm

    #1516484

    Any time, I have been there myself. 😀

  • September 12, 2013 at 3:53 am

    #1649632

    Thank you so much for this - schoolboy error but so easily done!:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply