Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Length of field imported from CSV file cut off at 255 characters Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 3:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:00 PM
Points: 27, Visits: 146
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
Posted Monday, August 27, 2012 3:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
How are you determining that the data is being cut off at 255 characters?



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
Posted Monday, August 27, 2012 4:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:00 PM
Points: 27, Visits: 146
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
Posted Monday, August 27, 2012 4:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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.



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 #1350640
Posted Monday, August 27, 2012 5:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:00 PM
Points: 27, Visits: 146
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
Posted Wednesday, September 5, 2012 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:00 PM
Points: 27, Visits: 146
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 »

Add to briefcase

Permissions Expand / Collapse