Precomputed tables

Dear Friends,

Suppose a database (SQL SEVER 2003) is consists of 500 Tables & 1000 Views.

As I understand from the theory, that Views are nothing but the queries stored in the databse. Whenever a view is referenced than it starts fetching data from tha database. Thus , it will force the processor to do calculations.

To reduce the processor burden and to ge the fast response: instead of keeping 1000 Views- I wish to keep precomputed tables in the databse.

Would this be fair practice?

Your suggestions & guidence required. Further Discussions are welcome.

Thank You.

SuryaPrakash

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...922abd573f7447e
*****************************************SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> As I understand from the theory, that Views are nothing but the queries
> stored in the databse. Whenever a view is referenced than it starts
> fetching data from tha database. Thus , it will force the processor to
> do calculations.

Not necessarily. You can index views, in which case SQL Server will
materialize them. However, not all views are indexeable. See the topic
on CREATE INDEX in Books Online for the many restrictions.

> To reduce the processor burden and to ge the fast response: instead of
> keeping 1000 Views- I wish to keep precomputed tables in the databse.
> Would this be fair practice?

Depends. If there is heavy updating going on, keeping the views (pre-
computed tables in sync) can take too much load. But if updates only
comes once a day, and in the middle of the night, and in the daytime
there are only queries, pre-computing can be a very good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

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

Practical Uses for the "Embedded Code" functionality

Dear Anyone,

I understand that there an "Embedded Code" functionality in RS2005. Does anyone know any good uses for this?

Thanks,
Joseph

Normally, you'll use embedded code to create "one off" functions that are (generally) specific to a report -- Maybe something that does a specific calculation that you need which isn't directly supported by VB.NET expressions.

Using embedded code (or code in a custom assembly) gives you greater control over logic flow and alows you to do stuff like more "advanced" conditional statements and looping which you can't do in an expression you place directly behind a control in a report

People will use a custom assembly (which is then referenced by the report) in cases where the functions are reusable, generally more complex. and do things like access system resources (the file system, the network, etc.) .

|||

Is it possible to iterate through a data set in the report in embedded code. If so, how would you reference the data set.

|||No, you can't. It would be nice to be able to just assign a "reporting services dataset" to an "true" ADO.NET datatable and then just walk the table, but there is no such functionality.|||

Hi,

Dont you thik there should be a property for a report that controls how many "records" get displayed on a page?. Currently when I create a report, only 3 records show up and I get 2000 pages. Instead I would prefer 100 records on one page and fewer number of pages?

Or is there any way to do this?

Thanks.

power point

dear all

i have problem

when i copy presentation from pc to pc the bullets and numbering change

i use power point 2003

shall i found solution

Hello - you are in the SQL Server Documentation forum. You might find more information here:

http://office.microsoft.com/en-us/help/FX100485361033.aspx?pid=CL100605171033

Buck Woody

Postal Codes 10001-10005 as a parameter

Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Possible to speed up the decision tree by Clustering the Server 2003

Dear All,

I have a dataminig programming that need to run for days. Is it possibile to speed up the training process by clustering several server by Windows 2003 clustering services? Is it actually that clustering 2 QUAD core computer is almost giving comparable performance as the sum of the speed of two (There must be some overhead, I know). I am actually familiary with the use of clustering. Is it just for making the server farm more reliable or it will collaborate and speeed up the whole training process?

If it is, is there any limit on the number of cluster is in the cluster. What version of Windows and SQL Server do I need to achieve speed up of data mining training process?

Thanks and regards

Tony Chun Tung Siu

I think your problem can be reduced at clustering Analysis Services Server because the model is in a AS database.

As is wrote in SQL Server 2005 Failover Clustering White Paper : "The ability to cluster Analysis Services is a new feature in SQL Server 2005. Before SQL Server 2005, the only way to make Analysis Services more available was to either configure it as read-only in a Network Load Balancing cluster, or create it as part of a standard Windows server cluster as a generic resource".

|||

The clustering solution will not help you with processing a single mining model. It could help if you need to apply a model to a very large data set (that is assuming you could partition your data set using a few queries, then execute the queries from multiple clients against the cluster -- the workload can be shared between cluster nodes and the performance will scale out almost linearly)

A very unrefined possible workaround, assuming you are training a decision tree model:

- start by building a model on one machine. Use a very large COMPLEXITY_PENALTY factor -- this would give you a very small tree. However, the first split in the tree will be the same as in the actual model you want to use

- create two different views on top of the relational data, one for each branch of the first split.

- train a model on each machine, using only data from one query

- copy both models on a single machine

- write some sort of stored procedure to execute predictions on the server, and have the stored procedure logic figure out which model to actually use (or, in your client application, decide based on the input which model to query)

Possible to speed up the decision tree by Clustering the Server 2003

Dear All,

I have a dataminig programming that need to run for days. Is it possibile to speed up the training process by clustering several server by Windows 2003 clustering services? Is it actually that clustering 2 QUAD core computer is almost giving comparable performance as the sum of the speed of two (There must be some overhead, I know). I am actually familiary with the use of clustering. Is it just for making the server farm more reliable or it will collaborate and speeed up the whole training process?

If it is, is there any limit on the number of cluster is in the cluster. What version of Windows and SQL Server do I need to achieve speed up of data mining training process?

Thanks and regards

Tony Chun Tung Siu

I think your problem can be reduced at clustering Analysis Services Server because the model is in a AS database.

As is wrote in SQL Server 2005 Failover Clustering White Paper : "The ability to cluster Analysis Services is a new feature in SQL Server 2005. Before SQL Server 2005, the only way to make Analysis Services more available was to either configure it as read-only in a Network Load Balancing cluster, or create it as part of a standard Windows server cluster as a generic resource".

|||

The clustering solution will not help you with processing a single mining model. It could help if you need to apply a model to a very large data set (that is assuming you could partition your data set using a few queries, then execute the queries from multiple clients against the cluster -- the workload can be shared between cluster nodes and the performance will scale out almost linearly)

A very unrefined possible workaround, assuming you are training a decision tree model:

- start by building a model on one machine. Use a very large COMPLEXITY_PENALTY factor -- this would give you a very small tree. However, the first split in the tree will be the same as in the actual model you want to use

- create two different views on top of the relational data, one for each branch of the first split.

- train a model on each machine, using only data from one query

- copy both models on a single machine

- write some sort of stored procedure to execute predictions on the server, and have the stored procedure logic figure out which model to actually use (or, in your client application, decide based on the input which model to query)

possible to save up the progress at some point of Decision Tree Training?

Dear All,

If I have a decision tree training work which might last for many days or months. Is it possible to tell the data mining training program to save up the progress at some point? In case the computer hangs or power fail in the middle, the computer can resume the rest of the work at the saving point?

Thanks

Tony Chun Tung Siu

No, SQL Server 2005 Data Mining does not support interactive training for the mining models. A training request is a transactional operation so, if it fails for any reason (including the reasons you mentioned) the transaction is not commited and, when the server is restarted, it is rolled back

possible to save up the progress at some point of Decision Tree Training?

Dear All,

If I have a decision tree training work which might last for many days or months. Is it possible to tell the data mining training program to save up the progress at some point? In case the computer hangs or power fail in the middle, the computer can resume the rest of the work at the saving point?

Thanks

Tony Chun Tung Siu

No, SQL Server 2005 Data Mining does not support interactive training for the mining models. A training request is a transactional operation so, if it fails for any reason (including the reasons you mentioned) the transaction is not commited and, when the server is restarted, it is rolled back