I always prefer to load my data from a text file (tab delimited) to SQL table, rather than using a xls or csv. The drawback of using csv(comma separator) is that the data should not have any comma (,). The drawback of using xls is if there is a column with data as numbers as well as characters, it considers only numbers and all character values are entered as NULL. So, I decided to use .txt files to load data to avoid these confusions. But now i have a new problem with txt files. If there is any special character in a text, it adds " " automatically. Suppose, there is a Description column value - ABCD,GHJK. When SSIS loads this value frm a text file to SQL table varchar column it loads as - "ABCD,GHJK" (with quotes.) this is OK for varchar columns. But the problem occurs when the destination column is a decimal type. Suppose there is a value 60,000 in the xls file. When I convert into .txt format, it automatically adds quotes for the numbers and SSIS throws error when trying to insert "60,000" into a decimal type column. How to handle this problem? Is there anyway i can clean up data for such cases?? Pls help. Thanks.

The Flat File Source component has a property called Text Qualifier. Set this property to equal " Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

The Flat File Source component has a property called Text Qualifier. Set this property to equal " Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD Wow it worked. Could you please tell me wat does this property 'Text Qualifier' do exactly??

It tells the Flat File Source component that your data is enclosed in some sort of qualifier (in your case quotes " "). The component essentially uses this to strip out the qualifier when processing the dataJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

It tells the Flat File Source component that your data is enclosed in some sort of qualifier (in your case quotes " "). The component essentially uses this to strip out the qualifier when processing the data Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD Suppose i wnt to remove characters like $ % also, Can I include more than one qualifier in that place?

No. The qualifier is used purely to distinguish what data is included in each column. You would need to use a Derived Column transformation to do this.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

No. The qualifier is used purely to distinguish what data is included in each column. You would need to use a Derived Column transformation to do this. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD I hope I should use some String functions like REPLACE() in Derived Column right?

That is correct. You could use the REPLACE( «character_expression», «search_expression», «replace_expression» ) functionJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

That is correct. You could use the REPLACE( «character_expression», «search_expression», «replace_expression» ) function Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD Thank you for all your patient replies. It helped me.

No problem and thanks for marking your question as answeredJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD