如何使用 SSIS 将固定宽度的平面文件导入数据库? [英] How to import a fixed width flat file into database using SSIS?

问题描述

有人有关于如何使用 SSIS 包将固定宽度的平面文件导入数据库的教程吗?

Does any one have a tutorial on how to import a fixed width flat file into a database using an SSIS package?

我有一个包含不同长度列的平面文件.

I have a flat file containing columns with varying lengths.

Column name    Width
-----------    -----
First name        25
Last name         25
Id                 9
Date               8

如何将平面文件转换为列?

How do I convert a flat file into columns?

推荐答案

这是一个使用 SSIS 2008 R2 创建的示例包，解释了如何将平面文件导入到数据库表中.

Here is a sample package created using SSIS 2008 R2 that explains how to import a flat file into a database table.

• 创建一个名为 Fixed_Width_File.txt 的固定宽度平面文件，其中的数据如屏幕截图所示.截图使用 Notepad++ 来显示文件内容.它具有显示特殊字符的能力，如回车和换行.CR LF 表示行分隔符回车和换行.

• Create a fixed-width flat file named Fixed_Width_File.txt with data as shown in the screenshot. The screenshot uses Notepad++ to display the file contents. It has the capability to show the special characters like carriage return and line feed. CR LF denotes the row delimiters Carriage return and Line feed.

• 在 SQL 服务器数据库中，使用 SQL 脚本 部分下提供的创建脚本创建一个名为 dbo.FlatFile 的表.

• In the SQL server database, create a table named dbo.FlatFile using the create script provided under SQL Scripts section.

创建一个新的 SSIS 包并添加一个新的 OLE DB 连接管理器来连接到 SQL Server 数据库.让我们假设 OLE DB 连接管理器被命名为 SQLServer.

Create a new SSIS package and add a new OLE DB Connection manager that would connect to the SQL Server database. Let's assume that the OLE DB Connection manager is named as SQLServer.

• 在包的控制流选项卡上，放置一个数据流任务.

• 双击数据流任务，您将进入数据流选项卡.在数据流选项卡上，放置一个平面文件源.双击平面文件源，平面文件源编辑器就会出现.单击新建按钮打开平面文件连接管理器编辑器.

• Double-click on the data flow task and you will be taken to the data flow tab. On the data flow tab, place a Flat File Source. Double-click on the flat file source and the Flat File Source Editor will appear. Click the New button to open the Flat File Connection Manager Editor.

在 Flat File Source Editor 的 General 部分，在 Connection manager name 中输入一个值(比如 Source)，然后浏览到平面文件位置并选择文件.本示例使用路径中的示例文件 C:\temp\Fixed_Width_File.txt 如果您的文件中有标题行，则可以在标题行跳过文本框中输入值 1 以跳过标题行.

On the General section of the Flat File Source Editor, enter a value in Connection manager name (say Source) and browse to the flat file location and select the file. This example uses the sample file in the path C:\temp\Fixed_Width_File.txt If you have header rows in your file, you can enter a value 1 in the Header rows to skip textbox to skip the header row.

• 点击列部分.根据您的选择更改字体 我选择了 Courier New，这样我就可以通过更少的滚动查看更多数据.在行宽文本框中输入值 69.此值是所有列的宽度之和 + 2 的行分隔符.设置正确的行宽后，您应该会在源数据列部分正确地看到固定宽度的文件数据.现在，您必须单击适当的位置来确定列限制.请注意以下屏幕截图中的第 4、5、6 部分.

• Click on the Columns section. Change the font according to your choice I chose Courier New so I could see more data with less scrolling. Enter the value 69 in the Row width text box. This value is the sum of width of all your columns + 2 for the row delimiter. Once you have set the correct row width, you should see the fixed width file data correctly on the Source data columns section. Now, you have to click at the appropriate locations to determine the column limits. Note the sections 4, 5, 6 and in the below screenshot.

• 点击高级部分.您会注意到根据我们在上一步中在 Columns 部分设置的列限制自动为您创建了 5 个列.第五列用于行分隔符.

• Click on the Advanced section. You will notice 5 columns created for you automatically based on the column limits that we set on the Columns section in the previous step. The fifth column is for row delimiter.

• 将列名重命名为FirstName、LastName、Id、Date 和RowDelimiter

• 默认情况下，列将设置为数据类型字符串 [DT_STR].如果我们相当确定某个列将具有不同的数据类型，我们可以在高级"部分对其进行配置.我们将把 Id 列更改为数据类型 四字节有符号整数 [DT_I4] 并将日期列更改为数据类型 date [DT_DATE]

• By default, the columns will be set with DataType string [DT_STR]. If we are fairly certain, that a certain column will be of different data type, we can configure it in the Advanced section. We will change Id column to be of data type four-byte signed integer [DT_I4] and Date column to be of data type date [DT_DATE]

• 点击预览部分.数据将按照列配置显示.

• Click on the Preview section. The data will be shown as per the column configuration.

• 在平面文件连接管理器编辑器上单击确定，平面文件连接将分配给数据流任务中的平面文件源.

• Click OK on the Flat file connection manager editor and the flat file connection will be assigned to the Flat File Source in the data flow task.

• 在平面文件源编辑器上，单击列部分.您会注意到在平面文件连接管理器中配置的列.取消选中 RowDelimiter 因为我们不需要它.

• On the Flat File Source Editor, click on the Columns section. You will notice the columns that were configured in the flat file connection manager. Uncheck the RowDelimiter because we won't need that.

• 在数据流任务上，放置一个OLE DB Destination.将平面文件源的输出连接到 OLE DB 目标.

• On the data flow task, place an OLE DB Destination. Connect the output from the Flat file source to the OLE DB Destination.

• 在 OLE DB Destination Editor 上，选择名为 SQLServer 的 OLE DB Connection manager 并设置表或视图的名称 drop直到 [dbo].[FlatFile]

• On the OLE DB Destination Editor, select the OLE DB Connection manager named SQLServer and set the Name of the table or the view drop down to [dbo].[FlatFile]

• 在 OLE DB 目标编辑器上，单击映射部分.由于平面文件连接管理器中的列名与数据库中的列名相同，映射将自动进行.如果名称不同，则必须手动映射列.单击确定".

• 现在包准备好了.执行包以将固定宽度的平面文件数据加载到数据库中.

• 如果查询数据库中的表 dbo.FlatFile，您会注意到导入数据库的平面文件数据.

这个示例应该让您了解如何将固定宽度的平面文件导入数据库.它没有解释如何处理错误日志，但这应该可以帮助您入门并帮助您在使用包时发现其他与 SSIS 相关的功能.

This sample should give you an idea about how to import fixed-width flat file into database. It doesn't explain how to handle error logging but this should get you started and help you discover other SSIS related features when you play with packages.

希望有所帮助.

SQL 脚本:

CREATE TABLE [dbo].[FlatFile](
    [Id] [int] NOT NULL,
    [FirstName] [varchar](25) NOT NULL,
    [LastName] [varchar](25) NOT NULL,
    [Date] [datetime] NOT NULL
)

这篇关于如何使用 SSIS 将固定宽度的平面文件导入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！