I used this for my solution and seems to work just fine. We faced a problem with creating extract files that an Excel PowerPivot is to import and if the data contained a quote it would mess up the imported data as a tab spaced format. So it was trying to be too smart for its own good, but no option to override the qualifier detection. So the solution that is working is to just replace all quotes with a double quote and sure we could have done this on the SQL query during the extract, but we are not sure which text based columns may have a quote and rather than editing all the queries for all columns that might have the situation, this worked out much faster and got the job done.

So create a script task and pass in your filename, mine is CurrentFile:

public void Main()

{

string fileText;

try

{

fileText = System.IO.File.ReadAllText(Dts.Variables["CurrentFile"].Value.ToString());

fileText = fileText.Replace("\"", "\"\"");

System.IO.File.WriteAllText(Dts.Variables["CurrentFile"].Value.ToString(), fileText);

Dts.TaskResult = (int)ScriptResults.Success;

}

catch (Exception ex)

{

Dts.TaskResult = (int)ScriptResults.Failure;

throw ex;

}

finally

{

fileText = null;

}

}