pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql

Pre-execute error in simple import/export

I am trying to copy data from a SQL Server 2000 DB to SQL Server 2005 DB using the import/export wizard in SQL Server Management Studio. The two databases are not identical with different table names and different columns but I thought I had set up all the right mappings and had set the 'Enable identity insert' option. When I ran the wizard it errored at the Pre-execute phase. I simplified the wizard down to one table and only as couple of varchar columns and this also errored in the same way. The error report is detailed below.

For reference the SQL2000(ent. edn) DB is on a windows 2000 server and the SQL2005(dev. edn) DB and the management studio are both on my WinXPSP2 workstation.

Could somebody explain why these errors have occured and more importantly how to rectify the problem?

Many thanks,
Michael.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)

Messages

Warning 0x80047076: Data Flow Task: The output column "DateAdd" (23) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "DateChg" (26) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

... NOTE: I have removed the rest of the warnings as they were the same as above (many of them).

- Pre-execute (Error)

Messages

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - Nrs_NewsArticles" (112) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

- Executing (Success)

- Copying to [NereusV2_1].[dbo].[Nrs_NewsArticles] (Stopped)

- Post-execute (Stopped)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - Nrs_NewsArticles" (112)" wrote 0 rows.
(SQL Server Import and Export Wizard)

Michael,

this is most likely a truncation problem. Could you check the sizes of used source and destination columns?

Thanks.

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.
Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>
|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:
> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

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

Prcess could not execute 'sp_MSadd_repl_commands27hp'

Hello all,
Our log reader agent is failing at times with the error "The process could
not execute 'sp_MSadd_repl_commands27hp' on 'DistributorServerName'...
We have custom distribution agents that have -CommitBatchSize = 500 and
-CommitBatchThreshold = 1000...
I'm also noticing that our distribution agent cleanup agent progressively
takes longer through our peak. From 10 minutes to close to an hour...
So I'm thinking we are having contention between the distribution db cleanup
and maybe our -CommitBatchSize...
Any recommendations?
Thanks!
Stop the distribution agent and run the log reader agent again. You may need
to enable logging and post the results back here if the condition does not
clear.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
> Hello all,
> Our log reader agent is failing at times with the error "The process could
> not execute 'sp_MSadd_repl_commands27hp' on 'DistributorServerName'...
> We have custom distribution agents that have -CommitBatchSize = 500 and
> -CommitBatchThreshold = 1000...
> I'm also noticing that our distribution agent cleanup agent progressively
> takes longer through our peak. From 10 minutes to close to an hour...
> So I'm thinking we are having contention between the distribution db
> cleanup
> and maybe our -CommitBatchSize...
> Any recommendations?
> Thanks!
|||OK... Here is what is in the log... It goes from working fine to throwing
errors to working fine again:
...
Status: 16384, code: 20007, text: 'No replicated transactions are available.'.
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'DistServerName'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 22020, text: 'Batches were not committed to the
Distributor.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Status: 4096, code: 20024, text: 'Initializing'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'DistServerName'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 2000, text: 'IDistPut Interface has been shut down.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Status: 4096, code: 20024, text: 'Initializing'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
...
Thanks!
Michael
"Hilary Cotter" wrote:

> Stop the distribution agent and run the log reader agent again. You may need
> to enable logging and post the results back here if the condition does not
> clear.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
>
>
|||At the time the below error occured, the "Distribution clean up:
distribution" job was running:
...
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'ANETREPUBVSQL1F'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 22020, text: 'Batches were not committed to the
Distributor.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
...
As I noticed before, our distribution cleanup takes 30-50 minutes to
complete during peak traffic.
Thanks,
Michael
"Hilary Cotter" wrote:

> Stop the distribution agent and run the log reader agent again. You may need
> to enable logging and post the results back here if the condition does not
> clear.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
>
>
|||Stop the distribution clean up task until your log reader has processed all
of your commands in the log. Set readbatchsize to 500 and the querytimeout
and log timeout to something large - I would try 300. These settings are for
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:F303B3EA-3B82-4F6B-AF9B-23386530C0F9@.microsoft.com...[vbcol=seagreen]
> At the time the below error occured, the "Distribution clean up:
> distribution" job was running:
> ----
> ...
> Status: 2, code: 1003, text: 'The process could not execute
> 'sp_MSadd_repl_commands27hp' on 'ANETREPUBVSQL1F'.'.
> The process could not execute 'sp_MSadd_repl_commands27hp' on
> 'DistServerName'.
> Status: 2, code: 1003, text: 'Timeout expired'.
> Status: 0, code: 22020, text: 'Batches were not committed to the
> Distributor.'.
> The agent failed with a 'Retry' status. Try to run the agent at a later
> time.
> ...
> ----
> As I noticed before, our distribution cleanup takes 30-50 minutes to
> complete during peak traffic.
> Thanks,
> Michael
>
>
> "Hilary Cotter" wrote:
|||The default Log Reader agent profile has a -ReadBatchSize of 500 and a
-QueryTimeout of 300 already...
So when you say "log timeout to something large - I would try 300", are you
meaning the QueryTimeout? Again, FYI... This is SQL 2000.
Anyway, I upped the QueryTimeout to 600 and I'm still getting the timeout...
Thanks,
Michael
"Hilary Cotter" wrote:

> Stop the distribution clean up task until your log reader has processed all
> of your commands in the log. Set readbatchsize to 500 and the querytimeout
> and log timeout to something large - I would try 300. These settings are for
> your log reader agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:F303B3EA-3B82-4F6B-AF9B-23386530C0F9@.microsoft.com...
>
>
|||OK... I upped the QueryTimout to 1800 and I got the error "The agent is
suspect. No response within last 10 minutes."...
Thanks,
Michael
"BATMAN" wrote:
[vbcol=seagreen]
> The default Log Reader agent profile has a -ReadBatchSize of 500 and a
> -QueryTimeout of 300 already...
> So when you say "log timeout to something large - I would try 300", are you
> meaning the QueryTimeout? Again, FYI... This is SQL 2000.
> Anyway, I upped the QueryTimeout to 600 and I'm still getting the timeout...
> Thanks,
> Michael
>
> "Hilary Cotter" wrote: