Precedence Constraint in SSIS didn''t work

Dear all,

I've been searching the article for error handling in SSIS but seems no article have same problem exactly as mine. In my package there's container, it contains Data Flow task and some of Script tasks. I put an error precedence from Data Flow task into Script task that contains script for log an error that might be occured. The Data Flow task imports data from flat file into SQL Server 2005 and I've set a semicolon as the column delimiter. Unfortunately there is a data in flat file that had one column contain semicolon as its value.

This would trigger an error and I hope the error would be logged into a table as I wrote inside Script task. But it didn't work. The error precedence won't work, the package stop in flat file source instead. I've been trying the event handler but it didn't work either. Maybe I got wrong implementation, anybody can help me explain the error handler and solve the problem ?

Here is the capture of my package, since I didn't know how to attach the picture in this forum.

Thanks in advance.

Best regards,

Hery

Try increasing the MaximumErrorCount property on the package. If it is 1, the package will fail as soon as an error is encountered.

Putting the script task in the OnError event handler for the ForEach container should work as well - what error are you getting when you try that?

|||Hi John,
If I increased the MaximumErrorCount into a value larger than 1, is it effective to solve the problem ? we won't know whether the package gets an error.
I've tried put the Script task in the OnError/OnPostExecute event handler for the container or Data Flow task but when error occured (in Data Flow task) it didn't redirect to those event handler. Actually there is no error with the Script task in event handler. Or maybe there's property I need to set ?
John do you want a copy of my package ?

Best regards,

Hery|||

That would help. My email is in my profile.

|||Hi John,
Have you got my email ? my network is a little bit slow I should try it many times. Sorry for waiting.

Best regards,

Hery|||

Looking at your package, none of your data flows are connected directly to the script task by an Error (red) constraint. You'll need that at a minimum, if you aren't using the OnError handler.

As an alternative, you could handle this error in the data flow (perhaps - haven't looked closely at the data) by redirecting error rows on the source in the data flow.

|||Hi John,

Sorry I've been deleted the error precedence since it didn't work well. Actually there was an error precedence connected directly from Data Flow to Script task. I was ini hurry when emailed it to you. In the package you can use the container named Harga FeLC, perhaps you should change the folder location and source of flat file since I used variable for ConnectionString.
Thanks John.|||

Sorry it took so long to get back to you - real work intruded.

In testing your package, the OnError event handler is firing exactly as it should - when the data flow fails. The Script Task connected with a Failure precedence constraint to the data flow also ran successfully. However, since you have two constraints on the script task (one from the Data Flow and one from an Execute SQL task) you have to set the constraints to evaluate as OR rather than AND. If you select the properties for one of the constraints leading to the script task, set the LogicalAnd property to False.

If it is set to false, it means a failure in either preceding task will cause the Script Task to run. However, when it is set to true, both tasks have to fail for the Script Task to run. Since the Execute SQL Task will never run if the Data Flow task fails, this means the Script Task can never be executed.

|||Hi John,

Sorry for waiting, I haven't try solution yet, maybe today I will and give the report tomorrow. How can I forget the property of the precedence...thanks for remind me John, I really forget about that. I think it will work.

One more question about OnError event handler, I've tried put the Script task inside Data Flow task but it didn't work, or maybe I should put it on the Container ?

Thanks in advance,

Best regards,

Hery|||

I tested it in the OnError event for the data flow, and it worked fine for me.

|||Hi John,

I tested change the precedence constraint from AND to OR, and it works fine. Thanks you John.

There's a question related with Script task. Inside the script I grab a system variable, the code just like below :

Dim errDesc As String = Dts.Variables("System::ErrorDescription").Value.ToString()

but it gets an error description just like below :

Error: The script threw an exception: The element cannot be found in a collection. This error happens when you try
to retrieve an element from a collection on a container during execution of the package and the element is not there.

is it allowed to grab the system variable just like my code ? or I have to set something inside Script Editor properties ?

About OnError event handler, I've tried to put it on Data Flow task event but nothing happen. Maybe you can check the package from me. Inside the Data Flow task of Harga FeLC container there's a Script task I've put on OnError event. Is there any setting that I have to do John ? Here is the capture of my OnError event handler and I put it on Data Flow task.

Thanks for your help John, I appreciate it so much.

Best regards,

Hery|||

On the variable issue - you need to lock it before reading it, and unlock it after. Here's a post that has more information - http://www.developerdotstar.com/community/node/512.

I tested having a Script task in the OnError handler for the data flow, and it ran without any problems. When you say nothing happens, have you looked at the event handler in the debugger while the package is running to see if it is called? It should turn green or red.

sql