SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
asita
asita
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 328
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18507 Visits: 20441
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14593 Visits: 8996
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/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.
Attachments
Edit Constraint.png (14 views, 2.00 KB)
Precedence Constraint Editor.png (10 views, 15.00 KB)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18507 Visits: 20441
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14593 Visits: 8996
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/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
asita
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 328
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
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14593 Visits: 8996
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/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
asita
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 328
Hi brandie,


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


Thanks & Best Regards
asitti
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14593 Visits: 8996
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/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.
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