Package variables

Question

Package variables

Animal Magic

SSChampion

Points: 13964
More actions
March 6, 2008 at 10:16 am

#124254

Hi All,
I am fairly new to SSIS and its features an am trying to do the following...
The first step is to run a script and then put the output into a variable (i have done this bit). I want to now - based on the output (its a count field, so im only bothered if its 0 or more than 0) - either run one task, or a different one.
Im just not sure how to say "if my variable is 0 then do this otherwise do that".
Any help would be appreciated before i pull out all my hair 🙂

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

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Add the two tasks to your control flow. Connect the two tasks to your initial step that initializes the variable. Now you can add a precedence constraint on both connections by double clicking on the connection. Select 'Expression' or 'Expression and constraint' as the evaluation operation. Set the expression to either '@[User::YourVariable] == 0' or '@[User::YourVariable] > 0' depending on the connection you're editing. You will see a small 'fx' icon beside the connection in the diagram to indicate that the connection is constrained.

Peter

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

You should set ResultSet to Single Row (as your query only returns a single count). In addition, alias the count(*) in your query, i.e. SELECT COUNT(*) AS JMCnt FROM .... Then, on the Result Set tab, add JMCnt as the Result Name with Variable Name User::JMCnt. Define User::JMCnt as of type Int32 (or any suitable integer type).

Good luck,

Peter

Animal Magic SSChampion Points: 13964 More actions · Answer 3

Hi Peter,

Thanks for your reply. I see now where i can enter my variables. It has however thrown up another issue where i have obviously gone wrong in my first step as it wont let me use my parameter in an expression.

The first step in my SSIS package basically build a temp table and then does a select count(*) which is what i am trying to capture. In the result set option of the task properties i have "full result set" and then on the result set tab i have resultname = 0 and variable = user::JMCnt, when i defined this variable it was of value type Object.

I think what i need to do is somehow not use this method and instead use the parameter mappings?? if this is the case do i need an output or a returnvalue type? im guessing that returnvalue are only used in SP's?

Sorry, thought i was half way through my problem here but it seems im still right near the start 😛

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Peter - your a legend, i cant thank you enough!!

Thanks for your help and perseverance.

John