Sql Server Import Wizard Error

[David92595]

Hello,

Basics:Using access/excel 2007, SQL Server 2008 R2

I keep getting the same error from the SQL Server Import Wizard. I've been trying to import my db, and I have all of it except for the last 2 columns. I had to leave them out to get the rest of the data in... The two remaining columns are both memo's or varchar (max).

The data is originally from an access db, but I exported the table to an excel document to make general changes easier. Changes such as eliminating erroneous characters that SQL will not allow. I'm working with an old db that was set up and used by individuals who didn't really know what they were doing.

Back to the point...

When I try to import both of the last columns I get the error:

Executing (Error)[/size] Messages

· Error 0xc020901c: Data Flow Task 1: There was an error with output column "F3" (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 "F3" (21)" failed because truncation occurred, and the truncation row disposition on "output column "F3" (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 - Sheet2{:content:}quot; (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've even taken a very small portion of the column (100 rows) and tried to bring that into the SQL db with no luck. I'm faced with finding an answer to this or manually inputting close to 20,000 records!

PLEASE help.

David92595

 

[bparkinson]

Sounds like the data are too big. You probably have data that exceed 8000 characters (or whatever SSIS decides is too big).

I would try creating a destination table on SQL Server with a column of data type VarChar(MAX). It's wasteful, but it will hold 2GB. Try importing to that table and see what happens.

 

[tehNellie]

Agree that it sounds like the data in the excel sheet is more than the table column is set to receive.

8000 characters is the maximum that a Char/Varchar data type can accomodate, but if the column you're trying to import to is simply set smaller (ie Varchar(10)) that may cause the error. What's the column length set to and what's the longest length of data in the spreadsheet?

Might be a more efficient means than simply setting the column blindly to accept up to 8000 characters and might raise a question as to whether the data type is set too small or there's too much information in the data that you're trying to import.

 

[David92595]

I would like to thank both of you for your suggestions on this topic. None of my cells are greater than 8000 charaters. The largest I have is approximatly 350 charaters.

I found what was causing the error. When importing to SQL from excel the maximun number of charaters excel will export is 255 charaters per cell. so my cells that had more than 255 charaters were being rejected...causing the error.

 

[bparkinson]

I learned something very useful. Thanks.

Did you put the excel table out to a csv file, or is there a similar limitation there as well? How did you get around this limit?

 

[David92595]

I found some vba code that replaced what was in the cell with a tracer, in my case I used "ABCDE". I was then able to import the excel sheet, and now I am currently going through and manually entering the cells that were greater than 255 by searching for "ABCDE".

If you'd like the code I used, let me know...

*Note: I actually ran the vba code with anything >255 and it didn't work, so I lowered the limit to 240 (to be safe) and it worked fine.

 

[Galaxiom]

now I am currently going through and manually entering the cells that were greater than 255

Manual entry?

Have you tried importing the table from Excel to Access and using a query to update the server table?

If that suffers from the same problem you could write code to read the spreadsheet and write to the server table.

 

[David92595]

I did think of that, but the results were pretty good. I only had to manually enter 100 records out of 13,000...I thought I'd just take the hit and get it done.

 

[Galaxiom]

Sometimes the direct but mindless "I know this will get it done" technique can be very appealing.

 

[David92595]

yes it can.

...that and I somehow changed a setting in access which protectsmy access db's from being opened by other users, and I don't know how to turn it off. I.E. the SA username I use when logging into the MSSMS...

 

[Galaxiom]

Soounds like you need to ask a new question.

I work on a domain and use Windows Authentication based on User Groups with our SQL Server. It is very easy to use.