How to make group of continuous tasks to execute based on single condition

Question

How to make group of continuous tasks to execute based on single condition

asita

SSCarpal Tunnel

Points: 4144
More actions
December 26, 2012 at 3:06 pm

#266550

Hello All,
Could you please help me out here, i am totally stuck here,
i have total 20 tasks in my ssis package (dataflow, execute sql task, etc)
at 16 dataflow task i want to check something in db table, if condistion satisfied then it will execute the remain tasks, if not then it should n't execute 16, 17, 18 tasks then 19 as usual
how to achieve this
please help me
Thanks in adavnce
asiti

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

asita (12/26/2012)
Hello All,
Could you please help me out here, i am totally stuck here,
i have total 20 tasks in my ssis package (dataflow, execute sql task, etc)
at 16 dataflow task i want to check something in db table, if condistion satisfied then it will execute the remain tasks, if not then it should n't execute 16, 17, 18 tasks then 19 as usual
how to achieve this
please help me
Thanks in adavnce
asiti

So is task 20 the only one whose execution should be conditional?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 2

Put the "optional" tasks in a sequence container. Then have the precedence constraint from task 15 (or whereever you're checking your conditional value) go to the sequence container using "Success and Expression" as the conditions of the constraint. To edit the constraint, right click the line leading from the task to the container and click "Edit". There is where you set the values and have the conditional verified.

Pictures attached. First one is the right click menu. Second is the window with the editor.

EDIT: Something I forgot to mention. You can have multiple precedence constraints going from task 15 to other tasks. You can also have the opposite check for task 15 to task 19 (or 20), i.e. Value is False. And to make sure task 19 or 20 runs after the sequence container (if Value is True), you add a constraint leading from the container to task 19 or 20. It'll look like a triangle with the arrows going from 15 to 20 and 15 to container and container to 20. 20 will NOT have an arrow going back to either 15 or the container.

Make sense?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Phil Parkin (12/27/2012)
asita (12/26/2012)
Hello All,
Could you please help me out here, i am totally stuck here,
i have total 20 tasks in my ssis package (dataflow, execute sql task, etc)
at 16 dataflow task i want to check something in db table, if condistion satisfied then it will execute the remain tasks, if not then it should n't execute 16, 17, 18 tasks then 19 as usual
how to achieve this
please help me
Thanks in adavnce
asiti
So is task 20 the only one whose execution should be conditional?

Oops, it seems I read "shouldn't" as "should" - I think I was distracted by the absence of task 20.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 4

And I seem to be confused as to whether or not these optional tasks are inside or outside the data flow. My suggestion applies to the Control Flow tasks.

Asita, could you clarify your needs? Tell us what types of task (Transformation or Control Flow) you're trying to avoid using?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

Thanks for your response Brandie, Phil

i am really sorry for the confusion,

here is bit clear explanation...

all i am using is Data flow tasks, Execute SQL tasks together,

let say i have 20 tasks (10 execute sql tasks & 10 data flow tasks)

upto 14 it is good,

now i added,

15 as dataflow task (fetch data to sql table (Table A) with primary key)

Task 16 is data flow task (it loads data to foreign key table(Table A1) belong to step 15 (Table A))

Task 17 is data flow task (it loads data to foreign key table(Table A1) belong to step 15 (Table A))

18 as dataflow task (fetch data to sql table (TableB) with primary key)

Task 19 is data flow task (it loads data to foreign key table(Table B1) belong to step 18 (Table B))

Task 20 is execute Sql Task to tally unrelated to above steps

so i added a task (execute sql task) before task 15 to check in data any primary key violation data in step 16 sql logic

if it has any violation primary key data then i want to avoid 15, 16, 17 but want to execute 18 onwards normally...

Hope this clear, please let me know if not, i will elaborate more

Thanks in advance

asita

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 6

Let me rephrase my question.

Are all these tasks occuring in the SAME dataflow task as task 16? Or are they all individual dataflow tasks?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 7

Hi brandie,

They are all individual data flow tasks in one single ssis package.

Thanks & Best Regards

asitti

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 8

Then my original suggestion of using Sequence Containers and precedence constraints (with expressions) should work for you.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.