为什么在导入UTF-8平面文件时，SSIS不能识别换行符{LF}作为行分隔符？

Question

我正在尝试使用SSIS将UTF-8编码的平面文件数据导入SQL Server 2008。在Notepad++中，行数据的结尾如下所示：

我还有几张图片展示文件连接管理器的外观：

您可以看到数据在文件连接管理器预览中正确显示。当我尝试导入此数据时，没有导入任何行。我收到错误消息，指示未找到行分隔符。您可以在文件连接管理器图像中看到标题行分隔符和行分隔符都设置为{LF}。这足以生成正确的预览，因此我迷失了为什么它无法工作以进行导入。我尝试了许多事情，但不起作用：

• 尝试在SSMS中使用向导导入...相同的结果
• 尝试使用数据转换，没有影响
• 尝试将行分隔符设置为 (0a)，相同的结果

[平面文件源 [582]] 警告: 在读取标题行时已达到数据文件的末尾。请确保标题行分隔符和要跳过的标题行数正确。

感谢您查看此问题，我非常感激您能提供的任何帮助。

- K Richard

Answer 1

原因：

SSIS无法读取文件，并显示以下警告，原因是列分隔符Ç（带有锐音的“c”）而不是行分隔符{LF}（换行符）。

[Read flat file [1]] Warning: The end of the data file was reached while 
reading header rows. Make sure the header row delimiter and the number of 
header rows to skip are correct.

这里有一个示例SSIS包，展示如何使用Script Component解决问题，并且最后还有另一个演示你的问题的示例。

解决方案：

下面的示例包是用SSIS 2008 R2编写的。它读取一个带有行分隔符{LF}作为单个列值的平面文件；然后使用Script Component拆分数据，将信息插入到SQL Server 2008 R2数据库中的表中。

使用Notepad ++创建一个简单的平面文件，其中有几行。以下示例文件每行都有产品ID和列表价格信息，以Ç作为列分隔符，并以{LF}作为行结束符。

在Notepad++中，单击编码，然后单击以UTF-8编码保存，将平面文件保存为UTF-8编码。

示例将使用名为Sora的SQL Server 2008 R2数据库。使用下面提供的脚本创建一个名为dbo.ProductListPrice的新表。SSIS将把平面文件数据插入到该表中。

USE Sora;
GO

CREATE TABLE dbo.ProductListPrice
(
        ProductId   nvarchar(30)    NOT NULL
    ,   ListPrice   numeric(12,2)   NOT NULL
);
GO

使用 Business Intelligence Development Studio (BIDS) 2008 R2 创建一个 SSIS 包。将包命名为 SO_6268205.dtsx。创建一个数据源，命名为 Sora.ds，以连接到 SQL Server 2008 R2 中的数据库 Sora。

在包内任意位置右键单击，然后单击 Variables 以查看变量面板。在包范围 SO_6268205 中创建一个名为 ColumnDelimiter 的新变量，其数据类型为 String，并将变量设置为值Ç。

右键单击连接管理器，然后单击新建扁平文件连接...以创建一个连接来读取扁平文件。

在Flat File Connection Manager Editor的General页面上，执行以下操作：

• 将Connection manager name设置为ProductListPrice
• 将Description设置为Flat file connection manager to read product list price information.
• 选择平面文件路径。我有文件在路径C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt
• 从Header Row Delimiter中选择{LF}
• 勾选Column names in the first data row
• 点击Columns页面

在平面文件连接管理器编辑器的列页面上，确认列分隔符为空白并已禁用。点击高级页面。

在 Flat File 连接管理器编辑器 的 高级 页上，执行以下操作。

• 将 Name 设为 LineData
• 确认 Column delimiter 已设置为 {LF}
• 将 DataType 设为 Unicode string [DT_WSTR]
• 将 OutputColumnWidth 设为 255
• 单击 Preview 页。

在Flat File Connection Manager Editor的预览页面上，验证显示的数据是否正确，并单击确定。

您将在包的底部的连接管理器选项卡上看到数据源Sora和平面文件连接管理器ProductListPrice。

将Data Flow Task拖放到包的Control Flow选项卡上，并将其命名为File to database - Without Cedilla delimiter

双击“数据流任务”将视图切换到包上的“数据流”选项卡。在“数据流”选项卡上拖放“平面文件源”。双击“平面文件源”以打开“平面文件源编辑器”。

在“平面文件源编辑器”的“连接管理器”页面上，选择“平面文件连接管理器”“ProductListPrice”，然后单击“列”页面。

在平面文件源编辑器的列页面上，选中LineData列并点击OK。

将一个脚本组件拖放到Data Flow选项卡下的Flat File Source之后，选择转换并单击OK。将绿色箭头从Flat File Source连接到Script Component。双击Script Component以打开Script Transformation Editor。

在Script Transformation Editor上单击Input Columns，然后选择LineData列。单击Inputs and Outputs页面。

在脚本转换编辑器的输入和输出页面上，执行以下操作。

• 将输入名称更改为FlatFileInput。
• 将输出名称更改为SplitDataOutput。
• 选择输出列并单击添加列。再次重复此步骤以添加另一列。
• 将第一列命名为ProductId。
• 将列ProductId的数据类型设置为Unicode字符串[DT_WSTR]。
• 将长度设置为30。

在脚本转换编辑器的输入和输出页面执行以下操作：

• 将第二列命名为ListPrice
• 将ListPrice列的DataType设置为numeric [DT_NUMERIC]
• 将Precision设置为12
• 将Scale设置为2
• 单击Script页面以修改脚本

在脚本转换编辑器的脚本页面上，执行以下操作。

• 单击ReadOnlyVariables旁边的省略号按钮，然后选择变量User::ColumnDelimiter
• 单击编辑脚本...

