precisions of DateTime

Hi,
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:

> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:

> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:

> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>

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

Precedence Constraint Expressions

Hi all

I've got a package that checks the length of chars in a flatfile and if it's correct it executes the dataflow task.

How can i write an expression in the precedense constraint editor to skip the dataflow task if there is an error in the script task?

Thank you

Set a variable in the script task and then test the variable in the precedence constraint.|||

Something similar to:

Code Snippet

If [File is Valid condition] Then

Dts.Variables.Item("User::ValidFile").Value = 1

Else

Dts.Variables.Item("User::ValidFile").Value = 0

End If

in the script task (mark the variable as ReadWrite in the script task properties) and

Code Snippet

@.ValidFile==1

in the precedence constraint. You can set the expression by double-clicking the precendence constraint and choosing Expression and Constraint in the Evaluation operation property.

|||

Thanks for all your help Phil and JWelch.