SSIS - Truncation Redirect Row Not Working

[Database Administrator]

I have set up a SSIS package consisting of

OLE DB Source - Legacy Customers Table
OLE DB Destination - Valid Customer Table
OLE DB Destination - Exceptions Table

Against the OLE Db Source , I have specified a Redirect Row on all columns
when Truncation occurs.

The OLE DB Source Error Output is directed to the Exceptions Table and valid
rows to the Valid Custome Table

I have set the Data Access Mode on the OLE DB Destinations to Table or View
and NOT fast Load, but I receive the following error when executing :

[OLE DB Destination [2917]] Error: There was an error with input column
"Region" (3035) on input "OLE DB Destination Input" (2930). The column status
returned was: "The value could not be converted because of a potential loss
of data.".

There is a previous error message but think it is due to the above error:
[OLE DB Destination [2917]] Error: SSIS Error Code DTS_E_OLEDBERROR. 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.".

Surely this error should have been redirected....Any ideas gratefully received

Thanks in advance
Nige

[Database Administrator]

Hello Nige,

I understand that an OLE DB Destination [2917] error is not redirected to
the Exceptions Table properly. To know the issue better, I'd like to know
the following information.

1. Did you configure "rediret row" for error for all the columns in the
OLEDB source?

2. Does the package stop on the error?

3. If possible, you could send me the package, sample source/destination
tables/schema so that I may reproduce the issue on my side. You could
remove Online from my displayed email address.

If you have any update, please feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Database Administrator]

Hi Peter,

I have sent a test package etc. to your email address petery@microsoft.com.
Please contact me if you do not receive.

Thanks
Nige

""Peter YangMSFT]"" wrote:

> Hello Nige,
>
> I understand that an OLE DB Destination [2917] error is not redirected to
> the Exceptions Table properly. To know the issue better, I'd like to know
> the following information.
>
> 1. Did you configure "rediret row" for error for all the columns in the
> OLEDB source?
>
> 2. Does the package stop on the error?
>
> 3. If possible, you could send me the package, sample source/destination
> tables/schema so that I may reproduce the issue on my side. You could
> remove Online from my displayed email address.
>
> If you have any update, please feel free to let's know. Thank you.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> .
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> .
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

[Database Administrator]

Hello Nige,

Thank you for the package/data schema. I reproduced the issue on my side. I
found the error occurs on the "SAP SD Customer Master" OLEDB destination
other than the OLEDB data source. Therefore the error output for the OLEDB
data source "Legacy Customer master" cannot catch the error properly.

I added a new Truncation exception OLEDB destination for the error output
of "SAP SD Customer Master" with the proper mapping of the column as the
old one, the error is captured properly and the row is transfered to the
"TruncationExceptions" table.

If you have further questions or concerns on the issue, please feel free to
let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

[Database Administrator]

Hi Nige,

I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

[nishasharma123]

Hi Peter,

I am also facing same problem explained in the previous thread with Truncation Redirect row.Could you please guide me on this.

Nisha