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

I am tryin to run an SSIS package from an Excel Spreadsheet to MS SQL Server 2005.

I receive the error: Text was truncated or one or more characters had no match in the target code page (full report is below).

I found this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2562259&SiteID=1
and have set the destination column to nvarchar(max), and I have also set the TruncationRowDisposition = RD_IgnoreFailure on the destination column, with no luck.

Any ideas? Thanks!

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Success)

- Copying to [cisense_new].[dbo].[_Details] (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column "Name" (66) on output "Excel Source Output" (60). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Name" (66)" failed because truncation occurred, and the truncation row disposition on "output column "Name" (66)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 1 - owners$" (52) 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread1" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 2 - trademarks$" (128) returned error code 0xC02020C4. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread2" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - details$" (1) returned error code 0xC02020C4. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)


- Copying to [cisense_new].[dbo].[_Owners] (Stopped)

- Copying to [cisense_new].[dbo].[_Trademarks] (Stopped)

- Post-execute (Success)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)


- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - _Details" (26)" wrote 14454 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task: "component "Destination 1 - _Owners" (92)" wrote 13304 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task: "component "Destination 2 - _Trademarks" (186)" wrote 10005 rows.
(SQL Server Import and Export Wizard)




May 13th, 2008 8:01pm

I have yet to find a solid fix to this, but I did find a workaround, courtesy of this post:
http://groups.google.ca/group/microsoft.public.sqlserver.dts/browse_thread/thread/e6595d244f896e64/d0b828ab2fd7a7a6?hl=en&lnk=st&q=error+with+output+column+Text+was+truncated+or+one+or+more+characters+had+no+match+in+the+target+code+page#d0b828ab2fd7a7a6

Apparently the SSIS system determines the data type of an EXCEL column by examining the first 8 or so rows (ridiculous). So if all of the first 8 rows are less than 255 chars, and after the 8th row there are values greater than 255 chars, the truncation error will occur.

So the workaround is to throw in a temporary row with a large (> 255 chars) value in the problematic column, and then run the Integration. Once complete, delete the temporary row.

I'd still love to hear of a real fix to this if anyone comes across one.
  • Proposed as answer by divydovy Friday, November 06, 2009 9:11 AM
Free Windows Admin Tool Kit Click here and download it now
May 13th, 2008 9:40pm

ryan1999 wrote:
I have yet to find a solid fix to this, but I did find a workaround, courtesy of this post:
http://groups.google.ca/group/microsoft.public.sqlserver.dts/browse_thread/thread/e6595d244f896e64/d0b828ab2fd7a7a6?hl=en&lnk=st&q=error+with+output+column+Text+was+truncated+or+one+or+more+characters+had+no+match+in+the+target+code+page#d0b828ab2fd7a7a6

Apparently the SSIS system determines the data type of an EXCEL column by examining the first 8 or so rows (ridiculous). So if all of the first 8 rows are less than 255 chars, and after the 8th row there are values greater than 255 chars, the truncation error will occur.

So the workaround is to throw in a temporary row with a large (> 255 chars) value in the problematic column, and then run the Integration. Once complete, delete the temporary row.

I'd still love to hear of a real fix to this if anyone comes across one.

The real solution is to find what the longest text length in that column is (=LEN(A2) and copy it all the way down), and then update the table definition and the metadata for the source column, but give yourself some leeway. To do this, right click on your source connection manager and select Show Advanced Editor, then you should be able to locate the metadata for your column and update it accordingly.

May 14th, 2008 4:04am

Thanks for the suggestions, as I am having the same issue when importing a simple csv file. In my case, the length of the fields in both the source and destination are correct, but I am still getting the truncate error, dispute the fact I have set the global and column import rules to ignore any truncation.

For me, this seems like a bug, as I have this silly expectation that when I set an option that says: "On Truncation: Ignoe" that all truncation errors will, in fact, be ignored!

What's even more suprising was the csv file was created by the export wizard in SQL 2000.

Anyway, I was able to get around the problem by using MS Access as a trasport file instead of CSV.
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2009 4:09pm

