August 27, 2012 at 3:20 pm
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.
August 27, 2012 at 3:50 pm
How are you determining that the data is being cut off at 255 characters?
August 27, 2012 at 4:14 pm
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.
August 27, 2012 at 4:32 pm
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.
August 27, 2012 at 5:11 pm
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.
September 5, 2012 at 2:05 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply