Error in importing data from excel to sql

[Serigraphie]

To all database genius out there,
I hope you can help me with this error I encountered when importing excel file to SQL Server 2005.

This is the error:

Collapse | Copy Code
Executing (Error)
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with output column "Description" (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 "Description" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (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 - 'Data $'" (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)
Luckily I have a hint what the problem is all about : It is because data type in excel can only accommodate nvarchar 255, and when I import it, it is beyond nvarchar 255, and that it contains special characters like : ',; and chinese characters.
The thing is, I ran out of solutions.
Thank you in advance for those who will help.
Appreciate it. .

[GaryMazzone]

Are you using the Imort Wizard or SSIS? (They are the same actually just in how nyou generate them). There is a way in the package to tell the import to Ignore Truncation Errors or to change the size of the field coming in to a larger size.

[Serigraphie]

Hi GaryMazzone,
Thanks for your response. I am using Sql Server 2008 management studio, I right click my database and then tasks, then import data.
Can you tell me how to ignore truncation errors?
I tried edit mappings -> changed it to nvarchar(max) but the error still occured.
Thank you again.

[GaryMazzone]

You can't from there. You will need to save as an SSIS package and then open that In BIDs and edit it there.

[Serigraphie]

But can I still import it using import task without the errors? or should I really use the SSIS?
If so, can you tell me how?
Is SSIS downloadable or it comes with SQL Management studio?
Thank you GaryMazzone!

[GaryMazzone]

SSIS is Sql Server Intergation Services and comes with SQL Server. What version of SQL Server do you have? An SSIS package is what the Import Wizard creates for you (you just don't see it). If you notice on of the steps asks if you want to Run Immediately or Save as SSIS package.

SSIS packages are edited in BIDs studio (this is a visual studio interface for working on SSIS packages).

[Serigraphie]

SQL Server 2008 Management Studio, I cant see the SSIS package notice, maybe I have the wrong version of SQL?
thanks GaryMazzone!

[GaryMazzone]

Management studio has nothing to do with the version of SQL server you are using... Are you using Express?

[Serigraphie]

Thank you for correcting me, yes I am using express.

[GaryMazzone]

Then you don't have the option of doing this (saving as SSIS package). Are you just doing a Select * from the Excel sheet?

[Serigraphie]

Yes, just a select *, by the way I tried copy pasting and it worked but the downside of it is that it's making my computer to slow down and it takes 30 mins to paste 5,000 of rows. Should I use the developer edition of SQL to have the SSIS package option?

[GaryMazzone]

I would.... The problem with select * is you have no control. I would try listing each field and for the string (text) fields doing a substring from 1 to the max length you want.