Length of field imported from CSV file cut off at 255 characters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Length of field imported from CSV file cut...

Length of field imported from CSV file cut off at 255 characters

Rate Topic

Display Mode

Topic Options

Author

Message

grantbanjo

Posted Monday, August 27, 2012 3:20 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 18, Visits: 81

Hello,
I am using SSIS in SQL Server 2008 R2 and am importing a field called "Notes" from a pipe-delimited CSV file. The values in the Notes field of the CSV file can exceed 255 characters, but when imported to a staging database table where the Notes field is defined as a varchar(1000), it is consistently cut off at 255 characters.

The CSV source connection defined in SSIS for the CSV file shows a length of 1000 for notes. A data conversion is being applied prior to the import of the fields to the OLE DB Destination database table. In this data conversion the Notes field is defined as string [DT_STR] with a length of 1000 and Code Page of "1252 (ANSI - Latin I)."

Because the values in the Notes field can be either blank or have fewer or more than 255 characters, how do I set up this field in SSIS so that it can import more than 255 characters from a CSV source file?

Thanks.

Post #1350620

Lynn Pettis

Posted Monday, August 27, 2012 3:50 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,832, Visits: 27,846

How are you determining that the data is being cut off at 255 characters?

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1350629

grantbanjo

Posted Monday, August 27, 2012 4:14 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 18, Visits: 81

I ran the following query:

SELECT Notes
FROM Tasks_import
WHERE len(Notes) = 255

Then I copied and pasted several values from the Notes field results into NotePad and I could see that the sentences were cut off. This also returned about 40 records. When I ran the following query looking for Notes values greater than 255 characters in length, no results are returned.

SELECT Notes
FROM Tasks_import
WHERE len(Notes) > 255

I also spot checked the CSV file for several of the 40 records mentioned about and could see that there is more information in the CSV file than is being imported to the database table and cut off at 255 characters.

Post #1350637

Lynn Pettis

Posted Monday, August 27, 2012 4:32 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,832, Visits: 27,846

Try the following:

Click on the Tools menu, select Options.
Go to Query Results -> SQL Server -> Results to Grid, change Maximum Characters Retrieved for Non XML data to 4000.

Do the same for Resutls to Text.

Rerun your queries and see if you notice any thing different.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1350640

grantbanjo

Posted Monday, August 27, 2012 5:11 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 18, Visits: 81

Lynn, I applied the steps you recommended, but when I reran the same two SQL statements afterward, the results were the same. No records were returned having a Notes value with a length greater than 255, but there are a bunch records with Notes values having a length equal to 255. The same problem continues.

I appreciate your suggestion, because the Results to Text was limited to 256. I changed that to 4000. The Results to Grid > Non XML data was set at around 65,000. I changed that to 4,000 as well.

Post #1350647

grantbanjo

Posted Wednesday, September 05, 2012 2:05 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 18, Visits: 81

I was able to resolve this issue with a simple modification. The file being imported had a .csv extension and that was causing SSIS to interpret it as related to Excel formatting with a limitation of 255 characters per column... regardless of how I defined the field size in the import process. The fix was changing the import file's extension to .txt and modifying the Connection Manager to look for the file with the updated extension. Now when the field is imported with values less than and more than 255 characters, everything comes through. SSIS no longer associates the import file with Excel and automatically applies the expected limitations.

Lynn, I greatlhy appreciate your help with this issue.

Thanks.

Post #1354874

« Prev Topic | Next Topic »

Permissions