SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94379 Visits: 38955
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)
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94379 Visits: 38955
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

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)
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
grantbanjo
grantbanjo
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 170
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search