none
Using variables in a data reader source

    Question

  • Greetings SSIS friends,

    I have tried using a sql command for my data reader source. I added the following expression to my datareader source :

    "select * from result where result_id > " + @[max_result_id]

    but I get the following error message :

     

    The SQL command has not been set correctly. Check SQLCommand property.

    I then got rid of the variable (defined at package level) and replaced it with a string like so :

    "select * from result where result_id > " + "123456"

    but I still get the same error message!

     

    What am I doing wrong?!

     

    Please advise.

    Wednesday, February 28, 2007 2:01 PM

Answers

  • I think you need to provide a query in the SQLCommand property directly in the DataReader component; even if you are using an expression to get the query at run time. The reason for that is that the designer needs metadata to validate it.

    Use a query like:

                select * from result where result_id > 123456

     

    Just to make it trhough the validation at design time; then the expression will do the trick at run time

    Wednesday, February 28, 2007 3:37 PM
    Moderator

All replies

  • Where are you exactly placing the expression?

    You should place it in the SQLCommandProperty of the data reader via expression. For that you have to go to the ControlFlow; then properties, then expressions. You should be able to see a property like <DatareaderName>.SQLCommnad.

    Do not type the expression directly in the SQLCommandExpression via DatReader editor.

    Wednesday, February 28, 2007 2:08 PM
    Moderator
  • Hi Rafael,

     

    That's what I did but it still doesn't work.

    1) I select the data flow task.

    2) I click outside of the DataReader Source

    3) I select the Expressions in the properties of my data flow task.

    4) I choose the [DataReader Source][sqlCommand] property then I add my expression. The expression evaluates but then when I close it I get the above error!

     

     

    Wednesday, February 28, 2007 2:12 PM
  • You should place it in the SQLCommandProperty of the data reader via expression. For that you have to go to the ControlFlow; then properties, then expressions. You should be able to see a property like <DatareaderName>.SQLCommnad.

    You mean the DataFlow not the ControlFlow surely?

    Wednesday, February 28, 2007 2:14 PM
  • Hu! If you say that the expression gets evaluated properly; that is weird!

    Is @[max_result_id] variable string type? (I guess it is as the expression evaluates correctly; but just in case)

    The other suggestion would be to make the query is properly formed. Evaluate the expression and then take the result to a native sql tool and make sure it runs.

     

     

     

    Wednesday, February 28, 2007 2:24 PM
    Moderator
  • I Meant CONTROL FLOW. Being in the control flow; select the data flow task and go to the properties of the data flow task....
    Wednesday, February 28, 2007 2:25 PM
    Moderator
  • That's what I've done though. I still don't understand why it doesn't work
    Wednesday, February 28, 2007 2:27 PM
  • Did You do this?

     

    Is @[max_result_id] variable string type? (I guess it is as the expression evaluates correctly; but just in case)

    The other suggestion would be to make the query is properly formed. Evaluate the expression and then take the result to a native sql tool and make sure it runs.

     

     

    Wednesday, February 28, 2007 2:33 PM
    Moderator
  • Is @[max_result_id] variable string type? (I guess it is as the expression evaluates correctly; but just in case)

    The other suggestion would be to make the query is properly formed. Evaluate the expression and then take the result to a native sql tool and make sure it runs.

     

    Hi Rafael,

    I cast my variable to string anyway. BUt even when I tried a simple select (without using a variable) I still can't get it to work. Is there a setting I am missing in my datareader source?!

     

    Wednesday, February 28, 2007 2:39 PM
  • Hi Rafael,

    For some reason, SSIS is expecting me to have something in the SqlCommand in the datareader source component properties windows. How can I get it to ingore that and take the sql code in the expression windows?!

    I am really confused now.

    Wednesday, February 28, 2007 3:01 PM
  • Wednesday, February 28, 2007 3:17 PM
    Moderator
  • I think you need to provide a query in the SQLCommand property directly in the DataReader component; even if you are using an expression to get the query at run time. The reason for that is that the designer needs metadata to validate it.

    Use a query like:

                select * from result where result_id > 123456

     

    Just to make it trhough the validation at design time; then the expression will do the trick at run time

    Wednesday, February 28, 2007 3:37 PM
    Moderator
  •  

    Afraid not. The porblem is, the datareader source requires a sql comman so how do I override this?! How do I make it use the expression without adding a sqlcommand in the datareader source component?

     

    I am really really really confused.

    Wednesday, February 28, 2007 3:39 PM
  •  dreameR.78 wrote:

    Afraid not. The porblem is, the datareader source requires a sql comman so how do I override this?! How do I make it use the expression without adding a sqlcommand in the datareader source component?

    I am really really really confused.



    See Rafael's post.  You need to add a sql command in the GUI of the datareader source.  It will be overridden by the expression you add.
    Wednesday, February 28, 2007 3:55 PM
    Moderator
  •  dreameR.78 wrote:

     

    Afraid not. The porblem is, the datareader source requires a sql comman so how do I override this?! How do I make it use the expression without adding a sqlcommand in the datareader source component?

     

    I am really really really confused.

    See my previous post (bellow)....basically you need both; the harcoded query tomake it during design time; then at run time that would be override by the expression

    Wednesday, February 28, 2007 3:57 PM
    Moderator
  • dreameR78,  Did you ever get this to work.  I am having the same problem.  When I place sql in the expression editor for [DataReaderSource].[SqlCommand] property it gives me an error, even without using a variable.  The same sql will run thru native client and it will also run fine when I place it in the Sql Command under the DataReader Source properties directly.  I want to place it in the expression editor so I can add a variable.
    Saturday, April 14, 2007 12:01 AM
  •  mj wrote:
    dreameR78, Did you ever get this to work. I am having the same problem. When I place sql in the expression editor for [DataReaderSource].[SqlCommand] property it gives me an error, even without using a variable. The same sql will run thru native client and it will also run fine when I place it in the Sql Command under the DataReader Source properties directly. I want to place it in the expression editor so I can add a variable.


    Did you also add a valid sql command in the normal GUI box for it?  You need to do that also, even if you are going to use expressions.
    Saturday, April 14, 2007 1:08 AM
    Moderator
  • Hi All,

    Many thanks for all the posts above - they've helped me move forward but I'm having a few probs:  Basic package structure is:

    1)  Use Script Task in Control Flow task to set a variable containing an MDX command, say @[user::mdxcommand]
    2)  In the Properties of the Data Flow task, have gone to Expressions and set [DataReader Source].[SqlCommand] to @[user::mdxcommand]
    3)  Within the the Data Flow task, in the SqlCommand property of the DataReader source has a basic MDX query that I know evaluates in a native SQL query tool.....as prescribed above to force the task to build

    ...so far so good


    ...but the output from the DataReader flows into a Data Conversion task, and on to be written in to a table.  The problem is the GUI only gives me the output columns of the "placeholder" query from step 3 above - I just can't see how to get the columns that are output by the evaluated expression at run time.

    Please, please, please can anyone help - I'm about to go a bit nuts here!!

    Thank you!
    Monday, April 21, 2008 1:36 PM
  • You don't need to put in a bogus sql string.  You can either 1) input the @[user:Tongue TiedqlVariable] you created, or at runtime 2) BIDS will populate the filed with the expression


    Tuesday, July 15, 2008 8:36 PM
  • Hi All,

    Select Data Flow Task (DFT) Under which you have used Data Deader Source-->Right Click-->Properties--> In Properties Window-->Go To Expressions--> Click on ...-->New Window (Property Expression Editor) will open-->Under Property column Select ([DataSourceReader].[SqlCommand]) & Paste your query "SELECT * FROM EMP_TABLE WHERE EMP_ID >TO_NUMBER('"+(DT_WSTR,10)@[User::V_EMP_ID]+"')" under Expression Column Or Click on ... , a New window (Expression Builder) will open & you can Paste your Query Under Expression Section & Click on Evaluate Expression Tab to check the Syntax of your Expression.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    • Proposed as answer by S Kumar Dubey Wednesday, March 21, 2012 7:25 AM
    Wednesday, March 21, 2012 7:24 AM