SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 error importing from excel to sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhizi
Starting Member

Albania
6 Posts

Posted - 07/19/2013 :  03:14:11  Show Profile  Reply with Quote
Hi,

I'm trying to import data from excel file to sql server. It copies some rows and than get this error messages:

* Error 0xc020901c: Data Flow Task 1: There was an error with output column "Subject_Name" (21) 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 "Subject_Name" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Subject_Name" (21)" 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)


I tried to set all the datatype to varchar(max) and to ignore fail on truncation, but i get the same error. Please any help.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/19/2013 :  03:58:12  Show Profile  Reply with Quote
Error seems to be due to fact that datatypes of Subject_Name column is not same between source and destination. See whats the datatype it assumes in metadata from Excel. You need to use same datatype for destination or need to apply a derived column/data conversion transform in between to make them same.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xhizi
Starting Member

Albania
6 Posts

Posted - 07/19/2013 :  05:07:54  Show Profile  Reply with Quote
Can you be more specific, please?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/19/2013 :  05:14:19  Show Profile  Reply with Quote
More specific? I thought I've already suggested what exactly you need to do.
These are the steps

1. Select the excel metadata (click on green line from excel source) and check the data type for Subject_Name column
2. See your destination table's corresponding columns datatype
3. Make then same if possible by changing datatype of table. If you cant change table, add a derived column tranformation or data conversion tranformation in between excel source and table and add an expression to cast the data type of Subject_Name source column to required type.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xhizi
Starting Member

Albania
6 Posts

Posted - 07/19/2013 :  05:36:42  Show Profile  Reply with Quote
Thanks for your help!
Go to Top of Page

dgh898
Starting Member

10 Posts

Posted - 08/08/2013 :  23:11:08  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000