Question

是否有任何人对如何使用SSIS一揽子计划向数据库输入固定的湿度假档案进行辅导?

我有一个固定的档案,有几长的栏目。

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

我如何将一个固定档案转换成一栏?

Answer 1

这里是利用SSIS 2008 R2制作的一套样本,说明如何将一个固定档案输入数据库表。

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.

Flat file data

In the SQL server database, create a table named dbo.FlatFile using the create script provided under SQL Scripts section.
创建新的SSIS一揽子计划,并增加一个新的OLE DB联系管理人,与SQ服务器数据库连接。让我们假设,OLE DB联系管理人的名字是SQLServer。

Connection manager

On the package s control flow tab, place a Data Flow Task.

Data flow task

关于数据流动任务的双轨数据,将输入数据流动表。关于数据流表,请登陆Flat文档<>。统一档案来源和Flat文档来源编辑<>m>上的双立影印本。 www.un.org/spanish/ga/president 法文版

关于Flat文档来源主编的一般部分,在Connection Manager name(saySource)和浏览到统一档案地点并选择档案。这一例子在<条码>C: empFixed_Width_File.tx中使用了样本文档。如果你在案卷中拥有头盔,你可以把头角的数值一输入,以绕过文字箱,跳出头角。

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.

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.

Rename the column names as FirstName, LastName, Id, Date and RowDelimiter

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.

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.

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

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]

On the OLE DB Destination Editor, click on the Mappings section. Since the column names in the flat file connection manager are same as the columns in the database, the mapping will take place automatically. If the names are different, you have to manually map the columns. Click OK.

Now the package is ready. Execute the package to load the fixed-width flat file data into the database.

If you query the table dbo.FlatFile in the database, you will notice the flat file data imported into the database.

https://i.stack.imgur.com/KsTCp.png” alt=“Data import into table”/>

这一样本应使您了解如何将固定宽度的固定档案输入数据库。它没有解释如何处理错误的伐木,但这应该让你开始,帮助你发现其他与信息社会首脑会议有关的特征。

希望会有所帮助。

<><><><Ros>:>

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

Answer 2

In the derived column transformation you can use SUBSTRING() function for each of the column. Example:

Columns DerivedColumn

FirstName SUBING(Data,startFrom, Length);

在这里,第一夫人有25人,如果我们认为,从上列第0位的位置来看,你应该通过提供SUBSTRING(Data, 0, 25);

同样,其他栏目也是如此。

Answer 3

Siva! 你的理论和出色的插图指出,微软本应明确指出哪些内容。

that the width for a fixed length row has to include the Carriage Return and Line Feed (CR & LF) characters (which I figured out because the preview showed the rows were not lining up correctly)
the all important step of defining an extra column to contain those CR & LF characters, even though they won t be imported. I figured this out, too. I would have benefited by finding your answer before I began.

Without those two things, an attempt to run the import will give this error message: The data conversion for column "Column x" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

我在这份错误案文中补充说,希望有人在寻找错误原因时找到这一页。即便在事实之后,你也值得tur。

友情链接