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)
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
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.
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.
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.
... Finally got it to work. Problem is something to do with thecsv file format. When I save it asxls the import worked.
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
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
You can edit your registry to specify the look ahead when figuring data length.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.
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
- Proposed as answer by Michael Felch Tuesday, June 30, 2009 12:39 PM
-Michael Felch
- Proposed as answer by Bill Sempf Friday, June 10, 2011 4:55 PM
- Edited by supanick78 Tuesday, July 21, 2009 1:33 PM
- Proposed as answer by Georgevdavis Thursday, October 08, 2009 10:34 AM
It's a strange world out there.
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!
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.
@ 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.
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
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.
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.
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
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?
I had similar issue. It was driving me nuts.
This Suggestion was really useful and worked.
Thanks!
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.
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.
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.
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.