Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Working with variables in SSIS Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 1:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:02 AM
Points: 173, Visits: 469
Hi 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 Active
From my_table"

the error keeps on saying :Error near key word "then"
Post #1408213
Posted Thursday, January 17, 2013 2:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 1,207, Visits: 9,333
When 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:
Select Col1, Col2, Case when convert(date,col3) <= ?

Then 1

Else 0

End as Active
From my_table

Then add the parameter into the parameter mapping tab.

Have a look here for more info:

http://technet.microsoft.com/en-us/library/ms140355.aspx
Post #1408223
Posted Thursday, January 17, 2013 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:02 AM
Points: 173, Visits: 469
Hi 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".
Post #1408239
Posted Thursday, January 17, 2013 2:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 1,207, Visits: 9,333
You 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 1

Else 0

End as Active
From 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:

col3 &lt;= @[User::vEnd_Date] ? 1 : 0

Post #1408248
Posted Thursday, January 17, 2013 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:02 AM
Points: 173, Visits: 469
Howard,

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 column
Post #1408262
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse