Variables in Execute SQL task

Question

Variables in Execute SQL task

herladygeekedness

SSCertifiable

Points: 5878
More actions
July 26, 2011 at 3:24 pm

#242007

Should be simple:
I have a pkg variable, @lclValOn, that determines whether or not validation is enabled to fail a package (validation occurs regardless, I just need to check if failure is an option :-P] ).
I need to use the value in this variable in the Execute SQL component. The thing is, I don't seem to be able to directly access the value (or, more likely, I don't know how to appropriately reference it - have tried the various syntaxtical ways I know and a few I made up from desperation).
I mapped it in parameters tab and have named it 0 (using OLEDB) and given it friendlier names also.
What I need to do is send an email based on teh value of that variable.
if @lclValOn = 1 send the email that refers to validation results
if @lclValOn = 0 send the email that ignores validation results
Am I able to use pkg variables in this manner or are they really only available as "criteria holders" (ie. Select * from TheTable where Whatever = ?)
I'm not married to this process and can part out the pieces to different components. I saw some references to using Expressions tab but there isn't a property setting that this impacts so abandoned that idea.
Anyone have a hint for me??

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

To use variables in an Execute SQL Task, you need to map the package variable in the parameters tab to an OLE DB parameter. From your explanation I see you already did that. Just name it 0 as you did earlier.

In the SQL statement, you need to do something like this:

DECLARE @myVar BIT;

SET @myVar = ?;

IF @myVar = 1 ...

The question mark is a placeholder for an OLE DB parameter. These placeholders are replaced by the ordinal position, meaning that the first question mark will be replaced by parameter 0, the second question mark by parameter 1 and so on...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246977 More actions · Answer 2

What I need to do is send an email based on teh value of that variable.

You can use precedence constraints to control which logical path an SSIS package follows. So a send mail task preceded by an appropriate constraint should give you this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 3

Awesome help, thank you!

The precedence branching comes later and it relies on this variable and one other and works beautifully (now that I am accustomed to the Expression Syntax).

Until a change made yesterday (ValidationEnabled doesn't mean what one would assume, it means, "validation is enabled to fail the job" and validation will always occur), it was a simple matter to send the email with failed records or an email that says all is groovy. But I needed another tracker to determine Enable + Fail, Disable + Fail, and the Enable + Pass, Disable + Pass. Basically, the possible outcomes doubled.

I thought about re-routing the whole thing but am soooo close to being done with the package, that stupid variable I couldn't access was the only holdup!

Now I have to go to a meeting on something unrelated but will hopefully be giving the var access a shot in an hour or so.

THANK YOU SO MUCH!

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 4

Koen, worked like a charm, thank you!

I had not considered layering a variable on a variable, but I appreciate adding this trick to my toolkit!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

herladygeekedness (7/27/2011)
Koen, worked like a charm, thank you!
I had not considered layering a variable on a variable, but I appreciate adding this trick to my toolkit!

In most cases you can use the question mark placeholder directly in your query, but I find the variable layering more readable and maintainable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 6

My problem is that it wasn't being used in a query, so not a normal parameter in that sense.

I do like the maintainability, declaring the bugger right at the top makes it clear what's happening in the script. Hardly matters to me that it's a bit of a two-step.