SSIS string truncation error

Question

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!

Answer 1

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

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...