A Conditional Component when true & then execute a Task

Question

Post reply

A Conditional Component when true & then execute a Task

Ismail Cassiem

SSC-Addicted

Points: 484
More actions
September 11, 2007 at 7:08 am

#153814

Hi,

I am looking for A Conditional Component when true & then execute a Task

eg. IF = Y then execute task else not

I'm not to sure. Something like a

A Record in a query

DTS Task

DTS Parameter

Regards

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Lookup "IF" in Books Online ("Transact-SQL HELP" in Query Analyzer) to find more about the following example (also from Books Online)...

IF (@ErrorSaveVariable <> 0)
BEGIN
   PRINT 'Errors encountered, rolling back.'
   PRINT 'Last error encountered: ' +      CAST(@ErrorSaveVariable AS VARCHAR(10))
   ROLLBACK
END
ELSE
BEGIN
   PRINT 'No Errors encountered, committing.'
   COMMIT
END
RETURN @ErrorSaveVariable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 2

Hi, thanks for the reply.

We execute a list tasks (being DTS packages, SSIS, copying of ascii files to network) etc. one after another

The problem is that it's unstable were the task will execute based on a previous task that might have encounted errors so we only really notice once a user complains or on arrival in the mornings then it's to late.

The idea that we want to incorporate is like a checklist were it execute a job goes back to the checklist(?) sets it's flag or ? and then on true/flag execute the next task.

Regards

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

That's a whole lot different than what you asked... but somethin similar to what I posted will still work. Just set some variables in the process. Have you read about this in BOL, yet?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 4

Something like a electronic checklist:

1. Task Sales : (with a tick, indicating successfull)

Now that task is green tick execute Task2

2. Task2

Some other software or so that can relate with SSIS or DTS then return to a value or so then indicate on the software checklist that is done then continue to next task.

The reason being is that we can open the electronic checklist & to also view the checklist & indicate everything was successfull if not indicate the red ticks(unsuccessfull).

Regards

S. Kusen SSChampion Points: 10924 More actions · Answer 5

I would set up a stand-alone table with these columns: tick1, tick2, tick3, etc

Then, when you're external process runs, set the tick equal to green or red or whatever other values u want to check.

In the ssis package, set up package variables to store the values for these ticks.

Step 1 - ExecuteSQL task that has a result set of green/red and associates with a particular variable

--set constraint and expression on green arrow between this and the next ssis task; make the expression check for the value of the tick

Step 2 - Next SSIS task fires or doesn't get touched

Example:

Step 1:

ExecuteSQL: declare @value1 varchar(10)

if exists (select tick1 from table1 where tick1 = 'green')

begin

set @value1 = 'green'

end

else

begin

set @value1 = 'red'

end

On the executesql task properties, map the result set to single row and map the result set to the package variable that u created that corresponds to that tick.

Add Precedence between step 1 and 2, change the properties of the precedence to expression and constraint. for the expression, enter [ @packagevariablename == 'green' ] without the brackets. change packagevariablename to your variable name. For the constraint, choose Success.

That will evaluate whether the execute sql task was successful and check the value that the other application wrote to Table1 for that particular tick.

Does that sound like what you're looking to do?

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 6

Ismail Cassiem

SSC-Addicted

Points: 484

September 13, 2007 at 12:19 am

#733375

Thank You for the idea & advice, but my manager informed me he is looking at the SQL Agent.

Thank You, Regards