This is a very frustrating error. I've now spent about 30 min. trying to import 28 rows from a cvs file into SQL Server!!!!!!! All fields in thedestination table are nvarchar(255). I cansee ALL 28 rows in my cvs file (in excel),the longest is about 100 chars long (I've counted). This is crazy! I can't import 28 records and I used to hold a SQL DBA position! After getting this error I can open excel, copy the data out of the colum from which the message is reporting the error is originating from and paste it into the correct field in the table in SQL Server Management Studio. Go figure.

... Finally got it to work. Problem is something to do with thecsv file format. When I save it asxls the import worked.
February 2nd, 2009 8:25pm

You can edit your registry to specify the look ahead when figuring data length.
Default is 8. 0 (zero) means all records.

Details here:

http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/0c245265-79fc-406c-8ec6-6019eebc4f68/?ppud=4
Free Windows Admin Tool Kit Click here and download it now
April 1st, 2009 7:41pm

short answer: non-breaking space (0xA0)

more detail:
I got the same error trying to import some data from a spreadsheet. I first tried writing the .XLS out as a .CSV and importing it as a flat file, having had better luck with this in the past. That's when I got the error that's the title of this page.

I tried importing the original XLS. It imported just fine. But two of the spreadsheet rows didn't match anything in the database. There was no visible difference between the string in the spreadsheet and the string in the database. At first I thought it was the old en-dash (0x2013) vs. hyphen (0x2D) problem (MS Word will sometimes helpfully turn a hyphen/minus into an en-dash for you automagically. In a monospace font, they're pretty much indistinguishable). But that wasn't it.

Turned out to be a non-breaking space. This turned it up:
with breakout(name1, name2, idx, char1, char2)
as (
	select name, custname, 1, ascii(name), ascii(custname)
	from #weirdnonmatch
	union all
	select name, custname, idx1, ascii(substring(name,idx1,1)),
		ascii(substring(custname,idx1,1))
	from (
		select name, custname, idx + 1 as idx1
		from #weirdnonmatch
		inner join breakout on name1 = name and name2 = custname
			and idx < len(name1) and idx < len(name2)) x
)
select * from breakout where char1 <> char2

(I know, gratuitous use of a CTE. I just irrationally hate WHILE loops. XML query, XML nodes, CTE - anything to avoid a WHILE loop)
  • Proposed as answer by ColVI Monday, June 14, 2010 9:19 AM
June 11th, 2009 1:22am

You can edit your registry to specify the look ahead when figuring data length.
Default is 8. 0 (zero) means all records.

Details here:

http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/0c245265-79fc-406c-8ec6-6019eebc4f68/?ppud=4
To save people some digging: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows (for CSV files). But if these don't immediately do what you want, search the innertubes for typeguessrows and maxscanrows to read about various potential gotchas.
  • Proposed as answer by Michael Felch Tuesday, June 30, 2009 12:39 PM
Free Windows Admin Tool Kit Click here and download it now
June 15th, 2009 3:14pm

In ConnectionManagers, double click your SourceConnection File. Under the Advanced Section you will see the column parameters for your source columns. Change the OutputColumnWidth to a higher value, this will avoid any truncation as it leaves the source into the destination. HTH! :)

-Michael Felch
  • Proposed as answer by Bill Sempf Friday, June 10, 2011 4:55 PM
June 30th, 2009 12:41pm

Under "choose a data source", you must select the advanced option on the left, and then individually define the output of each column (OuputColumnWidth), regardless if the destination table is already set up correctly (even if you are creating the table on the fly and edited the column values in there, this is they only way to get it to work). Once I did this, my import worked. I had the same text was truncated issues as mentioned above.
  • Edited by supanick78 Tuesday, July 21, 2009 1:33 PM
  • Proposed as answer by Georgevdavis Thursday, October 08, 2009 10:34 AM
Free Windows Admin Tool Kit Click here and download it now
July 21st, 2009 1:29pm

