Post reply

Using Variable as sqlcommand in DataReader

  • July 22, 2008 at 6:34 am

    #124217

    I'm wondering what the syntax is for using a variable in the sqlcommand property. I have tried referring to one of my variables numerous ways (@VariableName, User::VariableName, @[User::VariableName) and I continually get errors such as unexpected char: '@'.

    Secondly, eventually I would like to place an entire sql statement within the variable before I use it in my datareader. Is it more appropriate to set the variable as a string value and dynamically change the sql statement text via a SQL command or a script? Or is it more appropriate to set the variable as an expression and use it that way?

    Thanks so much for any help/advice.

    D

  • July 22, 2008 at 7:05 am

    #846375

    You cannot use a variable directly in the DataReader SQLCommand. You will need to use an expression on the data flow component. Just set the SQLCommand to your variable in the expression.

    It has been my experience that you should use expressions and expression variables before using a script task. Going to a script task is typically a last resort for me. It adds complication for error handling, makes the interactive mode with visual studio less useful, and is generally harder to follow.

  • July 22, 2008 at 7:25 am

    #846395

    Thanks Michael!

    I set the SQLCommand to my variable in the expression. It evaluated fine. However when I leave the expressions window, the sqlcommand property of the data flow task is blank. Is this ok, or do I need to add something here as well? Reason I ask is that I left it blank and now when I try to go into the datasource reader in the data flow I get the following warning:

    The component has detected potential metadata corruption during validation.

    Error at Data Flow Task [Source - Query [1]]: The SQL command has not been set correctly. Check SQLCommand property.

    Do you want to edit this component?

  • July 22, 2008 at 7:41 am

    #846408

    Hard-code a T-SQL statement into the SQL Command of the Data Reader. Preferably one that will run very quickly (TOP 1 helps). The expression will over-ride this when the package runs, but you will need a SQL command at design time to set up the data flow. Just make sure they return the same meta-data.

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

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