Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

While Import data into Sql server 2008 from excel sheet Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 1:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 3:53 AM
Points: 16, Visits: 18
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)
Post #1378591
Posted Tuesday, October 30, 2012 1:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 6:57 PM
Points: 81, Visits: 333
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.
Post #1378594
Posted Tuesday, October 30, 2012 1:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 3:53 AM
Points: 16, Visits: 18
When change datatype max then also get same error
Post #1378597
Posted Tuesday, October 30, 2012 1:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1378598
Posted Tuesday, October 30, 2012 2:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 9:03 PM
Points: 1,128, Visits: 1,161
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
Post #1378613
Posted Tuesday, October 30, 2012 2:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1378616
Posted Tuesday, October 30, 2012 3:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 3:53 AM
Points: 16, Visits: 18
When import file by default it takes nvarchar(255) then change this datatype to nvarchar(max)
then also it shows same error
Post #1378640
Posted Tuesday, October 30, 2012 3:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1378643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse