Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with variables in SSIS


Working with variables in SSIS

Author
Message
Trybbe
Trybbe
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 482
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"
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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
Trybbe
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 482
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
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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
Trybbe
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 482
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search