2

Many questions have been asked on this topic, but I can't find anything specifically addressing what I see in Visual Studio 2017 (SSDT). A Custom Property named "EscapeQualifier" exists for a flat-file destination component in an SSIS project. Unfortunately, setting this to true doesn't seem to do anything.

Searching official documentation from MS doesn't even show the property existed.

On the surface, using this option seems to be a very elegant solution to the common issue of creating a "real" CSV file when the data being exported contains the double-quote character. If it worked as it seems it should, then it would double any double-quotes (or similarly escape whatever character you defined as your text-qualifier) for all quotable fields in the destination.

The solutions for "the CSV problem" that I've been able to find suggest modifying the specific data via transforms or at the data-retrieval level, but that's very impractical to do on each and every text-qualified data column.

To add insult to injury, I found a KB article from MS that suggests "exporting to CSV" is an official thing in SSDT.

KB4135137 - SSMS and SSDT do not escape double quotation marks when you export data as CSV

For example, you export a table into CSV format in a SQL Server Integration Services (SSIS) project.

This article suggests that the double-quotes not being escaped are a bug that has been fixed. Maybe it has, but only for the "Save results as..." option within SSMS. I still don't see any possible way to specify a true CSV export in an SSIS package, and this "EscapeQualifier" option gave me false hope.

Does this "EscapeQualifier" option ever do anything? If so, how do I get it to work? If not, is there another universal solution to the SSIS export to CSV issue?

Hadi
  • 36,233
  • 13
  • 65
  • 124
RichardB
  • 21
  • 3
  • 1
    So the problem is that you want to quote-qualify everything, but some data items contain quotes? Quote qualifying itself is only a workaround for commas within data elements, which is only a problem if you use comma as a delimiter – Nick.Mc Feb 22 '22 at 23:08
  • That is correct, I'd like a way to use SSIS to get as close as possible to exporting a "proper" CSV per [RFC 4180](https://datatracker.ietf.org/doc/html/rfc4180), which is as close there is to some kind of standard. It would also be nice in SSIS if they had an option to only include the qualifier when the content contained a delimiter. – RichardB Feb 23 '22 at 15:43
  • I created a [pull request](https://github.com/MicrosoftDocs/sql-docs/pull/7335) to add information about this property to the [Microsoft Docs](https://learn.microsoft.com/en-us/sql/integration-services/data-flow/flat-file-custom-properties?view=sql-server-ver15) – Hadi Feb 23 '22 at 18:58

1 Answers1

1

Note: I created a pull request to add information about this property to Microsoft Docs.

As mentioned in the Flat File Destination properties, the EscapeQualifier property is used to:

When text qualifier is enabled, specifies whether the text qualifier in the data written to the destination file will be escaped or not

enter image description here

To test this property, I created a package the transfer data from a flat file to another one.

In the source flat file connection manager, the Text Qualifier is set to <none>, while in the destination flat file connection manager the text qualifier is set to ". The source flat file only contains the following value: my name is "hadi".

Is set the EscapeQualifier property as True in the flat file destination and execute the package. As shown in the screenshot below, the destination file contains the following value: "My name is ""hadi""" which means that this property worked as excepted.

enter image description here

Make sure that you have set a text qualifier in the flat file connection manager to ensure that this property will work as excepted.

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124