August 26, 2013 at 7:59 am
some column in excel has lengthy data so,
im gettin the following error while importing Data from sqlserver2008:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with output column "Description" (33) 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 1: The "output column "Description" (33)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (33)" 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 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - 'Request Tracking$'" (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. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
-------------------------plz suggest im using import wizard from sqlserver2008
August 26, 2013 at 2:33 pm
First, this is an SSIS issue, not a T-SQL question, so it should really be posted in the SSIS forum.
The problem is that Excel determines the field lengths/types by sampling the first EIGHT rows, which is almost never sufficient for an accurate determination. There is a way to change the default sampling amount, but I don't remember what it is right now. I usually save the file as a delimited text file which don't have these problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2013 at 3:07 pm
To change the 8 rows lookup, you need to modify the registry key TypeGuessRows of the ACE OLE DB provider.
(there's a link in my signature with more info, but unfortunately the site is down right now)
Also modify the length of the column in the advanced editor of the source component to something bigger, for example 4000.
If the column is still too wide, you'll need to import the data as DT_TEXT.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply