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

    Question

  • 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)
     



    Tuesday, May 13, 2008 8:01 PM

Answers

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

    Kurt
    Wednesday, January 07, 2009 4:09 PM

All replies

  • 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
    Tuesday, May 13, 2008 9:40 PM
  •  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.

    Wednesday, May 14, 2008 4:04 AM
  • 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.

    Kurt
    Wednesday, January 07, 2009 4:09 PM
  • 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 the destination table are nvarchar(255).  I can see 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 the csv file format.  When I save it as xls the import worked.
    Monday, February 02, 2009 8:25 PM
  • 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
    Wednesday, April 01, 2009 7:41 PM
  • 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
    Thursday, June 11, 2009 1:22 AM
  • 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
    Monday, June 15, 2009 3:14 PM
  • 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
    Tuesday, June 30, 2009 12:41 PM
  • 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
    Tuesday, July 21, 2009 1:29 PM
  • supanick78, bless you.  What an easy workaround for something that plagues me constantly.  THANKS!!!!
    Tuesday, August 18, 2009 5:37 PM
  • 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.
    Thursday, October 08, 2009 10:37 AM
  • 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!

    Thursday, October 22, 2009 11:51 AM
  • 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.
    Tuesday, November 10, 2009 5:53 PM
  • I have 250+ columns to do this to.
    Thursday, November 12, 2009 9:00 PM
  • 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.

    Tuesday, March 23, 2010 2:34 PM
  • @ 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.

    Monday, April 12, 2010 3:07 PM
  • 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
    Tuesday, April 20, 2010 10:14 AM
  • 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. 

     

    Tuesday, June 15, 2010 1:58 PM
  • Thank you Paul!  Suggest type + setting it to scan all rows has fixed the problem for me.
    Thursday, June 17, 2010 9:02 PM
  • 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.

    Thursday, October 13, 2011 6:28 PM
  • 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

Tuesday, October 25, 2011 8:36 PM
  • 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.

    ss

    Thursday, March 29, 2012 2:45 PM
  • 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?


    R, J

    Monday, August 27, 2012 5:53 PM
  • Spot on - but I used hex-view in ultraedit to spot the buggers and replace with a 20.
    Friday, January 18, 2013 12:29 PM
  • I had similar issue. It was driving me nuts. 

    This Suggestion was really useful and worked. 

    Thanks!

    Tuesday, October 29, 2013 6:18 PM
  • 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.

    Wednesday, January 08, 2014 5:20 AM