locked
Use of user variable by Execute SQL Task causes validation error RRS feed

  • Question

  • I have an Execute SQL Task inside a Sequence Container.  The Sequence Container has two string variables in its scope, A1 and A2.  When the values assigned to these by the Sequence Container are concatenated they form a valid T-SQL statement.  I have used an expression in the Execute SQL Task to use the concatenated values of A1 and A2 as the SQLStatementSource.  If I write the expression as @[A1] + @[A2] the Execute SQL Task will execute successfully, but an error is listed stating 'Validation Error: Execute SQL Task: There were errors during task validation'.  Also, the valid T-SQL statement appears in the SQLStatementSource property.  If I write the expression as @[UserVar::A1] + @[UserVar::A2], which is what I would expect, I get a validation error when I try to build or debug the project.  It states "Error at Execute SQL Task:  The variable @[UserVar::A1] was not found in the variables collection.  The variable might not exist in the correct scope'

    Any ideas what this is about?

    Tuesday, September 13, 2011 4:53 PM

Answers

  • Hi Ken,

    The above mentioned task works quite fine for me - despite of the scope selected for the variables a) Package or b) Sequence Container.

    The only thing i noticed in your post is the syntax of the expression used. If its just a typo - i havent got your problem yet.

    It should be "@[User::A1]+ @[User::A2]" rather than "@[UserVar::A1] + @[UserVar::A2]".

     

    Seetha

     

    • Marked as answer by Mulak Wednesday, September 14, 2011 7:32 AM
    Wednesday, September 14, 2011 6:30 AM

All replies

  • Perhaps at the time the execution starts the values for one or both of the variables are not right/missing. This causes the validation error(s).

    The remedy thus would be in either assigning the proper values to the variables or turning the validation off for this component.


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, September 13, 2011 5:19 PM
  • Can you please copy and paste the contents of your 2 variables - A1 and A2
    Tuesday, September 13, 2011 5:47 PM
  • Check your variables scope by looking into Variables pane of BIDS.It should be either Package or Execute SQL Task to be able to access it within the Execute SQL task. Is it's not, try deleting and re-creating the variable. This time click anywhere on Control Flow pane or select Package in Properties pane before creating the variable.

    Cheers.


    http://thebipalace.wordpress.com

    Tuesday, September 13, 2011 6:04 PM
  • Thanks for these responses, but I am no further forward.

    I have now set up a project with just an Execute SQL Task inside a Sequence Container. 

    What is in the variables pane is:

    Name        Scope                                 Data type        Value

    A1             Sequence Container           String              Delete from dbo.

    A2             Sequence Container           String              CutDateTable

    I have tried setting DelayValidation for the Execute SQL Task to false, but the problem is still the same on running debug.

    I have also tried with the variables scoped at the package level, but this is just the same.

    Any further thoughts?

    Ken

    Wednesday, September 14, 2011 6:00 AM
  • Progress! And an obvious error, but still a question.

    When the reference to the variables is changes to @[User::A1] from @[UserVar::A1], the mechanism works.  I still get a validation error when it runs, but this is removed by setting delayvalidation to true.  I can appreciate that an expression will not provide a valid SQL command until execution, but does validation always have to be turned off?  I haven't noticed any reference to this.

    Ken

    Wednesday, September 14, 2011 6:28 AM
  • Hi Ken,

    The above mentioned task works quite fine for me - despite of the scope selected for the variables a) Package or b) Sequence Container.

    The only thing i noticed in your post is the syntax of the expression used. If its just a typo - i havent got your problem yet.

    It should be "@[User::A1]+ @[User::A2]" rather than "@[UserVar::A1] + @[UserVar::A2]".

     

    Seetha

     

    • Marked as answer by Mulak Wednesday, September 14, 2011 7:32 AM
    Wednesday, September 14, 2011 6:30 AM
  • Hi Ken,

    Guess i was few min late !! anyways, the expression can always be validated by clicking on the 'Evaluate Expression' which will provide results based on the values of the variables then. Errors with respect to syntax as in this case can be avoided.

    Seetha

     

     

    Wednesday, September 14, 2011 6:45 AM