none
How to Set variable value on Script Component & use those values for SQL In clause

    Question

  • Hi All,

    I'm having ssis package which is developed BIDS 2008.In my data flow task i'm using script component and inside script component i'm wiritng some values ( ex :- "R02411" + "," + "R02410" + "," + "R02409" ) to my string valriable call "DocNos".Then finally i've sql task which uses some other varible to get sql and execute it . sql statement is like this in side the variable.

    "UPDATE       dbo.BillDetail SET IsExported = 1 WHERE (DocNo IN (" + @[User::DocNos]+ ")")

    The problem is even if i set single or multiple value to my variable call "DocNos" ,it wont compile it as value for "Where IN " clasue,it says "Invalid column name 'R02411' ".In my understanding it'll treat as column name but not as value.I suspect this is due to variable value assgin problem.bcos its working fine when i hard code some value to the "DocNos" varibale.This is my varibale assign part..

    public override void PostExecute()
        {
            base.PostExecute();
    
            // Lock the variable for write
            VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
            variableDispenser.LockForWrite("User::BillDetailDocNos");
    
            IDTSVariables100 vars;
            variableDispenser.GetVariables(out vars);
    
            // Set the variable
            vars["User::DocNos"].Value = "R02411" + "," + "R02410" + "," + "R02409" + "," + "R02408" + "," + "R02407";
    
            // Unlock the variable
            vars.Unlock();
            /*
              Add your code here for postprocessing or remove if not needed
              You can set read/write variables here, for example:
              Variables.MyIntVar = 100
            */
        }

    So any body having idea of whats the problem...


    Thanks & Regards, Dimuthu


    Tuesday, July 02, 2013 1:46 AM

Answers

  • Hi Dimuthu,

    String in the sql are treated different then c#, There you need to enclose them in single quote not double quotes.

    Please change this line in your script task:

     // Set the variable
            vars["User::DocNos"].Value = "'R02411','R02410','R02409','R02408','R02407'";

    Regards Harsh


    Tuesday, July 02, 2013 4:27 AM