supanick78, bless you. What an easy workaround for something that plagues me constantly. THANKS!!!!
August 18th, 2009 5:37pm

I just want to share that this IS the correct answer - I received a HUGE batch of records from one of our offices, the offending column was titled "Employee Size" which I thought "there is no way in ____ there would be more than 250 characters in one of these fields" --  But sure enough, after adjusting it to 500 the import worked like a charm, and you can use regular management studio from here to format and edit column widths. 

It's a strange world out there.
Free Windows Admin Tool Kit Click here and download it now
October 8th, 2009 10:37am

Hi ryan1999,

Yeah.. this is a good workaround to apply. And the best thing is that it works fine!!! :o) :o) :o) :o)

Thanks a lot man, It was very helpfull!

October 22nd, 2009 11:51am

3-hours later after filtering the data hoping to find the culprit record and it dawned on me that this is probably just another MSFT bug. The scan default is 8 rows ? ROFL. Seriously Microsoft, do you need further examples on why there is so much animosity towards you as a company? It's things like this that makes me dream of never using or looking at anything Microsoft-related.
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2009 5:53pm

I have 250+ columns to do this to.
November 12th, 2009 9:00pm

Here is a workaround that will do the trick - if you have only 1 column giving trouble.

In the spreadsheet, create a new column with the formular : LEN(B2) - where B2 is the column with the long string in it. Copy the formular to the rest of the rows - and sort descending by that column.

Then you'll have the longest string as the top record - and SQL Server will use that when determining the target datatype.

Had the same issue, and thought I'd give sorting by length af try - found this article describing how to that here : http://mr-euro.com/excel-sort-by-length-of-string/

Note : This will only work for 1 column.

Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2010 2:34pm

@ Supanik,

I am using SQL server management studio 2008 and could not find the advanced tab under choose data source ?

Am I looking at the correct window ?

Thanks.

April 12th, 2010 3:07pm

Ronak,

 

Select Flat File as Data source. You will find Advance tab on left.

 

@@ Brindesh

  • Proposed as answer by Brindesh Patel Tuesday, April 20, 2010 10:15 AM
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 10:14am

You can also double click the connection manager,

Click the advanced tab.

Click Suggest Types.

You can then specify the number of rows to use in the sample rather than changing the registry. 

 

June 15th, 2010 1:58pm

Thank you Paul!  Suggest type + setting it to scan all rows has fixed the problem for me.
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2010 9:02pm

That's what I've been doing since the beginning of time.  Using MSAccess to import data into, what should be considered, a superior product in every way.  However, nowadays, with 64 bit Office looming, MSAccess is going to become more difficult to get my hands on, as my company has not budgeted for MSAccess 64 bit version.  So, I'm going to be a DBA that can't import the simplest of data.

Microsoft needs to fix this bug that has been going on for 10 years, or someone needs to find a real workaround.

