Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eric_ht
Starting Member
37 Posts |
 Posted - 2009-05-13 : 15:27:01
|
| I have a single package that has two For Each Loops. One inside the other. Inside the Inner For Loop is a Data Flow Task.There are times when the Data Flow Task will not be able to pull the data due to an Exclusive lock on the source table. The data flow is using a sql variable and the destination is a Flat File.When and if a lock happens I would like the For Loop to continue processing. FailPackageOnFailure is False and I have messed with the Propagate system variable trying to keep the loop going.But it still fails and processing stops. Any ideas on how I can keep the loop going?Thanks |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-05-13 : 17:32:04
|
| Use the failure precedence, drag the (red arrow/line) out from the Data Flow Task to another task that always succeeds (and does nothing).Hope this is clear. Find it difficult to explain SSIS with using my hands:-) |
 |
|
|
eric_ht
Starting Member
37 Posts |
Posted - 2009-05-13 : 18:40:28
|
| Thanks Yellowbug. I had tried that previously with a send mail task. but it still failed.So I put the Data flow Task in a separate package and did again as you suggested this time with a script task that does nothing but read in a variable and it still fails and never continues looping. |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-05-14 : 08:11:18
|
| Hmmm...Is the error in a stored procedure? Can you trap it with TRY...CATCH? |
|
|
|
eric_ht
Starting Member
37 Posts |
Posted - 2009-05-14 : 20:33:25
|
| Not using a Stored Proc, just a straight data flow from ole db source to flat file destination.Here's how I ended up solving the problem:After the Package Task I do a precedence workflow on completion(the blue line) to the next task afterwards. I also had to change the For Loop to force success. In the Package I set a boolean variable in the OnError event to use in the completion workflow to determine if the tasks afterwards should be executed or not.May not be the best solution but it works! |
|
|
|
|
|
|
Continue Loop After Failure inside For Loop