Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to make group of continuous tasks to execute based on single condition Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 3:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:48 AM
Points: 140, Visits: 281
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
Post #1400391
Posted Thursday, December 27, 2012 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 5,078, Visits: 11,856
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400498
Posted Thursday, December 27, 2012 4:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.


  Post Attachments 
Edit Constraint.png (10 views, 2.76 KB)
Precedence Constraint Editor.png (6 views, 15.33 KB)
Post #1400553
Posted Thursday, December 27, 2012 4:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 5,078, Visits: 11,856
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400555
Posted Thursday, December 27, 2012 6:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1400588
Posted Thursday, December 27, 2012 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:48 AM
Points: 140, Visits: 281
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
Post #1400635
Posted Thursday, December 27, 2012 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1400659
Posted Tuesday, January 8, 2013 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:48 AM
Points: 140, Visits: 281
Hi brandie,


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


Thanks & Best Regards
asitti
Post #1404314
Posted Tuesday, January 8, 2013 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
Then my original suggestion of using Sequence Containers and precedence constraints (with expressions) should work for you.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1404335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse