Working with variables in SSIS

Question

Working with variables in SSIS

Trybbe

Hall of Fame

Points: 3967
More actions
January 17, 2013 at 1:42 am

#267115

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"

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

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

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

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".

HowardW One Orange Chip Points: 29109 More actions · Answer 3

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 <= @[User::vEnd_Date] ? 1 : 0

Trybbe Hall of Fame Points: 3967 More actions · Answer 4

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