Hi,I am developing a package which takes data from SQL Server 2005 and hits the JDE files. In this package I need to send error mail when anyrow returns failure on OLE DB Command component which calls JDE Stored Procedure. So I created a script component which takes error output (red arrow) from OLE DB Command component. But whenever error is triggered, this is not redirecting the rows to script component even though I configured Error output as "Redirect Row". Can anyone please tell me how to redirect error to a script component?

When you execute your package in Visual Studio and examine the data flow, are any error rows being redirected? If not, does the count of input records being set to the OLE DB Command transform equal the count of records being sent out of the OLE DB Command transform on the green data path? And one final question: are you certain that there are error records?

Thanks for your replyI didn't see anyrow redirected when I am executing from Visual Studio.In OLE DB Command, the count of input rows is equal to the sent outs rows. After inserting records in JDE, I am resetting flag to all my original records in SQL Server. Also if input rows count is 20 then the output rows count is 20-<errorrowcount>Yes to test we generated records which creates error at the time of inserting data in jde file.Line after the error message in Execution result"[DTS.Pipeline] Error: The ProcessInput method on component "olc_JDE_DB" (17741) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. "

Hi Vasanth, The OLE DB Command does not handle all errors from the OLE DB provider using row redirection. Some errors will just cause the transform to fail execution instead of redirecting, particularly if the error is due to failing to execute the command instead of a data-related error (e.g. integrity violation). If you've already checked your OLE DB Command row redirection is configured correctly, then it's possible the error returned to the Command is not triggering the redirection. Here are some suggestions to get this to work: 1. If possible, see if you can return a different error to trigger the row redirection. Try returning an error that's on a column level instead of on a command level. 2. If the above doesn't work, you can try the script transform in place of the OLE DB Command, which can be set up to execute commands per row and also redirect rows based on any error you want. This will take more work. Ted

Ted Lee - MSFT wrote: 1. If possible, see if you can return a different error to trigger the row redirection. Try returning an error that's on a column level instead of on a command level. Ted - can you please elaborate on this? I spent some time last week reproducing this error in relationship to a different forums post and was somewhat surprised and alarmed at the failing-not-redirecting behavior I observed. I really appreciate your response, but to be honest I have no idea how to return "an error that's on a column level instead of on a command level" and would love it if you could post an example. Thanks in advance!

Sure. Let's first discuss a bit more on the difference between command-related errors and column / data related errors. Some errors can occur if the command used has invalid syntax or if it uses a bad object name. The input data processed by the command transform does not contribute to those errors. In those cases, more often then not, users want to correct their command instead of looking for bad data and so, the command transform just fails execution instead of redirecting rows (if it redirected, it will end up redirecting all the rows since the command will always fail regardless of the data). For those errors, OLE DB providers can return generic errors such as DB_E_ERRORSINCOMMAND to the Command transform. Column / data errors can occur when the data in the columns cause the error, such as an invalid data conversion when inserting a badly formatted stringinto a datecolumn or a value that violates an integrity constraint. For those errors, users dowant the rows redirected. OLE DB providers usually return more specific errors (e.g. DB_E_INTEGRITYVIOLATION) along with which column contains the data that caused the problem. So, one example is if a stored proc was writtento catch all errors, including integrity violations that might have cause the OLE DB provider to return a DB_E_INTEGRITYVIOLATION to the Command transform, and in its exception handling, raises a generic error that the provider ends up returning as DB_E_ERRORSINCOMMAND to the transform. Therow redirection would not be triggered. Depending on the database and its OLE DB provider, it might not be possible to produce an error that will cause the OLE DB command to redirect. For example, if the OLE DB provider is written to always return DB_E_ERRORSINCOMMAND regardless of what the actual error was reported by the database. Since we've shipped the command transform, we have seen cases where command-related errors are being returned for data related errors, causing rowredirection to not occur. So, for the next version of Integration Services, we've made the Command transform more accommodating by having it look at DB_E_ERRORSINCOMMAND and try to redirect if possible. Hope this clears things up a little bit more. Ted

Thanks again, Ted - this does help a lot. Just for clarification, I have three follow-up points and/or questions: 1) By "the next version" do you mean SQL Server 2008 SSIS, or the next version after that? 2) The two repro scenarios that I put together when working with the other "OLE DB Command not redirecting" thread mentioned earlier had different ways to raise errors. One simply called RAISERROR ('Foo', 16, 1) and the other divided the input parameter by zero. Both were using SQL Server 2005 SSIS and a stored procedure in a SQL Server 2005 database. Neither one redirected any records. Personally, (keep in mind that I don't know what the SQL Server OLE DB provider is going to do with either one of these errors)I would expect at least the second error to be considered a "redirectable" error, since the cause of the error is an operation being performed on column data from the data flow. I assume I'm missing something here, but I don't know what it is. 3) Is there a way in T-SQL for SQL Server 2005 to explicitly raise an error (as opposed to simply not handling an error) so that the OLE DB Command transform can redirect the row for which the stored procedure was called? Although the OLE DB error information you shared earlier is very interesting, it's not clear how to act upon it. You say that"Depending on the database and its OLE DB provider, it might not be possible to produce an error that will cause the OLE DB command to redirect" but can you answer this SQL Serer 2005-specific question?

Thanks. Your response really help me

Ted,I just hit the problemusing SSIS on SQL 2008. Thanks for the explanation I was pulling my hairs for couple of (long) minutes before I found your reply. Iam writing a dynamic ETL process for the client where the table/column name is passed dynamically. Therefore my merge statement may fail either because of the data format ora command issue. It would be nice if the component could redirect row regardless of the type of error and even better if there was a way to intercept the original error message from the SQL statement. Something to consider for the future version if possible. Thanks, Rob RK

Hi Ted Your explanation was helpful in understanding my problem too but the OLE DB provider was still SS2008 R2 and not some 3rd party provider. Mine was a simple case of integrity violation when inserting duplicates, pretty basic stuff really. Could you update this thread with a guestimate as to when this bug might be fixed? Regards Derek