1

I have a SSIS Package that is very basic. Exports data from a SQL Server 2008 table into a txt file (Comma Delimited). The end customer wants the fields with a Text Qualifier (") but when the value is Null does not want the text qualifier. Unfortunately sometimes the fields have a value and other times they are null. Is there a way to only include text qualifier when there is a true value in the field?

Current export looks something like this:

 "Reference", "Key", "Name", "Custom1"
 "","123456","John Doe", "9515551212"
 "","654321","Jane Doe",""

Preferred Output:

 "Reference", "Key", "Name", "Custom1"
 ,"123456","John Doe", "9515551212"
 ,"654321","Jane Doe",

On the Reference field, it is always null. So I attempted to make Text Qualifier "False" on this field, but unbelievably they want even the Column Headers to be Text Qualified.

David Redden
  • 147
  • 1
  • 15
  • this is not possible due to the fact that csv files to not adhere to the same theory of "NULL" as most databases. Null and an empty string are considered the same thing in a csv file and the qualifier is denoting an empty text string as it should. In any case if you were to have a " qualified file where some fields were not qualified it would be an absolute pain to import. If I were you I would consult with your customer and insist that they rethink their decision – SFrejofsky Apr 17 '15 at 14:51
  • Well, it's not possible in one step. But after you create the file, you could have a script task edit the file and remove all the occurrances of "". – Tab Alleman Apr 17 '15 at 15:19

1 Answers1

0

I figured out an ugly workaround. I created a transact query to perform a case when [field] is null then null else '"'+[field]+'"' end as """[field]""" did that for every field and it worked for what I (or rather my customer) was looking for.

David Redden
  • 147
  • 1
  • 15