Manipulating SSIS variables in script task

  • jo_square

    Old Hand

    Points: 344

    I have a SSIS package with a script task which I would like to calculate the sum of two int32 variables(say "a" and "b", package level var) and store in variable "c". I'm not familiar with VB/.NET and I follow some books' example and wrote the following code:

    -------

    If ((Dts.Variables.Contains("a") = True) And (Dts.Variables.Contains("b") = True)) Then

    Dts.Variables("c").Value = Dts.Variables("a").Value + Dts.Variables("b").Value

    End If

    -------

    The IDE told me that "Option Strict On prohibits operands of type Object for operator '+'". I don't know also how to set the property of a variable to be readable/writable. Could someone tell me what should I amend to make the script work? Thank you!

  • Todd Engen

    SSCrazy Eights

    Points: 9869

    The variables would added to the ReadWriteVariables option which is on the same tab where you clicked on Design Script... of the Script Task.

  • jo_square

    Old Hand

    Points: 344

    Thanks. I think I've already added those variables to the properties (using the format of "User::a", "User::b", etc, w/o quote), is it right? However, it still shows that error message. Would you mind to teach me the necessary steps to make it work? Or point out the steps that maybe missing? Thank you!

  • Todd Engen

    SSCrazy Eights

    Points: 9869

    First, the variables would be specified as "a,b,c" (without the quotes).

    Second, "Option Strict" is a compiler option that's there to help you from introducing logic errors.

    For example, set the type for variable "a" to Boolean and set it to true. Set the type for variable "b" to Int32 and set it to 3. Open the script and add "Option Strict Off" at the top and then run the task. With checking off it will not only run but give you an incorrect result.

    To make this work, explicitly convert the variables before adding them.

    CInt(DTS.Variables("a").value) + CInt(DTS.Variables("b").value)

  • jo_square

    Old Hand

    Points: 344

    Got it! Thank you! 😀

  • emna

    Newbie

    Points: 3

    Bonjour,

     

    pouvez vous m'aidez slvp

     

     

    DEST = Dts.Variables("User::DestPath").Value & "\" & Replace(Dts.Variables("User::FileName").Value, "/", "") & ".xlsx"

    ce bout de code bloque l’exécution de package

    j ai pas compris l utilité de cet opérateur  &

    Merci d avance

  • emna

    Newbie

    Points: 3

    Sorry i have to write in english.

    I have a problem in the exceution of the package.

    The package find a problem with this line

    DEST = CType(Dts.Variables("User::DestPath").Value, String) & "\" & Replace(CType(Dts.Variables("User::FileName").Value, String), "/", "") & ".xlsx"

    i was make a cast but there in always the same problem.

    he does not accetp the operator &

    Thanks

     

  • emna

    Newbie

    Points: 3

    this is the error

    i made a cast to string

    but he does not accept &

     

    thx a lot

    Attachments:
    You must be logged in to view attached files.

Viewing 8 posts - 1 through 8 (of 8 total)

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