在SSIS中将固定宽度的行拆分为多行 [英] Split fixed width row into multiple rows in SSIS

问题描述

我有一个固定宽度的平面文件，需要将其加载到多个oracle表中(一行需要拆分成多行)

I Have a fixed width flat file and that needs to be loaded into multiple oracle tables(one row needs to be split into multiple rows)

每列顶部的数字是它们的大小， 并且我想要的输出应如下图所示.

The numbers which are on top of each column is their size, and my desired output should look like shown below.

Flatfile data(fixed width):
3   6    3   11          3   10        3   10        3
ID NAME  AGE CTY1       ST1 CTY2      ST2 CTY3      ST3
200JOHN  46  LOSANGELES CA  HOUSTON   TX  CHARLOTTE NC
201TIMBER54  PHOENIX    AZ                CHICAGO   IL
202DAVID 32  ATLANTA    GA  PORTLAND  AZ

发生的次数可能会有所不同.它可能会增长到20-30

The occurrence may vary.. it can grow upto 20-30

DESIRED OUTPUT:
TABLE1
ID NAME  AGE
200JOHN  46
201TIMBER54
202DAVID 32


TABLE2
ID  SEQ CTY        ST
200 1   LOSANGELES CA  
200 2   HOUSTON    TX  
200 3   CHARLOTTE  NC
201 1   PHOENIX    AZ      
201 2   CHICAGO    IL        
202 1   ATLANTA    GA  
202 2   PORTLAND   AZ

有人可以帮我吗?

谢谢！

推荐答案

我会先听@bilinkc给出的建议，然后尝试不做任何改动.

I would listen to the advice given by @bilinkc first and attempt to solve this with an unpivot.

单击此处了解详细信息有关如何使用SSIS Unpivot数据流转换的信息.

Click here for details on how to use the SSIS Unpivot Data Flow Transformation.

但是，如果由于某种原因不能解决问题，并且您真的想用SSIS解决此问题，我(很高兴)说，使用SSIS和一个数据流来解决问题在技术上是可行的.

However, if that does not work out for some reason and you really want to solve this with SSIS, I am (kind of) happy to say it is technically feasible to solve the problem using SSIS and one data flow.

以下是步骤的缩写列表:

Below are an abbreviated list of steps:

1)将数据流任务添加到您的程序包中

1) Add a Data Flow Task to your package

2)将平面文件源添加到您的数据流任务

2) Add a Flat File Source to your Data Flow Task

3)使用连接管理器为您的平面文件配置平面文件源

3) Configure the Flat file Source with a Connection Manager for your flat file

4)向数据流任务添加多播数据流转换

4) Add a Multicast Data Flow Transformation to your Data Flow Task

5)将平面文件源与多播数据流转换相连接

5) Connect your Flat File Source with the Multicast Data Flow Transformation

现在，有趣"部分(复制和粘贴可以在这里节省您的时间)...

Now the "fun" part (copy and paste can save you time here)...

6)向您的数据流任务中添加30个条件拆分数据流转换

6) Add 30 Conditional Split Data Flow Transformations to your Data Flow Task

7)将组播数据流转换连接到每个条件拆分数据流

7) Connect the Multicast Data Flow Transformation to each Conditional Split Data Flow

8)配置每个条件拆分N，以拉取州N和城市N具有值的行子集

8) Configure each Conditional Split N to pull the row subset where State N and City N has a value

示例:条件拆分1

输出名称:CTY1_ST1

Output Name: CTY1_ST1

条件:[CTY1]！="&& [ST1]！="

Condition: [CTY1] != "" && [ST1] != ""

9)将30个派生列数据流转换添加到您的数据流中

9) Add 30 Derived Column Data Flow Transformations to your data flow

10)将每一个连接到您的30个条件拆分

10) Connect each one to your 30 Conditional Splits

11)为每个配置派生列名称SEQ，并将其值设置为1到30

11) Configure each with a Derived Column Name SEQ and a value 1 to 30

12)添加联合所有数据流转换"和联合所有30个数据管道"

12) Add a Union All Data Flow Transformation and Union All 30 of the data pipes back together

现在，简单"部分...

Now the "easy" part...

13)将您的第一个排序转换添加到数据流任务中

13) Add your first Sort Transformation to your Data Flow Task

14)将第31个多播管道连接到您的第一个排序转换

14) Connect a 31st Multicast pipe to your first Sort Transformation

15)在旁边打勾并按ID排序(希望ID:NAME和ID:AGE为1:1)

15) Put a check mark next to and sort by ID (Hopefully ID:NAME and ID:AGE is 1:1)

16)选中删除具有重复排序值的行

16) Check Remove rows with duplicate sort values

17)添加第二个多播数据流转换

17) Add your second Multicast Data Flow Transformation

18)向您的数据流任务中添加第二个排序转换

18) Add a second Sort Transformation to your Data Flow Task

19)将您的Union All连接到第二个Sort Transformation并按ID进行排序

19) Connect your Union All to your second Sort Transformation and sort by ID

20)将合并联接添加到您的数据流任务

20) Add a Merge Join to your Data Flow Task

21)将第二个多播数据流转换连接为左输入

21) Connect your second Multicast Data Flow Transformation as the Left Input

22)将第二个排序转换连接到合并联接作为正确的输入

22) Connect your second Sort Transformation to your Merge Join as your Right Input

23)将合并联接配置为联接类型=内部联接，然后选择ID，SEQ，CTY，ST列

23) Configure your Merge Join as Join Type = Inner Join and select columns ID, SEQ, CTY, ST

24)将第一个OLE DB目标添加到数据流，并将您的合并联接连接到它(结果为TABLE2)

24) Add your first OLE DB Destination to your data flow and connect your Merge Join to it (the result is TABLE2)

25)将第二个OLE DB目标添加到您的数据流，并将第二个多播数据流转换连接到它(结果为TABLE1)

25) Add a second OLE DB Destination to your data flow and connect your second Multicast Data Flow Transformation to it (the result is TABLE1)

这篇关于在SSIS中将固定宽度的行拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！