Importing CSV to SQL Server with text column > 8000 characters

sql-server import ssms sqldatatypes truncation
Michelle Williamson picture Michelle Williamson · Sep 19, 2013 · Viewed 9.7k times · Source

I'm trying to import a csv file with two columns (sku, description) into SQL Server 2008 using the SQL Server management studio 2012 import/export wizard. Because the description column definitely has rows greater than 8000 characters, I go to the advanced tab when choosing the csv data source and click on the description column and click "Suggest Types". It then puts in 16718 for the OutPutColumnWidth property. Apparently there is a description in there somewhere that is THAT long.

The sql it generates is: 

CREATE TABLE [dbo].[mag-prod-descriptions1] (
[sku] varchar(7),
[descrip] varchar(16718)
)

However, when I execute the import, I get the error "Could not connect source component. Error 0xc0204016: SSIS.Pipeline: The "Source - mag-prod-descriptions1_csv.Outputs[Flat File Source Output].Columns[Column 1]" has a length that is not valid. The length must be between 0 and 8000."

If I change the OutputColumnWidth property to 8000 then I get an error saying the column was truncated. I can't win.

How do I get the thing to allow me to import cells that are greater than 8000 characters?

Answer

Michelle Williamson picture Michelle Williamson · Sep 19, 2013

Ack. Finally got it. The answer was to use the Text Stream datatype for the large column on the source file.

Related questions

Import / Export database with SQL Server Server Management Studio

I thought this would be trivial, but it isn't... I'm sure there is a simple way to do it but I can't manage to find it. Shame on me. I want to import/export the database itself, the tables, the …

Read More
SQL Server: Importing database from .mdf?

I have an .mdf file on my local box. I have SQL Server 2008 Express and SQL Management Studio 2008 Express installed on my local box. How in the world do I import this .mdf file as a new database into my …

Read More
Import Excel spreadsheet columns into SQL Server database

I have an Excel spreadsheet that I want to import select columns into my SQL Server 2008 database table. The wizard didn't offer that option. Do any easy code options exist?

Read More