Home Sign In

Steven Van de Craen's Blog

Bloggings about SharePoint, .NET and more.

MVP SharePoint Server

RSS Feed
  • Contact Me

Archives

July 2007 (19)
August 2007 (5)
April 2008 (4)
September 2007 (3)
November 2007 (12)
October 2007 (4)
December 2007 (8)
January 2008 (6)
February 2008 (3)
March 2008 (4)
June 2008 (2)

Categories

  • SharePoint 2003
  • SharePoint 2007
  • General
  • Office 2003
  • Office 2007
  • Excel Services
  • Dynamics NAV
  • SOAP
  • Search
  • Search Server 2008
  • .NET
  • CKS
  • IIS

Recent Posts

  • Browsable Hyperlink fields in SharePoint 2007
  • Working with the AssetUrlSelector
  • Rooms and Equipment Reservations v2 (UNOFFICIAL)
  • Being a SharePoint MVP...
  • New MVP on the block

Other Blogs

//BLOG: naked programmer
JOPX
Jan Tielens' Bloggings [MVP]
Serge van den Oever [Macaw]
Carlos Segura Sanz
-->Patrick Tisseghem's Blog [MVP SharePoint] -->
Remco Ploeg's Blog
SharePoint Blogs
Romeo Pruno
[email protected]
Andrew Connell [MVP MOSS]
Mark Harrison
Pedro Serrano
Clemens Vasters and the Indigettes
Kevin Boske
SharePoint Team Blog
Brian Jones: Office XML Formats
Mart Muller's Sharepoint Weblog
Joel Oleson's SharePoint Land
Kris' blog
STSADM Custom Extensions
Microsoft Enterprise Search Blog
Michaël's coding thoughts
Chris O'Brien's blog
John Holliday's SharePoint Reflections
Koen's blog
PDT IT Services Blog Posts

SQL Server 2005: Importing from Excel error (repost)

Something weird that occured today: I had an Excel file that I needed to import into my SQL Server 2005 database. I followed the instructions but kept getting an error.

Problem

Error 0xc020901c: Data Flow Task: There was an error with output column "Agenda 2" (63) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Cause

The Jet driver by default looks only at the first 8 columns to guess the maximum size of the rows. So if your first 8 columns are smaller than 255 characters and column9 has more characters it will crash.

Solution

Make a change to your machine's registry to increase the number of columns to check for:
http://support.microsoft.com/kb/281517/EN-US/

Note

The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.

 
Categories: General

Trackback Url

 

Comments

Thursday, 27 Dec 2007 07:53 by berakcrust
Hi, cool findings. Me got the same kinda probs. Error as follows :- -------------------------------------------------------- * Error 0xc020901c: Data Flow Task: There was an error with output column "TI" (15) on output "Excel Source Output" (9). 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 "TI" (15)" failed because truncation occurred, and the truncation row disposition on "output column "TI" (15)" 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: The PrimeOutput method on component "Source - Sheet1$" (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. (SQL Server Import and Export Wizard) --------------------------------------------------------- any suggestion of the ideal number of columns to be increased? Suppose to key in 0 and 16 rite? Really appreciate your kind attention. thxs

Thursday, 27 Dec 2007 07:53 by Steven Van de Craen
Just set the registry key value to 0 so that it scans for all columns.

Thursday, 27 Dec 2007 07:53 by berakcrust
Thanks Steven. It works!

Thursday, 27 Dec 2007 07:53 by hemant kumar
thats great finding its problem of conversion of ascii charachet to unicode character , Just set the registry key value to 0 so that it scans for all columns. it will work hundred percent

Thursday, 27 Dec 2007 07:53 by Manish
Great work. It definately works.

Wednesday, 9 Jan 2008 02:43 by Aram
Thank u very much I hav tried a lot of things to overcome this problem at last I managed to import data from excel by configuring error output for SSIS package (see ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/53d7eeea-927d-4b45-8ea9-084e65ad5390.htm) But the best solution is describes in this topic and ut works Thanks

Thursday, 17 Jan 2008 03:01 by Andrea
sorry but this solution don't work at all.....

Friday, 25 Jan 2008 02:55 by goodd
good job(emeğe saygı)

Wednesday, 6 Feb 2008 05:03 by Kyle
Very helpful... thank you.

Wednesday, 23 Apr 2008 11:02 by anonymous
Thanks Steven, it saved a lot of time researching on the solution.

Wednesday, 4 Jun 2008 02:27 by Franziska
I've got the same error-message but I will import into sql from a csv-file, not excel. If I change the registry key from 8 to 0 it doesn't work... Any ideas?

Leave a comment

Name

Url

Email

Comments

CAPTCHA Image Validation

© 2007 Steven Van de Craen — Powered by Community Kit for SharePoint: Enhanced Blog Edition.