October 13th, 2011 6:28pm

  • Issue Fixed - Please increase the below registry key value to more then the number of records in your excel sheet, so that the driver could scan all the rows in the excel sheet to determine the default data length. By default microsoft has set this value to only 8 records as a result, if there were rows having more than 255 characters after these 8 rows throws the error  "Text was truncated or one or more characters had no match in the target code page "
    See microsoft article as shown below for complete details,

    Truncated text
    . When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.

    http://technet.microsoft.com/en-us/library/ms141683.aspx

  • Free Windows Admin Tool Kit Click here and download it now
    October 25th, 2011 8:36pm

    Had the same error and wasted 6 hrs!! I finally dumped the SSIS route and qrote up a code for importing with LINQ. That had couple of issues and I went old way declaring OLEDC object etc.. and using bulk insert to sql server and that worked. What a waste of time.
    March 29th, 2012 2:45pm

    I'm confused by the issue... in the case where truncation is listed, the largest length is three and the field is 90 so trancation should never be an issue - UNLESS - unless the second part of this is true where there is a code page issue.   It would be nice if we could set the code page from the import/export wizard.  Meanwhile, save the import/export as a job, then make the changes using the VS2008 (2010/2012) interface and run it again.  Generally as suggested above by unbob - the CTE guy, this can be a white space error that trips the ANSI7 switch and causes a code page error (making it impossible to see what is wrong - another piece that would be helpful for developers working with BI here).

    In this second scenario, try running a conversion (to address code page issues on white space) for each and every column (there needs to be a wizard for this too in the Data BI IDE - this would also be helpful).  It is not uncommon for a client in a hurry to paste junk into a row/column in Excel and then throw it at you from a distance.   Perhaps we need a wizard that removes white space (or at least report it to us and give us the option to remove it)... Jeesh.   Those who work on BI development for Microsoft seem to be unaware that we typically use it to bring in data offered to us in an Excel spread sheet.  NOTE HERE - we do, yep, we use it to import Excel frequently - [says me tongue in cheek].

    Please, pretty please?

    Free Windows Admin Tool Kit Click here and download it now
    August 27th, 2012 5:53pm

    Spot on - but I used hex-view in ultraedit to spot the buggers and replace with a 20.
    January 18th, 2013 12:29pm

    I had similar issue. It was driving me nuts. 

    This Suggestion was really useful and worked. 

    Thanks!

    Free Windows Admin Tool Kit Click here and download it now
    October 29th, 2013 6:18pm

    When importing data from a CSV or TXT make sure to modify the columns width SQL Server auto-detect change the output width of the columns giving the error.

    January 8th, 2014 5:20am

    I realize this is old, but I thought I'd add a few thoughts for those who are still finding this (like I did today).

    1. - Definitely find out what your maximum length in your field is.  Instead of copying the LEN(A2) all the way down and taking a max, you can do that in one formula.  In Excel, insert a new first row right above your headers.  Enter =Max(Len(ColumnRange)).  You must do Ctrl+Shift+Enter to enter the array formula.  You can't Copy/Paste this, but you can grab the cell at the bottom right (where the white cross hair turns into a small black one) and drag it across all of your columns.

    2. - I was running into this problem today and could not figure out why my import failed.  I followed everything else suggested in this form.  Finally, I went to my table in SQL Server Management Studio, selected Edit Top 200 Rows, and started pasting them 1 by 1.  (I only had 20 rows in my file).  I found the error on row 9 where it inserted the first 10 columns, and then was NULL for all the columns after.  Come to find out, there was a line return within my Address1 field in the data.  (If you type Alt+Enter in Excel, it puts a line return within the single Cell.)  I removed the line return from my data, and it imported perfectly.

    Free Windows Admin Tool Kit Click here and download it now
    May 15th, 2014 11:28pm

    I found this thread today as I was working (OK, struggling) with the same issue. I don't have the same technical level as most of the commenters, but here's what I found to be a practical fix:

    The odd space issue was fixed by exporting the database table to .CSV format and opening in MS Excel (2010, if that matters). We then did a manual scan of the document, and found that there were a bunch of otherwise normal entries that had a pound sign (#) in them. We replaced them with ordinary spaces using the bulk replace command(CTRL + H, then Replace All), saved the document, and it imported into SQL Server smoothly.

    I hope this helps. Cheers.

    August 1st, 2014 10:07pm

    Here is a workaround that will do the trick - if you have only 1 column giving trouble.

    It might have been nearly four years ago but thanks for this - instead of wasting several hours on this I lost only one. In my case even though I had set the destination field to nvarchar(max), the import was still giving me the "Text was truncated or one or more characters had no match in the target code page." error.

    Sorting records by descending length of the offending field worked for me.

    Free Windows Admin Tool Kit Click here and download it now
    January 13th, 2015 1:45pm

    This method is effective even in later versions of SQL that still have the same "challenge" when using SSIS to import a csv.   Even with the columns defined at max, anything over 255 was creating the truncation error.  After identifying the larger pieces of data and putting them up top, the import worked just fine.  Thanks!!!
    August 29th, 2015 2:38pm

    This topic is archived. No further replies will be accepted.

    Other recent topics Other recent topics