November 8, 2007 at 9:00 am
Hi all,
I try to make a split of an array of values (1549;787;6476) and during this cicle I want insert a row for every step. In the code above into the destination file I got only last index of array.
Alen Italy
Public Class ScriptMain
Inherits UserComponent
Dim iEmpCount As Integer
Public Overrides Sub CreateNewOutputRows()
EmployeeSumOutputBuffer.AddRow()
End Sub
Public Overrides Sub EmployeeInput_ProcessInput(ByVal Buffer As
EmployeeInputBuffer)
While Buffer.NextRow()
EmployeeInput_ProcessInputRow(Buffer)
End While
If Buffer.EndOfRowset Then
EmployeeSumOutputBuffer.EmpCount = iEmpCount
EmployeeSumOutputBuffer.SetEndOfRowset()
End If
End Sub
Public Overrides Sub EmployeeInput_ProcessInputRow(ByVal Row As
EmployeeInputBuffer)
If Row.SalariedFlag = True Then
Row.VacationHours = Row.VacationHours + CType(10, Short)
iEmpCount = iEmpCount + 1
End If
End Sub
End Class
November 8, 2007 at 11:15 am
Alen - since you're in sql2005, try using the new CROSS APPLY predicate. First, though, you need a table-valued function to give you the splits.
alter function split(@val as varchar(max), @spl as varchar(5))
returns @t table (val varchar(max))
as
begin
declare @i int
declare @tmpstr varchar(max)
declare @maxlength int
set @i=1
set @maxlength=len(@val)
set @spl='%'+@spl+'%'
while patindex(@spl,substring(@val,@i,@maxlength))>0
begin
set @tmpstr=left(substring(@val,@i,@maxlength),patindex(@spl,substring(@val,@i,@maxlength))-1)
set @i=@i+patindex(@spl,substring(@val,@i,@maxlength))
insert @t values (@tmpstr)
end
insert @t values (substring(@val,@i,@maxlength))
return
Once you have this, you can use this kind of syntax:
insert MyInsertTable (fieldlist)
select MyTable.otherfields, SplitList.Val
from MyTable CROSS APPLY dbo.split(MyInsertTable.FieldTosplit,';') SplitList
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 2:29 pm
Also - in case you're doing a large amount of splits (on big table, etc...) - the CLR version is about 3 times faster. (15 secs vs 49 secs on 1M records).
This is probably going to get butchered, but here's the code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRowSplit", IsDeterministic:=True, IsPrecise:=True, TableDefinition:="val nvarchar(100)")> _
Public Shared Function Regexsplit(ByVal input As SqlChars, ByVal pattern As SqlString) As IEnumerable
Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value, optionS)
Dim T As String()
T = rex.Split(input.Value)
Return T
End Function
Public Shared Sub FillRowSplit(ByVal obj As Object, ByRef Val As SqlString)
Val = CType(obj, String).ToString
End Sub
End Class
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 9, 2007 at 1:34 am
Hi Matt,
thank for your response.
It was very usefull but I think wrote wrong example for post.
I must split a Value for OUTPUT into script task into the data flow area.
Here belove the rigth example...Split run correctly but only last index array write into row...the other index not write a row as I need.
Simple, How write a row dinamically as much as the index of my array?
Thanks Alen, Italy
-------------
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim arrTemp() As String
Dim iLooper As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Se c'è + di un accessorio correlato per il prodotto
If InStr(Row.arrIDaccessori, ";") > 0 Then
arrTemp = Split(Row.arrIDaccessori, ";")
For iLooper = 0 To UBound(arrTemp)
Row.IDarticoloRiferito = Row.IDarticolo
Row.IDarticoloAccessorio = arrTemp(iLooper)
Next
'Una sola riga
Else
Row.IDarticoloRiferito = Row.IDarticolo
Row.IDarticoloAccessorio = Row.arrIDaccessori
End If
End Sub
End Class
November 9, 2007 at 12:50 pm
You need to use an asynchronous output from the script task to add new rows. I posted a similar example (of using a split to output multiple rows) on my blog here:
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/17/dynamically-pivoting-columns-to-rows.aspx
Hopefully it is helpful. Let me know if you have any questions.
November 12, 2007 at 1:16 am
Perfect this is my solution...
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply