我正在尝试使用 SSIS 将数据从 utf-8 编码的平面文件导入到 SQL Server 2008 中。这是行数据末尾在 Notepad++ 中的样子:
我还有几张图像显示了内容文件连接管理器如下所示:
您可以看到数据在文件连接管理器预览中正确显示。当我尝试导入此数据时,没有导入任何行。我收到一条错误消息,指示未找到行分隔符。您可以在文件连接管理器图像中看到标题行分隔符和行分隔符都设置为 {LF}
。这足以生成正确的预览,所以我不明白为什么它无法导入。我尝试了很多带来零结果的方法:
- 尝试使用 SSMS 中的向导导入...相同的结果
- 尝试使用数据转换,没有影响
- 尝试将行分隔符设置为 (0a),相同的结果
[平面文件源 [582]] 警告:
已到达数据文件末尾
读取标题行。确保
标题行分隔符和行数
要跳过的标题行是正确的。
感谢您查看此内容,我非常感谢您提供的任何帮助。
I am trying to import data from a utf-8 encoded flat file into SQL Server 2008 using SSIS. This is what the end of the row data looks like in Notepad++:
I have a couple more images showing what the file connection manager looks like:
You can see that the data shows correctly in the file connection manager preview. When I try to import this data, no rows import. I get an error message indicating that the row delimiter was not found. You can see in the file connection manager images that the header row delimiter and the row delimiter are both set to {LF}
. This was sufficient to generate the correct preview, so I am lost to why it did not work to import. I have tried a number of things that have brought zero results:
- Tried using the Wizard import in SSMS...same results
- Tried using data conversion, no impact
- Tried setting the row delimiter to (0a), same results
[Flat File Source [582]] 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.
Thanks for looking at this and I really appreciate any help you can offer.
发布评论
评论(3)
原因:
由于列分隔符
Ç
("c" with cedilla)和 ,SSIS 无法读取文件并显示以下警告>不是
,因为行分隔符{LF}
(换行)。下面是一个示例 SSIS 包,展示了如何使用
脚本组件
解决问题,最后还有另一个示例来模拟您的问题。解决方案:
以下示例包是用
SSIS 2008 R2
编写的。它读取带有行分隔符{LF}
的平面文件作为单个列值;然后使用脚本组件
分割数据,将信息插入到SQL Server 2008 R2
数据库的表中。使用 Notepad++ 创建一个包含几行的简单平面文件。下面的示例文件每行都有产品 ID 和标价信息,以
Ç
作为列分隔符分隔,并且每个行以{LF}
分隔符结尾。在 Notepad++ 上,单击
编码
,然后单击以 UTF-8 编码
,以UTF-8
编码保存平面文件。该示例将使用名为 < 的
SQL Server 2008 R2
数据库代码>索拉。使用下面给定的脚本创建一个名为 dbo.ProductListPrice 的新表。 SSIS 会将平面文件数据插入到该表中。使用 Business Intelligence Development Studio (BIDS) 2008 R2 创建 SSIS 包。将包命名为
SO_6268205.dtsx
。创建名为Sora.ds
的数据源,以连接到 SQL Server 2008 R2 中的数据库Sora
。右键单击包内的任意位置,然后单击
变量
以查看变量窗格。在包范围SO_6268205< 中创建一个名为
ColumnDelimiter
的新变量,其数据类型为String
/code>并将变量设置为值
Ç
右键单击
连接管理器
并单击新建平面文件连接...
创建连接以读取平面文件。常规
页面平面文件连接管理器编辑器,执行以下操作:ProductListPrice
平面文件连接管理器读取产品标价信息。
C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt
{LF}
< /strong> 来自标题行分隔符第一个数据行中的列名称
列
页在
Columns
页面上em>平面文件连接管理器编辑器,验证列分隔符
为空且已禁用。单击高级
页面。在
高级
页面上在平面文件连接管理器编辑器中,执行以下操作。LineData
{LF}
Unicode 字符串 [DT_WSTR]
255
预览
页面。在
预览
页面上在平面文件连接管理器编辑器中,验证显示的数据是否正确,然后单击确定
。您将看到数据源 Sora 和平面文件连接包底部的
连接管理器
选项卡上的ProductListPrice管理器。将
数据流任务
拖放到控制流上包的选项卡并将其命名为文件到数据库 - 没有 Cedilla 分隔符
双击数据流任务将视图切换到包上的
数据流
选项卡。将平面文件源
拖放到数据流选项卡上。双击平面文件源以打开平面文件源编辑器
。在平面文件源编辑器的
连接管理器
页面上,选择平面文件连接管理器ProductListPrice
并单击列页面。在
列
页面上在平面文件源编辑器中,选中LineData
列,然后单击确定
。拖放
脚本组件
在平面文件源下方的数据流选项卡上,选择转换
,然后单击确定
。将绿色箭头从平面文件源连接到脚本组件。双击脚本组件打开脚本转换编辑器
。单击“脚本转换编辑器”上的“输入列”,然后选择“
LineData
”列。单击“输入和输出”页面。在
输入和输出
页面上在脚本转换编辑器中,执行以下操作。SplitDataOutput
添加列
。再次重复此操作以添加另一列。ProductId
Unicode 字符串 [DT_WSTR ]
30
在脚本转换编辑器的
输入和输出
页面上,执行以下操作。ListPrice
数字 [DT_NUMERIC]
12
2
在 >脚本转换编辑器的
脚本
页面,执行以下操作。User::ColumnDelimiter
编辑脚本...
将以下 C# 粘贴到脚本编辑器中。该脚本执行以下任务。
Ç
,方法FlatFileInput_ProcessInputRow
< /em> 拆分传入值并将其分配给脚本组件转换中定义的两个输出列。C# 脚本组件代码
拖放
OLE DB 目标
到数据流选项卡。将绿色箭头从脚本组件连接到OLE DB 目标。双击OLE DB 目标 打开OLE DB 目标编辑器
。在OLE DB 目标编辑器 的
连接管理器
页面上,执行以下操作。Sora
表或视图 - 快速加载
中选择数据访问模式[dbo].[ProductListPrice]
单击 OLE DB 目标编辑器上的
Mappings
页面如果输入和输出列名称相同,em> 将自动映射列。单击确定
。配置所有组件后,数据流选项卡应如下所示。
在 SQL Server 中执行查询
select * from dbo.ProductListPrice
Management Studio (SSMS) 查找表中的行数。在执行包之前它应该是空的。执行包。您将注意到该包已成功处理 9 行。该平面文件包含 10 行,但第一行是带有列名称的标题。
在 SQL 中执行查询
select * from dbo.ProductListPrice
Server Management Studio (SSMS) 查找已成功插入表中的 9 行。数据应与平面文件数据匹配。上面的示例说明了如何使用脚本组件手动拆分数据因为平面文件连接管理器在配置列分隔符
Ç
时遇到错误问题模拟:
此示例显示了一个单独的平面文件连接管理器 配置了列分隔符
Ç
,执行但遇到警告并且不处理任何行。右键单击
连接管理器
,然后单击新建平面文件连接...
以创建要读取的连接平面文件。在平面文件连接管理器编辑器的常规
页面上,执行以下操作:ProductListPrice_Cedilla
带有 Cedilla 列分隔符的平面文件连接管理器。
C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt
选择平面文件路径。{LF}
第一个数据行中的列名称
列
页面关于平面文件连接管理器编辑器的
列
页面,执行以下操作:{LF}
重置列
Ç
高级
页面img src="https://i.sstatic.net/Dxxjz.png" alt="平面文件连接管理器编辑器 - 带 Cedilla - Columns">
< 平面文件连接管理器编辑器的
高级
页面,执行以下操作:ProductId
Ç
Unicode 字符串 [DT_WSTR]
30
ListPrice
在平面文件连接管理器编辑器<的
高级
页面上< /em>,执行以下操作:ListPrice
{LF}
数字[DT_NUMERIC]
12< /code>
2
确定
将
数据流任务
拖放到控制流 选项卡并将其命名为文件到数据库 -使用 Cedilla 分隔符
。禁用第一个数据流任务。使用
平面文件源
配置第二个数据流任务 和OLE DB 目标
双- 单击平面文件源以打开
平面文件源编辑器
。在平面文件源编辑器的连接管理器
页面上,选择平面文件连接管理器ProductListPrice_Cedilla
并单击列页面以配置列。单击确定
。执行包。所有组件将显示绿色,表示该过程成功,但不会处理任何行。您可以看到
平面文件源
和OLE DB 目标
之间没有行号指示单击
进度
选项卡,您将注意到以下警告信息。Cause:
SSIS fails to read the file and displays the below warning due to the column delimiter
Ç
("c" with cedilla) andnot
due to the line delimiter{LF}
(Line Feed).Here is a sample SSIS package that shows how to resolve the issue using
Script Component
and at the end there is another example that simulates your issue.Resolution:
Below sample package is written in
SSIS 2008 R2
. It reads a flat file with row delimiter{LF}
as a single column value; then splits the data usingScript Component
to insert the information into a table inSQL Server 2008 R2
database.Use Notepad++ to create a simple flat file with few rows. The below sample file has Product Id and List Price information on each row separated by
Ç
as column delimiter and each row ends with{LF}
delimiter.On the Notepad++, click
Encoding
and then clickEncoding in UTF-8
to save the flat file inUTF-8
encoding.The sample will use an
SQL Server 2008 R2
database namedSora
. Create a new table nameddbo.ProductListPrice
using the below given script. SSIS will insert the flat file data into this table.Create an SSIS package using Business Intelligence Development Studio (BIDS) 2008 R2. Name the package as
SO_6268205.dtsx
. Create a data source namedSora.ds
to connect to the databaseSora
in SQL Server 2008 R2.Right-click anywhere inside the package and then click
Variables
to view the variables pane. Create a new variable namedColumnDelimiter
of data typeString
in the package scopeSO_6268205
and set the variable with the valueÇ
Right-click on the
Connection Managers
and clickNew Flat File Connection...
to create a connection to read the flat file.On the
General
page of the Flat File Connection Manager Editor, perform the following actions:ProductListPrice
Flat file connection manager to read product list price information.
C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt
{LF}
from Header Row DelimiterColumn names in the first data row
Columns
pageOn the
Columns
page of the Flat File Connection Manager Editor, verify that theColumn delimiter
is blank and disabled. ClickAdvanced
page.On the
Advanced
page of the Flat File Connection Manager Editor, perform the following actions.LineData
{LF}
Unicode string [DT_WSTR]
255
Preview
page.On the
Preview
page of the Flat File Connection Manager Editor, verify that the displayed data looks correct and clickOK
.You will see the data source Sora and the flat file connection manager ProductListPrice on the
Connection Managers
tab at the bottom of the package.Drag and drop
Data Flow Task
onto the Control Flow tab of the package and name it asFile to database - Without Cedilla delimiter
Double-click the Data Flow Task to switch the view to the
Data Flow
tab on the package. Drag and drop aFlat File Source
on the Data Flow tab. Double-click the Flat File Source to openFlat File Source Editor
.On the
Connection Manager
page of the Flat File Source Editor, select the Flat File Connection ManagerProductListPrice
and click Columns page.On the
Columns
page of the Flat File Source Editor, check the columnLineData
and clickOK
.Drag and drop a
Script Component
onto the Data Flow tab below the Flat File Source, selectTransformation
and clickOK
. Connect the green arrow from Flat File Source to Script Component. Double-click Script Component to openScript Transformation Editor
.Click Input Columns on Script Transformation Editor and select
LineData
column. Click Inputs and Outputs page.On the
Inputs and Outputs
page of the Script Transformation Editor, perform the following actions.SplitDataOutput
Add Column
. Repeat this again to add another column.ProductId
Unicode string [DT_WSTR]
30
On the
Inputs and Outputs
page of the Script Transformation Editor, perform the following actions.ListPrice
numeric [DT_NUMERIC]
12
2
On the
Script
page of the Script Transformation Editor, perform the following actions.User::ColumnDelimiter
Edit Script...
Paste the below C# in the Script Editor. The script performs the following tasks.
Ç
defined in the variable User::ColumnDelimiter, the methodFlatFileInput_ProcessInputRow
splits the incoming value and assigns it to the two output columns defined in the Script Component transformation.Script component code in C#
Drag and drop
OLE DB Destination
onto the Data Flow tab. Connect the green arrow from Script Component to OLE DB Destination. Double-click OLE DB Destination to openOLE DB Destination Editor
.On the
Connection Manager
page of the OLE DB Destination Editor, perform the following actions.Sora
from OLE DB Connection ManagerTable or view - fast load
from Data access mode[dbo].[ProductListPrice]
from Name of the table or the viewClick
Mappings
page on the OLE DB Destination Editor would automatically map the columns if the input and output column names are same. ClickOK
.Data Flow tab should look something like this after configuring all the components.
Execute the query
select * from dbo.ProductListPrice
in the SQL Server Management Studio (SSMS) to find the number of rows in the table. It should be empty before executing the package.Execute the package. You will notice that the package successfully processed 9 rows. The flat file contains 10 lines but the first row is header with column names.
Execute the query
select * from dbo.ProductListPrice
in the SQL Server Management Studio (SSMS) to find the 9 rows successfully inserted into the table. The data should match with flat file data.The above example illustrated how to manually split the data using Script Component because the Flat File Connection Manager encounters error when configured the column delimiter
Ç
Issue Simulation:
This example shows a separate Flat File Connection Manager configured with column delimiter
Ç
, which executes but encounters a warning and does not process any lines.Right-click on the
Connection Managers
and clickNew Flat File Connection...
to create a connection to read the flat file. On theGeneral
page of the Flat File Connection Manager Editor, perform the following actions:ProductListPrice_Cedilla
Flat file connection manager with Cedilla column delimiter.
C:\Siva\StackOverflow\Files\6268205\ProductListPrice.txt
Select the flat file path.{LF}
from Header Row DelimiterColumn names in the first data row
Columns
pageOn the
Columns
page of the Flat File Connection Manager Editor, perform the following actions:{LF}
Reset Columns
Ç
Advanced
pageOn the
Advanced
page of the Flat File Connection Manager Editor, perform the following actions:ProductId
Ç
Unicode string [DT_WSTR]
30
ListPrice
On the
Advanced
page of the Flat File Connection Manager Editor, perform the following actions:ListPrice
{LF}
numeric [DT_NUMERIC]
12
2
OK
Drag and drop a
Data Flow task
onto the Control Flow tab and name it asFile to database - With Cedilla delimiter
. Disable the first data flow task.Configure the second data flow task with
Flat File Source
andOLE DB Destination
Double-click the Flat File Source to open
Flat File Source Editor
. On theConnection Manager
page of the Flat File Source Editor, select the Flat File Connection ManagerProductListPrice_Cedilla
and click Columns page to configure the columns. ClickOK
.Execute the package. All the components will display green color to indicate that the process was success but no rows will be processed. You can see that there are no rows numbers indication between the
Flat File Source
andOLE DB Destination
Click the
Progress
tab and you will notice the following warning message.上面的答案似乎非常复杂,只需转换文件 Rehashed 中的行结尾
此处
Answer above seems awfully complicated, just convert the line endings in the file
Rehashed from here
如果您尝试在 Windows 上通过 SSIS 使用在不同平台(如 Unix、Mac 等)上生成的 FlatFile,也会出现此问题
在这种情况下,您需要做的就是使用 unix2dos 命令将文件格式从 UNIX 转换为 DOS
This issue also arises if you are trying to consume FlatFile generated on a different platform like Unix, Mac etc via SSIS on windows
In such a scenario all you need to do is convert the file format from say UNIX to DOS with unix2dos command