2

Im trying to execute a SQL Task after a For Loop Container completes (i.e performs its iterations) but it just wont execute. I cant for the life of me work out why it wont execute as there are no errors. Any ideas?

SSIS Workflow Params

The Loop executes the correct number of times but as you can see from the image, the Insert Referral Actions SQL Task does not execute.

The idea is that if the number of source records falls under a certain threshold e.g. 2000000 then just load in 1 shot otherwise loop 1000000 at a time and load. This is to get around some network and connection issues I was having.

The final task (Insert Referral Actions) executes a stored procedure that joins the staging data with a lot of related tables and populates the Fact table. It is this SQL Task that I need to share between the 2 branches.

Thanks for any help

Matt
  • 389
  • 1
  • 8
  • 15
  • Have you tried adding a script task inside your For Loop to display the loop value? Also, it might help if you could provide a screenshot of the parameters used in the For Loop – MiguelH Sep 30 '15 at 15:32
  • Havent tried the Script Task. I know the loop is working as im getting over 5 million rows in my staging table so it is doing its job – Matt Sep 30 '15 at 15:41
  • I don't doubt it works, but it might be good to include a script component just to see the ReferralActionLoopiteration value compared to the ReferralActionLoopMaxCount. – MiguelH Sep 30 '15 at 15:48
  • If you remove the "Load Referal action into staging", does the Insert Referal Actions fire as expected? – billinkc Sep 30 '15 at 16:49
  • The prior task doesn't run either, `Load Referral Actions into Staging` isn't completing, so the next task won't run unless you change the run conditions. – Hart CO Sep 30 '15 at 16:50
  • See also http://stackoverflow.com/a/32104849/181965 – billinkc Sep 30 '15 at 16:59

1 Answers1

2

Logical explanation of "Insert Referral action" says that it should execute when input is executed successful. In your case, one out of two input is successful. So, technically "Execute SQL" task doesn't have permission to execute because both input are not successful. Here is how you can do that click on the precedence constraint and select logical OR instead of Logical AND.

enter image description here

Anuj Tripathi
  • 2,251
  • 14
  • 18