将以下C#代码粘贴到脚本编辑器中。该脚本执行以下任务。

• 使用在变量User::ColumnDelimiter中定义的列分隔符值Ç，方法FlatFileInput_ProcessInputRow将传入的值拆分并分配给在脚本组件转换中定义的两个输出列。

C#中的脚本组件代码

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void FlatFileInput_ProcessInputRow(FlatFileInputBuffer Row)
    {
        const int COL_PRODUCT = 0;
        const int COL_PRICE = 1;

        char delimiter = Convert.ToChar(this.Variables.ColumnDelimiter);
        string[] lineData = Row.LineData.ToString().Split(delimiter);

        Row.ProductId = String.IsNullOrEmpty(lineData[COL_PRODUCT]) 
                            ? String.Empty 
                            : lineData[COL_PRODUCT];

        Row.ListPrice = String.IsNullOrEmpty(lineData[COL_PRICE]) 
                            ? 0 
                            : Convert.ToDecimal(lineData[COL_PRICE]);
    }
}

将OLE DB Destination拖放到Data Flow选项卡上。将Script Component的绿色箭头连接到OLE DB Destination。双击OLE DB Destination打开OLE DB Destination Editor。

在OLE DB Destination Editor的Connection Manager页面上，执行以下操作。

• 从OLE DB Connection Manager中选择Sora
• 从Data access mode中选择Table or view - fast load
• 从Name of the table or the view中选择[dbo].[ProductListPrice]
• 点击Mappings页面

点击OLE DB Destination Editor上的Mappings页面，如果输入和输出列名相同，则会自动映射列。 点击OK。

数据流选项卡在配置所有组件后应该看起来像这样。

在SQL Server Management Studio (SSMS)中执行查询select * from dbo.ProductListPrice，以查找表中的行数。在执行包之前，它应该是空的。

执行该包。您会注意到该包成功处理了9行。该扁平文件包含10行，但第一行是带有列名称的标题。

在SQL Server管理工具（SSMS）中执行查询select * from dbo.ProductListPrice，以查找成功插入表中的9行。数据应与平面文件数据匹配。

上面的例子说明了如何使用Script Component手动拆分数据，因为Flat File Connection Manager在配置列分隔符Ç时会遇到错误。

问题模拟：

此示例显示了一个单独的Flat File Connection Manager，配置了列分隔符Ç，它执行但遇到警告并且不处理任何行。

右键单击连接管理器，然后单击新建平面文件连接...以创建连接以读取平面文件。 在Flat File Connection Manager Editor的常规页面上，执行以下操作：

• 将连接管理器名称设置为ProductListPrice_Cedilla
• 将描述设置为带有Cedilla列分隔符的平面文件连接管理器。
• 我有一个位于路径C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt的文件。选择平面文件路径。
• 从标题行分隔符中选择{LF}
• 勾选第一行数据中包含列名
• 单击列页面

在平面文件连接管理器编辑器的列页面上，执行以下操作：

• 将行分隔符设置为{LF}
• 列分隔符字段可能已被禁用。单击重置列
• 将列分隔符设置为Ç
• 单击高级选项卡

在平面文件连接管理器编辑器的高级页面上，执行以下操作：

• 将名称设置为ProductId
• 将列分隔符设置为Ç
• 将数据类型设置为Unicode字符串[DT_WSTR]
• 将长度设置为30
• 单击ListPrice列

在Flat File Connection Manager Editor的高级页面上，执行以下操作：

• 将Name设置为ListPrice
• 将ColumnDelimiter设置为{LF}
• 将DataType设置为numeric [DT_NUMERIC]
• 将DataPrecision设置为12
• 将DataScale设置为2
• 点击OK

在“控制流”选项卡上拖放一个“数据流任务”，并将其命名为“文件到数据库 - 使用 Cedilla 分隔符”。禁用第一个数据流任务。

使用Flat File Source和OLE DB Destination配置第二个数据流任务

双击平面文件源以打开平面文件源编辑器。 在平面文件源编辑器的连接管理器页面上，选择平面文件连接管理器ProductListPrice_Cedilla，然后单击列页面以配置列。 单击确定。

执行该包。所有组件将显示绿色以指示过程成功，但不会处理任何行。您可以看到在Flat File Source和OLE DB Destination之间没有行号指示。

点击 进度 选项卡，您将会看到以下警告信息。

[Read flat file [1]] Warning: The end of the data file was reached while 
reading header rows. Make sure the header row delimiter and the number of 
header rows to skip are correct.

- user756519

---

这是一个不可思议的答案，我无法用言语表达我的感激之情。这应该成为stackoverflow的一个亮点示例。 - K Richard

谢谢，再好不过了。 - Daniel Sh.

Answer 2

上面的答案看起来非常复杂，只需转换文件中的行结束符即可。

Dim FileContents As String = My.Computer.FileSystem.ReadAllText("c:\Temp\UnixFile.csv")

Dim NewFileContents As String = FileContents.Replace(vbLf, vbCrLf)

My.Computer.FileSystem.WriteAllText("c:\temp\WindowsFile.csv", NewFileContents, False, New System.Text.UnicodeEncoding)

重新整理自这里。

- Richard

---

不要把长篇文章混淆为复杂性。它可能看起来很复杂，但非常精细地编写并且清晰地解释了该方法。相比之下，你的答案质量远低于此，并且甚至不清楚首先如何回答这个问题。请记住，这是5年前的问题。 - Takarii

Answer 3

如果您正在尝试通过Windows上的SSIS消耗在不同平台（如Unix、Mac等）上生成的FlatFile，则可能会出现此问题。

在这种情况下，您需要做的就是使用unix2dos命令将文件格式从UNIX转换为DOS。

unix2dos file-to-convert

- Jeevan