InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 21:08:11 GMT20http://www.sqlservercentral.com/Forums/Topic1408213-391-1.aspxHoward,THANK YOU VERY MUCH. I was missing the quotes in my method. used that line you provided, it's working like a charm. now I'm going to try that other one where I use a derived columnThu, 17 Jan 2013 03:11:44 GMTTrybbehttp://www.sqlservercentral.com/Forums/Topic1408213-391-1.aspxYou should be able to get that working with your original approach if you just add in the single quotes and set the data flow source to your variable. e.g.:"Select Col1, Col2, Case when convert(date,col3) <= '"+(DT_WSTR, 40) @[User::vEnd_Date] +"'Then 1Else 0End as ActiveFrom my_table"But if you just need this Active flag as above, you could also avoid passing variables into the SQL entirely by using a very simple Derived Column Transformation in the data flow. Just bring Col3 in as a column in the select, then add a Derived Column, call it Active and put an expression like the below in:[code="sql"]col3 <= @[User::vEnd_Date] ? 1 : 0[/code]Thu, 17 Jan 2013 02:46:22 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1408213-391-1.aspxHi Howard,Thanks for the feedback, I just tried your and it works fine. I would like to do this through a data flow task for so that I can move the data to a staging table for further transformation. how do I go about getting that working?Here's my current flow:Using execute sql task, I populate the variable (vEnd_Date). quotes are also taken into account in this query as the value is return as a string.once this is done this is where I want to use this variable in a query on my OLEDB source.I tried using the sql task and set the resultset option to full result set but I get the error "[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".Thu, 17 Jan 2013 02:26:05 GMTTrybbehttp://www.sqlservercentral.com/Forums/Topic1408213-391-1.aspxWhen that's converted to a string, you'll be missing the required single quotes from around your date (as you're passing it as a string).You're better off passing the variable directly to an Execute SQL Task, then you don't need to convert it to a string and worry about the single quotes you're missing as you're passing the parameter in as its original type.E.g. assuming you're using OLEDB, create an Execute SQL Task with the following in it:[code]Select Col1, Col2, Case when convert(date,col3) <= ?Then 1Else 0End as ActiveFrom my_table[/code]Then add the parameter into the parameter mapping tab.Have a look here for more info:[url]http://technet.microsoft.com/en-us/library/ms140355.aspx[/url]Thu, 17 Jan 2013 02:05:49 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1408213-391-1.aspxHi pls assist.I have the following query inside a variable which I'm trying to pass a date as a value but it keeps giving me an error.I have two variables, vSQL to contain the query and vEnd_Date for the date."Select Col1, Col2, Case when convert(date,col3) <= "+(DT_WSTR, 40) @[User::vEnd_Date] +" Then 1 Else 0 End as ActiveFrom my_table"the error keeps on saying :Error near key word "then"Thu, 17 Jan 2013 01:42:43 GMTTrybbe