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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

Prblem with MSDN Article on forms authentication for rs2005

Hi All
i wanted to implement foms authentication to RS2005.i am
using the MSDN article to implement the forms authentication.when build
the project i am getting this error.
'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
not implement interface member
'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
System.IntPtr, byte[],
Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
can any one help me with this problem.
ThanksTry opening your reference and see any problems otherwise remove the
reference and search for the reference and add it again..
Amarnath
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Ok Thanks.i will try it.
Thanks
Vamsi
Amarnath wrote:
> Try opening your reference and see any problems otherwise remove the
> reference and search for the reference and add it again..
> Amarnath
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >|||everything looks good.i added microsoft.reporting.interfaces and web
services and named as my machine name.still getting the error at the
authorization .
Thanks
vamsi wrote:
> Ok Thanks.i will try it.
> Thanks
> Vamsi
> Amarnath wrote:
> > Try opening your reference and see any problems otherwise remove the
> > reference and search for the reference and add it again..
> >
> > Amarnath
> >
> > "vamsi" wrote:
> >
> > > Hi All
> > > i wanted to implement foms authentication to RS2005.i am
> > > using the MSDN article to implement the forms authentication.when build
> > > the project i am getting this error.
> > >
> > >
> > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > not implement interface member
> > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > System.IntPtr, byte[],
> > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > >
> > >
> > >
> > > can any one help me with this problem.
> > >
> > > Thanks
> > >
> > >|||Hi,
apparently the samplel code is not complete for the current version, I faced
the same problem.
The issue is that the code supplied does not include 2 overloads of the
CheckAccess method.
To resolve include the following in Authorisation.cs:
// Overload for a Model operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelOperation modelOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelOperation))
return false;
else
return true;
}
// Overload for a ModelItem operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelItemOperation modelItemOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelItemOperation))
return false;
else
return true;
}
This will allow the code to compile.
Now if only it didnt give me an 'Invalid Namespace' Error when I try to run
it... :(
Please lt me know if it works for you!
"vamsi" wrote:
> everything looks good.i added microsoft.reporting.interfaces and web
> services and named as my machine name.still getting the error at the
> authorization .
> Thanks
> vamsi wrote:
> > Ok Thanks.i will try it.
> >
> > Thanks
> > Vamsi
> > Amarnath wrote:
> > > Try opening your reference and see any problems otherwise remove the
> > > reference and search for the reference and add it again..
> > >
> > > Amarnath
> > >
> > > "vamsi" wrote:
> > >
> > > > Hi All
> > > > i wanted to implement foms authentication to RS2005.i am
> > > > using the MSDN article to implement the forms authentication.when build
> > > > the project i am getting this error.
> > > >
> > > >
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > > not implement interface member
> > > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > > System.IntPtr, byte[],
> > > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > > >
> > > >
> > > >
> > > > can any one help me with this problem.
> > > >
> > > > Thanks
> > > >
> > > >
>|||Update:
The problem is that you are using the Report Services 2000 sample on a 2005
server. You should download the 2005 sample from here:
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
hope this helps :-)
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Hi i downloaded new article from the same page just a week back and it
worked.it is able to authenticate.But i am having little bit doubt on
the member ship role authorization.
where we have to design all the tables for the roles that each
registered user supposed to get.Like i want some users to get the
subscription screated and i dont want for some of the users to do this
and they can just view the reports.
Thanks
Vamsi
> Update:
> The problem is that you are using the Report Services 2000 sample on a 2005
> server. You should download the 2005 sample from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
> hope this helps :-)
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >sql

posting again

Hi,
I do not see any guideline or a thorough article for ISVs who will be using MSDE for deploying their application on desktops using application security roles. There definetely is information here and there but a comprehensive writeup is needed. Any plan
s by any of you SQL Server gurus.
Thank you
Read the BOL about database security roles. There should be all the
information you need. In general you define the security roles you need
based on your application security model. Then set the permissions to your
database objects using the roles. Users of the database are then give a role
membership, rather than explicit permissions.
Jim
"qa" <anonymous@.discussions.microsoft.com> wrote in message
news:30E7FFAA-A6C6-4333-B3AD-382B25689330@.microsoft.com...
> Hi,
> I do not see any guideline or a thorough article for ISVs who will be
using MSDE for deploying their application on desktops using application
security roles. There definetely is information here and there but a
comprehensive writeup is needed. Any plans by any of you SQL Server gurus.
> Thank you
>
|||can all this be done even if I am using the integrated security. See I am implementing my own custom identity and principal classes and here is how it works:
1> my app's first screen will be the login screen.
2>once the user enters the username and password, these 2 fields will be verified from a table in my app's database. If the user does exist in the table then I assign this user as my app domain's current user and use it's id for further interaction with
the objects in the app. I even read the roles the user is part of from a role table, for e.g. dataentryclerk, poweruser etc.. these roles will be used to control the app's UI behaviour too.
3> I am using integrated security to access the database but instead of windows giving me the current user's id token, I am using my role based security (as explained above) and assigning the currect user to the app domain.
This was needed because it is not necessary that every user who accesses my app will be a windows user. My app's requirements state that anyone who has been entered in the database (we will provide a UI to enter users and their passwords) can access the
application.
So in this situation, how can I protect my database, tables and stored procedures from anyone who has some MSDE Manager type of tool.
sql