Error converting data type varchar to numeric

I have faced this error "error while converting data type varchar to numeric" several times when converting string data to numeric. Every time I found a unique solution .

This usually happens in a scenario when we do not have much control over the data which is being pulled from different source, which again could be a private internal data or data from external public sites.

Recently in one of my projects while pulling data from external url I faced this issue to convert string data to numeric.

Following are few steps I follow to troubleshoot this error :-)

• Make sure the column used for conversion doesn't have any visible special characters. If any then try to replace them. 
• Make sure to use the replace for non visible characters like TAB, Line Feed, Carriage Return. In T-SQL these are can be identified using CHAR function
• TAB => CHAR(9)
• Line Feed => CHAR(10)
• Carriage Return => CHAR(13) (Resource: MSDN )
• So use the replace function to replace the above non visible characters with '' (space).
• There are few scenario's where after doing all these I still get the error. To solve this I recently used the ASCII() function. Use this function to know if there are any unwanted non visible data in the column. Probably update the column to '0.0' where ASCII(ColumnName) IS NULL.

Let me know if you are aware of any other methods to handle this error.

SSIS error: [Flat File Destination [187]] Error: Data conversion failed. The data conversion for column "col1" returned status value 4 and status text.

If you have worked on SSIS tool, at some point while working in Dataflow task we will face this issue.
Error statement : "[Flat File Destination [187]] Error: Data conversion failed. The data conversion for column "col2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

This usually happens when we are loading data from database source to flat file destination or if loading from flat file source to database destination.
I assume it occurs more often when it is database source to flat file destination.

Here are the few steps to troubleshoot this issue. I usually follow the same order.

• Run the query in SSMS and see if you are seeing some dirty data. Usually this happens when we pull data from different platform like Mac or Linux. Example the file path.
• Right click on flat file destination, click on Show advance editor and go to Input and output column tab. On external columns see if the data type and length of the col2 (here in example) is as per request. Should be wide enough . Match the same with input columns.

Once you are sure the data types are matching, go to next step.

• Double click on the destination file connection in connection manager and make sure if you have checked unicode option or not. If you are using nvarchar or DT_WSTR as datatpe then I would suggest to check this option.

I have used above steps most of the times and it has worked for me.

Let me know if you are aware of any other trouble shooting methods in SSIS for the above mentioned error.