SSIS数据如何处理,大侠帮忙楼

0
"Time","LineName","MachineName","LaneNo","ProgramName","PanelID","PanelStatus"
"2013/03/13 16:00:32","Training","NXT","1","Test_TKQ NXT","C7P305608SVF346A904EF0B2","0"


"SequenceNo","ModuleNo","StageNo","SlotNo","PartNo","ReelID","FeederID","Vendor","LotNo","DateCode","LightingClass","PickupStatus","BlockNo","Reference"
"838","1","1","11","RECT1005050_N","RIDTEST001","KT08D 091983","","","","6","0","1","12"
"966","1","1","11","RECT1005050_N","RIDTEST001","KT08D 091983","","","","6","0","1","24"
"982","1","1","11","RECT1005050_N","RIDTEST001","KT08D 091983","","","","6","0","1","36"
"998","1","1","11","RECT1005050_N","RIDTEST001","KT08D 091983","","","","6","0","1","48"
"1206","1","1","11","RECT1005050_N","RIDTEST001","KT08D 091983","","","","6","0","1","60"

在一个FLAT FILE的文件中有以上两种格式的内容,如何整合后将数据导入到数据库,整合到数据库中的格式如下:
C7P305608SVF346A904EF0B2,Test_TKQ NXT,838,1,1,11,RECT1005050_N,RIDTEST001,KT08D 91983,,,,6,0,1,12






已邀请:
2

天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2014-05-31 回答

首先,你这个文件可以拆分成2部分吗?
如果可以,就方便了很多。

在可以拆分的情况下, 如下做法:
  1. 建立2个 FlatFileSource, 读取它们的内容
  2. 为每个FlatFileSource添加一个扩展列
  3. 随便起个名字,列的值给1
  4. 排序
  5. MergeJoin 它们,使用你刚刚创建的新列(犹豫你的数据没有规律,只有使用这个新列)

如果你有很多数据,那么就需要加入更多的实现逻辑。 我这只是一个简单的例子。


2.png


1.png


0.png


3.png
0

梁勇 - 天道酬勤、上善若水。爱好商业智能 2014-05-14 回答

有没有啥规律,我没看到你这个有一定的规律呀。合并的话,SSIS有UNION ALL控件,拆分的话,SSIS也有拆分控件,建议最好把对应的规律说下,以便大家更好地帮助你。
0

shanzi 2014-05-14 回答

2014-05-14_213007.jpg

在文件的开始的前两行是一种格式,第一行可以认为是column name,第二行是实际的数据记录,且仅有一行数据。
第三、第四行是空行,没有数据。
第五行开始时两一种格式,第五行也可以认为是column name,第六行开始时数据记录,有多行。

假设第一种格式有数据A,第二种有数据B、C、D. 导入到数据库中的数据需要时仅需要有三条记录
A,B
A,C
A,D
即前一种格式的唯一的记录行与后一种格式的数据做笛卡尔积/Cross Join

以上请帮忙,感谢感谢感谢!!




要回复问题请先登录注册