December 20, 2007 at 11:50 am
I have created a task that uses a ForEachFile Loop. Within that loop I load 5 files. Once those 5 files have loaded successfully I want to run a stored procedure. I placed this stored procedure into a script task. The script task has 5 precedence constraints,. They are a success on the load of the files. When I run the task, the 5 files load, but the script task will not start. I have gone so far as to check each load individually to see if it would start the script task, which they do. It's only when I add more than 1 precedence constraint that the script task will not run. The precedence constraints are set to AND, so they all are required. They all succeed. What am I doing wrong? here is a link to a screenshot of the task.
December 20, 2007 at 12:37 pm
Successfully completing and being successful because you have not done anything do not necessarily mean the same thing in the precedents.
To reduce this confusion, I would put a sequence container around everything before the sp_WSTR_EXTRACT_DATA component and then just have a single flow control between that sequence container and the sp_WSTR_EXTRACT_DATA task.
First, it will look nice. Second, it will reduce the number of incoming arrows into the task making SSIS look at everything in the sequence container once it has all completed and then make the decision on moving to the next task.
December 20, 2007 at 12:48 pm
I tried what you said and the Run SP_... task kicks off before all of the files have loaded.
December 20, 2007 at 12:56 pm
So here is what I think you want...
You have your script task looping through a bunch of files. After all of the loops have completed successfully, you want the Execute SQL task to run.
If that is the case, you need to move the Execute SQL task out of the loop container. Set the loop container MAXErrorCount to 0. Connect the precedent from the loop container to your Execute SQL task.
The loop container will run through all of your files - running the script task and branching to the appropriate data flow. When done successfully, it will run the Execute SQL Task. If any of the files fail, the loop will discontinue and the package will stop.
December 20, 2007 at 1:05 pm
Ok, were getting close. It works as you said except that if one of the files is not in the directory and that loading routine doesn't kick off, the Run sp_WSTR... still starts. I only want the Run sp_WSTR... to start if ALL 5 files get loaded.
December 21, 2007 at 6:26 am
It is really because you are using a loop to pick up the files. You probably should not, but we can handle that also.
Create 5 variables - one for each file and make them boolean defaulting to False.
After each DataFlow task, add a task to set the appropriate variable to true.
Then, in the precedent flow from your sequence container, check the value of your variables.
You could also simply increment a single variable each time you loop in your ForEach container and only run the ExecuteSQL if the variable was set to 5, but then if you got the same file twice you would still run the next task.
Honestly though, you have a loop process picking up files that MUST run 5 times because there will always be 5 files in the directory. I assume you are probably trying to deal with the file names being different and you have to look in the file to figure out the correct path to take.
I would handle this differently.
In the loop container, leave your script task. Using your current logic, copy the 5 different file types to another folder with specific hard-coded names. After the loop container put your truncates and data flows in the sequence container with the Execute SQL task that runs after them.
Your ForEach will have a single OnSuccess to the sequence container and the Truncate / DataFlow pieces will run in parallel once. Then, you can wire them OnSuccess to the ExecuteSQL task. The DataFlow tasks will fail if the file was not copied to the correct location by the loop so the ExecuteSQL will not run either.
At the end of the package, delete the temporary files created in the ForEach loop container to ensure they will not be there is the loop container does not get the correct file the next time the package runs.
...I hope that made sense
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
