I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||
A variable can be used in an expression.
A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.
The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
These topics are well documented in Books Online. Try these for a start-
DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."
Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)
|||Ok, thanks Darren. I am gonna try it out to see if it works...Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||
I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:
"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"
Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.
Adrian|||Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.
Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)
-Jamie
|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.Daren
|||
Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.
Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])
This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:
Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.
So, there's a bit of a disconnect here somewhere...
|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx
It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.
-Jamie
|||Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.
