October 30, 2012 at 1:28 am
While Import data into tables(in Sql server 2008) from excel sheet i got this error
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with output column "Executive Biography 32" (495) 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 "Executive Biography 32" (495)" failed because truncation occurred, and the truncation row disposition on "output column "Executive Biography 32" (495)" 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 - 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. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
October 30, 2012 at 1:31 am
The data which you are importing might be having text bigger than columns size of table.
Maximize the size of the table column and then try.
October 30, 2012 at 1:34 am
When change datatype max then also get same error
October 30, 2012 at 1:48 am
Having come across this error a lot, its actually on the source connection, there must be data that is greater than the column length definition.
One way you can check this is to get a copy of the excel sheet you are importing, insert a new column next to the one that is causing you a problem and type in =Len(<cell ref>) and copy this through all columns, then filter the sheet on anything that is bigger than the column definition in SSIS.
It will also give you an Idea what you max column width should be.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 30, 2012 at 2:44 am
sreenu_rvkl (10/30/2012)
When change datatype max then also get same error
I once face this issue while loading an image in binary format and saving it to the database ..
If the length more than 8000 , the size of max it throws an error....
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
October 30, 2012 at 2:54 am
By default when importing from CSV XLS etc the source columns in the excel connection are set to 50 characters, you need to go in and change this to the correct sizes for the columns then re-import to the DB.
October 30, 2012 at 3:42 am
When import file by default it takes nvarchar(255) then change this datatype to nvarchar(max)
then also it shows same error
October 30, 2012 at 3:51 am
we're actually talking about the base connection in connection manager, as far as im aware they dont use nvarchar(MAX), as a data type its likley to be DT_WSTR.
if you open that and look at the column thats giving you a problem you will probably see the size is set to a DT_WSTR with a length of 50, increase this to be the correct field length.
After this you will then see a warning triangle on the Excel connection task, simply double click this to open it and refresh the metadata within the data flow, it will probably warn you that the data types are going to be changed, then close it down.
You can check this has changed by viewing the meta data of the Data flow and making sure that the column has actually changed to match the value in the source.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply