SSIS 导出到平面文件目标 (CSV) - 自定义属性 EscapeQualifier 不起作用（未记录？）
关于这个主题有人提出了很多问题，但我找不到任何专门解决我在 Visual Studio 2017 (SSDT) 中看到的内容的内容。 SSIS 项目中的平面文件目标组件存在名为“EscapeQualifier”的自定义属性。不幸的是，将其设置为 true 似乎没有任何作用。
从 MS 搜索官方文档甚至没有显示该属性存在。
从表面上看，使用此选项似乎是一个非常优雅的解决方案，可以解决当导出的数据包含双引号字符时创建“真实”CSV 文件的常见问题。如果它按预期工作，那么它将为目标中的所有可引用字段加倍任何双引号（或类似地转义您定义为文本限定符的任何字符）。
我找到的“CSV 问题”的解决方案建议通过转换或在数据检索级别修改特定数据，但对每个文本限定数据列执行此操作非常不切实际。
雪上加霜的是，我发现 MS 的一篇知识库文章表明“导出到 CSV”是 SSDT 中的官方内容。
KB4135137 - 将数据导出为 CSV 时，SSMS 和 SSDT 不会转义双引号
例如，您在 SQL Server Integration Services (SSIS) 项目中将表导出为 CSV 格式。
本文表明双引号未转义是一个已修复的错误。也许有，但仅限于 SSMS 中的“将结果另存为...”选项。我仍然没有看到任何可能的方法来在 SSIS 包中指定真正的 CSV 导出，并且这个“EscapeQualifier”选项给了我错误的希望。
这个“EscapeQualifier”选项有什么作用吗？如果是这样，我该如何让它发挥作用？如果没有，是否有另一种通用解决方案来解决 SSIS 导出到 CSV 问题？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
注意：我创建了一个拉取请求来添加有关此属性的信息到 Microsoft Docs。
正如平面文件目标属性中提到的，
EscapeQualifier属性用于：为了测试此属性，我创建了一个传输包来自平面文件的数据到另一个。
在源平面文件连接管理器中，
文本限定符设置为"。源平面文件仅包含以下值：我的名字是“hadi”。将
EscapeQualifier属性设置为True> 在平面文件目标中并执行如下面的屏幕截图所示，目标文件包含以下值："My name is ""hadi"""这意味着此属性已排除。 //i.sstatic.net/gHf55.png" rel="nofollow noreferrer">
确保您已设置 以确保此属性将按例外方式工作。
平面文件连接管理器中的文本限定符， https://i.sstatic.net/7ST6G.png" alt="在此处输入图像描述">
Note: I created a pull request to add information about this property to Microsoft Docs.
As mentioned in the Flat File Destination properties, the
EscapeQualifierproperty is used to: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 Qualifieris 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
EscapeQualifierproperty asTruein 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.Make sure that you have set a text qualifier in the flat file connection manager to ensure that this property will work as excepted.