SSIS string truncation error

4
votes

I have a SQL Server 2005 SP2 database which has a table with a poc_resp_city attribute which is nvarchar(35).

It was changed to nvarchar(80) 2 months ago without aligning the very same attribute in the data warehouse. (which still has nvarchar(35) )

The SSIS data loading package (after two months of proper working) now gives back package failure every time I run it with the following error:

There was an error with output column "poc_resp_city" (2250) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". There was an error with output column "poc_resp_city" (2250) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source Table" (1) 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.

Neither the package nor the databases were modified regarding this issue. I know that I could ignore this error or I could make the arrangements to make sure it's working but I want to provide a proper and acceptable answer why this error appears 2 months after the modification? Because maybe I miss an important step in this situation.

Important note: I don't have even a single record which has more than 35 characters so truncation never occurs. (this warning belongs to some kind of an SSIS validation step)

Now I think that maybe after a period of time, SSIS package recompiles itself and now it sees this misalignment in its metadata (35 =/= 80) and because TruncationRowDisposition attribute is set to RD_FailComponent, it fails the component.

And I would exclude the code page option because every database column is nvarchar, not varchar, so this shouldn't be the case.

Thanks!

1
sql-server-2005ssistruncation
Did you check MAX(LEN()) in the source table? I'd guess you somehow picked a record over 35 characters, maybe unprintables or trailing spaces. That seems more likely than the spontaneous recompile explanation, though SSIS 2005 is rather old and always had more weird behavior than 2008 or 2012. – criticalfix
I checked it and checked LEN(city+'a') too to ignore white spaces. (because LEN('aa') and LEN('aa ') gives back the same value. And there is no record which goes above 35 – dn7123
I really think you're mistaken. This is not a validation error, and the fact that it simply appeared after some time strongly indicates that someone just happened to enter an amount of data that was longer than 35. If you really want to get to the bottom of it, follow the advice below and run the package and isolate the data row(s) causing the issue (by reproducing the issue then divide and conquer to find the data). When you isolate the data row(s) it'll all become clear. – Nick.McDermaid
If you're convinced absolutely nothing could have been modified then that only leaves the data in the database as the culprit because end users can modify that. – Nick.McDermaid

1 Answers

2
votes

You need to refresh size of column:

  1. With right button push on OLE DB Source -> Show Advanced Editor
  2. Choose Input and Output Properties tab -> Ole DB Source Output -> Output Columns
  3. In the right panel Length row insert your new size.
  4. Push OK

Show Advanced Editor

Or you can copy your query from OLE DB Source, delete OLE DB Source, insert new OLE DB Source and paste query. This gonna automaticily refresh your columns.

Just remember what there are probably more element in Dataflow where you need to edit length of your column, like Data Converion...