无法识别SSIS衣衫file的文件CRLF [英] SSIS ragged file not recognized CRLF
本文介绍了无法识别SSIS衣衫file的文件CRLF的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在SSIS中,我尝试从平面文件加载数据. 平面文件具有固定宽度的列,但行中不存在某些列(一列可以具有CRLF,必须为新行)
In SSIS, I try to load data from a flat file. The flat file have fixed width columns, but some column are not present in a row (a column can have a CRLF, which must be a new line) like this
a b c
the first rowok<CRLF>
iu jjrjdd<CRLF>
this is a newline<CRLF>
如何在输出中拥有完全相同数量的行和数据?
How I can have exactly the same number of line and exact data in my output?
我建立了一个平面文件连接,类型参差不齐.
I setup a flat file connection, of ragged right type.
在此示例中,正确检索了第1行,但对于第2行,它无法识别CRLF,因此将第3行的所有内容都放在了b列中.
In this sample, row 1 is correctly retrieve, but for row 2, it didn't recognize CRLF, and put in b column all the 3rd row.
推荐答案
解决方法
- 在平面文件连接管理器中,将整行读取为一列(仅添加一列DT_STR类型且长度为4000的列)
- 然后在数据流任务中添加脚本组件
- 添加三个类型为
DT_STR
的输出列(a,b,c)
- Then in the dataflow task add a script component
- Add three output column (a,b,c) of type
DT_STR
- 编写一个脚本,该脚本将每一行拆分并将值放入列中(如果缺少一个值,则为null)(我用过vb.net)
制表符分隔列
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not Row.Column0_IsNull AndAlso
Not String.IsNullOrEmpty(Row.Column0.Trim) Then
Dim str() As String = Row.Column0.Split(CChar(vbTab))
If str.Length >= 3 Then
Row.a = str(0)
Row.b = str(1)
Row.c = str(2)
ElseIf str.Length = 2 Then
Row.a = str(0)
Row.b = str(1)
Row.c_IsNull = True
ElseIf str.Length = 1 Then
Row.a = str(0)
Row.b_IsNull = True
Row.c_IsNull = True
Else
Row.a_IsNull = True
Row.b_IsNull = True
Row.c_IsNull = True
End If
Else
Row.a_IsNull = True
Row.b_IsNull = True
Row.c_IsNull = True
End If
End Sub
固定宽度的列
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not Row.Column0_IsNull AndAlso
Not String.IsNullOrEmpty(Row.Column0.Trim) Then
'Assuming that
'Col a => 0-5
'Col b => 5-15
'Col c => 15-
Dim intlength As Integer = Row.Column0.Length
If intlength <= 5 Then
Row.a = Row.Column0
Row.b_IsNull = True
Row.c_IsNull = True
ElseIf intlength > 5 AndAlso intlength <= 15 Then
Row.a = Row.Column0.Substring(0, 5)
Row.b = Row.Column0.Substring(5, 10)
Row.c_IsNull = True
ElseIf intlength > 15 Then
Row.a = Row.Column0.Substring(0, 5)
Row.b = Row.Column0.Substring(5, 10)
Row.c = Row.Column0.Substring(15)
End If
Else
Row.a_IsNull = True
Row.b_IsNull = True
Row.c_IsNull = True
End If
End Sub
您还可以使用派生列转换来实现此目的
这篇关于无法识别SSIS衣衫file的文件CRLF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文