//
you're reading...
SQL Server

Text was truncated or one or more characters had no match in the target code page.

I recently had an unusual problem with one of my SSIS packages after modifying the destination table layout from VARCHAR to NVARCHAR. The package included a data flow task that ran in-line SQL as an OLE DB Source. I was using a case statement to reformat the backupset. type column. Of course after changing the destination column from VARCHAR to NVARCHAR i received a warning regarding the inability to implicitly convert the input field to UNICODE and that the resulting conversion would be defaulted to DT_WSTR. This seemed perfectly acceptable to me as is the data type for UNICODE text. The package failed with the following error :

[OLE DB Source [267]] Error: There was an error with output column "backup_type" (343) on output "OLE DB Source Output" (277). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.

So of course I immediately check the code page properties for the source and destination and both were set to a default code page of 1252. I then figured that there was something amiss with the implicit conversion to UNICODE, so I attempted and explicit conversion to NVARCHAR in my in-line SQL code. Again the package failed.

The Solution:

I inserted a Data Conversion task between the OLE DB Source  and the OLE DB Destination that explicitly converted the offending column to [DT_WSTR] (UNICODE). 

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: