非常感谢您的帮助。我需要针对编写SSIS脚本组件以将单行分隔为多行寻求帮助。以下是我查看的许多有用博客和文章:
http://bi-polar23.blogspot.com/2008/06/splitting-delimited-column-in-ssis.html
然而,我需要一些编码方面的额外帮助才能完成该项目。基本上这是我想做的事情。输入数据
ID 项名称 1 Apple01,02,Banana01,02,03 2 Spoon1,2,Fork1,2,3,4
输出数据
ParentID ChildID Item Name 1 1 Apple01 1 2 Apple02 1 3 Banana01 1 4 Banana02 1 5 Banana03 2 1 Spoon1 2 2 Spoon2 2 3 Fork1 2 4 Fork2 2 5 Fork3 2 6 Fork4
以下是我的编码尝试,但如果不合逻辑,请随意修改。SSIS异步输出已设置。
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim posID As Integer, childID As Integer
Dim delimiter As String = ","
Dim txtHolder As String, suffixHolder As String
Dim itemName As String = Row.ItemName
Dim keyField As Integer = Row.ID
If Not (String.IsNullOrEmpty(itemList)) Then
Dim inputListArray() As String = _
itemList.Split(New String() {delimiter}, _
StringSplitOptions.RemoveEmptyEntries)
For Each item As String In inputListArray
Output0Buffer.AddRow()
Output0Buffer.ParentID = keyField
If item.Length >= 3 Then
txtHolder = Trim(item)
Output0Buffer.ItemName = txtHolder
'when item length is less than 3, it's suffix
Else
suffixHolder = Trim(item)
txtHolder = Left(txtHolder.ToString(), Len(txtHolder) _
- Len(suffixHolder)) & suffixHolder.ToString()
Output0Buffer.ItemName = txtHolder
End If
Next
End If
End Sub
当前代码产生以下输出
ID 项目名称 1 苹果01 1 02 1 香蕉01 1 02 1 03 2 匙1 2 2 2 叉1 2 2 2 3 2 4