Closing the loop here. I had to knock the dust off my c# to create the below. however it worked for me.

The problem it solves: The issue is i have .csv text qualified files. in these files on occasion i'd get a doule quote in the data itself and ssis who chock.

example below

1000000,213452,"hello guys","This is the string that "would break ssis","goodbye world"

The double quote in front of the word "would" is whats killing it. That should be two double quotes and it would treat it as one and process correctly.

The below visual studio 2010 c# code run as an .exe will parce the folder it is stored in and in this case loop through every file that has the string "18_CREATIVE_" and "CSV" in it's name. It will then do several replaces to find and replace all single double quote in the data and replace it w/ two double quotes. Lastly it calls a proc passing in a string that has the filenames it went through. This proc sends an email to my team specifying what files were processed. This portion is commented out however but u get the picture. Hope this help the next poor sap who stumbles upon this issue

using System;

using System.IO;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

namespace TextQualifierInDataClenser

{

class Program

{

static void Main(string[] args)

{

string OutputEmailBody = "";

int sendMail = 0;

string CurrDir = Directory.GetCurrentDirectory();

Console.WriteLine(CurrDir);

foreach (var file in System.IO.Directory.GetFiles(CurrDir))

{

int LOCAL = 0;

int isCreative = 0;

string uFile = file.ToUpper();

LOCAL = uFile.IndexOf(".CSV");

isCreative = uFile.IndexOf("18_CREATIVE_");

if (LOCAL > 0 && isCreative > 0)

{

try

{

string contents = File.ReadAllText(file);

contents = contents.Replace("\",\"", "~~~");

contents = contents.Replace(",\"", "RRR~~~");

contents = contents.Replace("\",", "LLL~~~");

contents = contents.Replace("\"\r", "EEE~~~");

contents = contents.Replace("\r\"", "BBB~~~");

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

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

contents = contents.Replace("BBB~~~", "\r\"");

contents = contents.Replace("EEE~~~", "\"\r");

contents = contents.Replace("LLL~~~", "\",");

contents = contents.Replace("RRR~~~", ",\"");

contents = contents.Replace("~~~", "\",\"");

File.WriteAllText(file, contents.ToString());

OutputEmailBody = OutputEmailBody + file + "\r";

sendMail = 1;

}

// catch ONLY those exceptions you expect

catch (System.ArgumentException e)

{

// ALWAYS log an error, expected or otherwise.

Console.WriteLine("error", e);

}

}

}

//i created a stored prod that took in a string and the string in this case specified whick files were clensed and emails it to my team. However this is not needed

if (sendMail > 0)

{

//code to call proc that sends email

/* using (var conn = new SqlConnection("Server=XXXXXX;DataBase=XXXXXX;Integrated Security=SSPI"))

using (var command = new SqlCommand("Stored Procedure Name", conn)

{

CommandType = CommandType.StoredProcedure

})

{

OutputEmailBody = "The below Files were adjusted by the TextQualifierInDataClenser. You may want to look them over and ensure that nothing out of the norm was adjusted. Should just be fixing the single double quote in the data" + "\r" + "\r" + OutputEmailBody;

conn.Open();

command.Parameters.Add(new SqlParameter("@bodyOut", OutputEmailBody));

command.ExecuteNonQuery();

conn.Close();

}*/

}

//these were for testing. the write to console tells me what was processed and the messageBox was used to stop the process from completing w/o me having to click the message box.

// Console.WriteLine(OutputEmailBody);

//MessageBox.Show("ff");

}

}

}