Randy Doub (2/20/2013)

---

I read elsewhere that since a flat file is a text file, if you use a Text Qualifier it will be applied to every column.

So here's what works... remove the Text Qualifier and as a derived column use:

mycol !="" ? "\"" + mycol + "\"" : mycol

This adds two to the length of every column with data. I have some datetime data types in the table that got converted to char(10) for formatting in the select query and I was failing with a truncation error on them. I could not edit the length in the Derived Column Transformation Editor.

So I tried mycol !="" ? DT_STR(<len+2>,1252)("\"" + mycol + "\"") : mycol. It didn't fail, but gave me "10/09/201 in the date columns in the output file. Not sure what I was doing wrong there.

So I changed the select query to pad all the columns giving ample space for the max data plus the quotes. Ex: convert(varchar(50),convert(varchar(10),datetimeColumn,101)) as mycol.

Learned a little more about SSIS Expression Language today!

Thanks for all your time and effort.