I have to import a flat file with commas and double quotes as the text qualifier in a SSIS package. However, when I try to import the data into a table, the data moves the information to right, therefore, the last field will capture mulitple fields worth of data. When I create the Connection Manager for the flat file, I have the format as [Delimited] and Text qualifier as ["]. I do not check the unicode button, but under Advanced, make each field a Unicode string [DT-WSTR]. I have included a sample of my data below. "Internal Sales Document ",9/23/2005 0:00,0.58,"STORES ISSUES","TAPE, PACKING, 2" X 55 YD, CLE ","EP0079771","US363800","2065431980""Internal Sales Document ",10/7/2005 0:00,3.76,"STORES ISSUES","Post-It Note Pads, 3"x3", Cana ","EP0079770","US363799","2065431980" As I highlighted above, I will have a description field that will have double quotes and commas within the text before the end of field. Please let me know if this is user error. Thanks,

Can you get the flat file in a different format such as Tab-delimited, or better yet, fixed-width?SSIS doesn't really handle embedded quotes too well.

No,the only way I can get the datais in this format. Right now I am importing the data onto a SQL 2000 server and then extracting out of that server into a SQL 2005 server. This has to be a temporary fix and the dataset is too large to manually manupulate. Thanks,

This is a pretty simple problem. Solving it is not so easy. How can SQL Server know that the " it encounters is the end of a column or in the contents of a column? YOu could write some custom .NET application to scrub the file and remove all " where its at the beginning of a line or followed by a comma, or at the end of the line. Then process them as a simple comma delimitted text file. And as far as extra commas go you will have to change the delimitter to a | or something

Is there another way. I don't have the an application that can handle the size of my dataset. Is there something in SQL 2005 that can be scrubbed this for me before importing into a table? Thanks,

You can pull each line in as a single string column, and do the parsing yourself in a script component. Still not a easy process though. Here's a sample that might get you started: http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

I was able to get around this issue by using the the Advanced column feature set. So here's my example flat file: "Name","Height",Weight "Joe","6'3"",185 Under the Advanced column properties for the Height field, I change the following settings: Column Delimeter: "{,} Text Qualified: False Now when I preview, I get the following: Name Height Weight Joe "6'3" 185 Notice I have an extra double quote (") at the beginning of the data but what I reallywantis 6'3". To fix this I add an column called QuoteCatcher before the Height column with the following properties: Column Delimeter: " Text Qualified: False I also set the field length to 1 but I dont' know if this is necessary since it's just a throw away column. Now when I preview, I get the following: Name QuoteCatcher Height WeightJoe " 6'3" 185 This seems excessive to me but it seemed easier than the script task option since (in my case) I only had 1 column to correct.

This is still an issue in 2011. What a shame.

I have to import a flat file with commas and double quotes as the text qualifier in a SSIS package. However, when I try to import the data into a table, the data moves the information to right, therefore, the last field will capture mulitple fields worth of data. When I create the Connection Manager for the flat file, I have the format as [Delimited] and Text qualifier as ["]. I do not check the unicode button, but under Advanced, make each field a Unicode string [DT-WSTR]. I have included a sample of my data below. "Internal Sales Document ",9/23/2005 0:00,0.58,"STORES ISSUES","TAPE, PACKING, 2" X 55 YD, CLE ","EP0079771","US363800","2065431980" "Internal Sales Document ",10/7/2005 0:00,3.76,"STORES ISSUES","Post-It Note Pads, 3"x3", Cana ","EP0079770","US363799","2065431980" As I highlighted above, I will have a description field that will have double quotes and commas within the text before the end of field. Please let me know if this is user error. Thanks, If both the qualifiers and the separators are incorrect its hard to get a correct value. I strongly recommend to get a better csv file from your source. This is still an issue in 2011. What a shame. Is it...? It's just not a correct csv file... you have to know where a text ends. You can't use the qualifiers in this case because they exist in the columntext, but you can't ignore them because there is also a column separator in the columntext. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com