SSIS truncation error

Question

First, I have searched and searched and searched and not found anything that helps me with this.

I have an SSIS project that will fetch a lot of data from an iSeries AS400 and it does this in two very different steps. Step 1 works perfectly so I manage to fetch tons of info from the AS400, so the connection itself is not the issue.

Step two fails horribly with the following three error codes:

    [OLE DB Source [41]] Error: There was an error with OLE DB
Source.Outputs[OLE DB Source Output].Columns[NAME] on OLE DB
Source.Outputs[OLE DB Source Output]. The column status returned was: "Text
was truncated or one or more characters had no match in the target code 
page.".

    [OLE DB Source [41]] Error: The "OLE DB Source.Outputs[OLE DB Source 
Output].Columns[NAME]" failed because truncation occurred, and the
truncation row disposition on "OLE DB Source.Outputs[OLE DB Source
Output].Columns[NAME]" specifies failure on truncation. A truncation error
occurred on the specified object of the specified component.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The
PrimeOutput method on OLE DB Source returned error code 0xC020902A.  The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the component,
but the error is fatal and the pipeline stopped executing.  There may be
error messages posted before this with more information about the failure.

I have desperately tried to find the solution to this problem and this is what I have done (which have not helped at all):

1 - Advanced Editor on SOURCE -> tab: Input and Output Properties -> OLE DB Source Output -> Output Column changed to a) 40 (from 28) in length - no change b) data text (from string) - complete crash c) changed codepage from 1251 to UTF-8 - no change

2 - Fetched the information with OPENQUERY in MSSMS, it works perfectly.

3 - Screamed in frustration at the screen (didn't help).

I am at roads end. I don't know what to do anymore. Help...?

Answer 1

You may find setting the Error Output for Truncation on the Source editor dialog to "Ignore Failure" gets you around the issue.

Update - Truncation Redirect:-

Forced truncation on surname - output set to redirect and enabled Data Viewer on the error output

then copied the row from the data viewer to notepad to show the error

Running the same dtsx wif truncation set to fail :-