我正在使用 ssis 将数据导出到 csv 文件中。在我的 ssis 包中，我将文件压缩为 zip 格式，并使用 sftp 将其上传到 linux 服务器上。问题是在目标文件系统中，csv 文件包含一个来自 dos 系统的 ^M 字符。

我找到了三个解决方案。

• 首先我可以将 sftp 传输模式设置为 ascii 而不是压缩文件(我后来发现只有 ftp 支持)。考虑到我的解压缩文件大于 3Gb，效率不高，上传需要很长时间。

• 其次，传输后我可以解压缩文件并使用 dos2unix 实用程序对其进行转换，但同样没有安装 dos2unix，我无权将其安装到目标系统。

• 最后，我可以使用像 sed 这样的 unix 编辑器从行尾删除 ^M。我的文件由超过 400 万行组成，这又需要很长时间。

问:有什么方法可以使用 ssis 将我的文件预先格式化为 ASCII，然后压缩并传输？

最佳答案

在搜索这个问题时，我发现了一个非常有用的链接，它们描述了这个问题的原因和可能的解决方案:

• How to remove CTRL-M (^M) characters from a file in Linux
• Why are special characters such as “carriage return” represented as “^M”?

原因

File has been transferred between systems of different types with different newline conventions. For example, Windows-based text editors will have a special carriage return character (CR+LF) at the end of lines to denote a line return or newline, which will be displayed incorrectly in Linux (^M). This can be difficult to spot, as some applications or programs may handle the foreign newline characters properly while others do not. Thus some services may crash or not respond correctly. Often times, this is because the file is created or perhaps even edited on a Microsoft Windows machine and then uploaded or transferred to a Linux server. This typically occurs when a file is transferred from MS-DOS (or MS-Windows) without ASCII or text mode.

可能的解决方案

(1) 使用dos2unix命令

dos2unix 包括将带有 DOS 或 MAC 换行符的文本文件转换为 Unix 换行符的实用程序，反之亦然。它还包括将 UTF-16 转换为 UTF-8。

• dos2unix
• Dos2Unix / Unix2Dos - Text file format converters
• dos2unix and unix2dos commands

您可以通过 Execute Process Task 使用类似的命令:

dos2unix filename

(2)数据流任务

您可以创建一个数据流任务，将数据从平面文件源传输到新的平面文件目标，这两个平面文件连接 manager 具有相同的结构，除了行分隔符属性 ({CR}{LF 在源中，{LF} 在目标中)

• Flat File Connection Manager Editor (Columns Page)

(3) 使用脚本任务-StreamReader/Writer

您可以使用类似代码的脚本任务:

string data = null;
//Open and read the file
using (StreamReader srFileName = new StreamReader(FileName))
    {
        data = srFileName.ReadToEnd();
        data = data.Replace("\r\n","\n");
    }

using (StreamWriter swFileName = new StreamWriter(FileName))
    {
        swFileName.Write(data);
    }

• Replacing LF with CRLF in text file

(4) 使用unzip -a解压

来自以下unzip documentation :

-a

convert text files. Ordinarily all files are extracted exactly as they are stored (as ''binary'' files). The -a option causes files identified by zip as text files (those with the 't' label in zipinfo listings, rather than 'b') to be automatically extracted as such, converting line endings, end-of-file characters and the character set itself as necessary. (For example, Unix files use line feeds (LFs) for end-of-line (EOL) and have no end-of-file (EOF) marker; Macintoshes use carriage returns (CRs) for EOLs; and most PC operating systems use CR+LF for EOLs and control-Z for EOF. In addition, IBM mainframes and the Michigan Terminal System use EBCDIC rather than the more common ASCII character set, and NT supports Unicode.) Note that zip's identification of text files is by no means perfect; some ''text'' files may actually be binary and vice versa. unzip therefore prints ''[text]'' or ''[binary]'' as a visual check for each file it extracts when using the -a option. The -aa option forces all files to be extracted as text, regardless of the supposed file type. On VMS, see also -S.

因此您可以使用以下命令来提取具有更改行尾的文本文件:

unzip -a filename

归功于 @jww评论

其他有用的链接

• How to Remove ^M in Linux & Unix
• Remove CTRL-M characters from a file in UNIX
• Convert DOS line endings to Linux line endings in vim
• How to replace crlf with lf in a single file
• How to remove Windows carriage returns for text files in Linux

关于linux - 从 Windows 到 Linux 的文件传输，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54516959/