Iterate through Columns per row via Script Component

[sauce1979]

In one of the packages I am developing I would like to run data through script component and loop through columns whose names contain _CreationID and are not null or blank. I then want to and check that the column values per are the same. However I am struggling to write the vb code in the script editor. I think the process should be

1) loop through columns whose names contain _creationID and are not blank/null
2) load values into an array
3) check if all elements in array are have the same value
4) if the elements have the same value create new output column with value 1
5) if the elements are not matched create new output column with value 0

My code thus far is as follows:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

        Dim rowType As Type = Row.GetType()

        Dim columnValue As PropertyInfo

        Dim previousRow As String


        For Each columnValue In Row.GetType().GetProperties()
            If (columnValue.Name.EndsWith("_CreationID") And columnValue.GetValue(Row, Nothing).ToString() = "") Then
                ' How can I access they value of the column and load it into and array?
                ' How can I check elements in the array are equal? 
            End If
        Next

        '
    End Sub

End Class

Maybe my strategy is incorrect and there is an easier way. Any ideas would be appreciated.

 

[sauce1979]

TO HELP GIVE A BETTER PICTURE OF THE SITUATION I HAVE ADDED SOME TEST DATA

CREATE TABLE #TEST(
	AUTOMATCHID INT IDENTITY (1,1) NOT NULL
,	A_CREATIONID VARCHAR(20) NULL
,	B_CREATIONID VARCHAR(20) NULL
,	C_CREATIONID VARCHAR(20) NULL
,	D_CREATIONID VARCHAR(20) NULL
,	E_CREATIONID VARCHAR(20) NULL
,	F_CREATIONID VARCHAR(20) NULL
,	G_CREATIONID VARCHAR(20) NULL
,	H_CREATIONID VARCHAR(20) NULL
)


INSERT INTO #TEST
SELECT 'ID12345','ID12345','ID12345','ID12345','ID12345','ID12345','ID12345','ID12345'
UNION ALL
SELECT 'ID12346','ID12349','','','','','',''
UNION ALL
SELECT 'ID12350','','','','','','','ID125454'
UNION ALL
SELECT 'ID12385','','','ID12385','','','',''
UNION ALL
SELECT '','','','','','','',''



SELECT * FROM #TEST

For every row there are 8 fields (whose name ends with_creationID) which can contain a IDnumber. All 5 _CreationID can be filled or 1 _CreationID can be filled. It is totally varied. If more than 1 _CreationID column is filled a check has to be done on all the values per row to see if they are the same. If they are that row is sent to 1 output and if not it is sent to another output.

In development at the moment we are working with 5 fields but the aim is to have14 fields. Therefore i thought a conditional split expression would be very complicated with so many possible permutations in the fields values.

 

[Alan0568]

Its not clear from your description what you want to do with rows which only have 1 column which isn't null or blank.

Anyway I would just do it as two sql select statements in two different ole db source objects. Or you could union the two together in one select with a hard coded identifier column 'A' & 'B' for the two outputs.

-- Rows with more than one which are all the same
select t.* from test t
inner join
	(select x.AUTOMATCHID, count(distinct creationid) as cid
	from
	(select 
	AUTOMATCHID,A_CREATIONID as creationid
	from test
	union all
	select 
	AUTOMATCHID,B_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,C_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,D_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,E_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,F_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,G_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,H_CREATIONID 
	from test) x
		inner join 
		(select AUTOMATCHID from test 
		where
		(case when A_CREATIONID <> '' then 1 else 0 end +
		case when B_CREATIONID <> '' then 1 else 0 end +
		case when C_CREATIONID <> '' then 1 else 0 end +
		case when D_CREATIONID <> '' then 1 else 0 end +
		case when E_CREATIONID <> '' then 1 else 0 end +
		case when F_CREATIONID <> '' then 1 else 0 end +
		case when G_CREATIONID <> '' then 1 else 0 end +
		case when H_CREATIONID <> '' then 1 else 0 end ) >1) y 
		on x.AUTOMATCHID=y.AUTOMATCHID
		and x.creationid <> ''

  group by x.AUTOMATCHID
  having count(distinct x.creationid) =1) z
on t.AUTOMATCHID=z.AUTOMATCHID

--- Rows with more than one which are not the same
select t.* from test t
inner join
	(select x.AUTOMATCHID, count(distinct creationid) as cid
	from
	(select 
	AUTOMATCHID,A_CREATIONID as creationid
	from test
	union all
	select 
	AUTOMATCHID,B_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,C_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,D_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,E_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,F_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,G_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,H_CREATIONID 
	from test) x
		inner join 
		(select AUTOMATCHID from test 
		where
		(case when A_CREATIONID <> '' then 1 else 0 end +
		case when B_CREATIONID <> '' then 1 else 0 end +
		case when C_CREATIONID <> '' then 1 else 0 end +
		case when D_CREATIONID <> '' then 1 else 0 end +
		case when E_CREATIONID <> '' then 1 else 0 end +
		case when F_CREATIONID <> '' then 1 else 0 end +
		case when G_CREATIONID <> '' then 1 else 0 end +
		case when H_CREATIONID <> '' then 1 else 0 end ) >1) y 
		on x.AUTOMATCHID=y.AUTOMATCHID
		and x.creationid <> ''

  group by x.AUTOMATCHID
  having count(distinct x.creationid) >1) z
on t.AUTOMATCHID=z.AUTOMATCHID

 

[sauce1979]

hey I actually managed to get this working with some tweaking. I used a derived column transform to create a field that concatenates the lookup fields with a semi colon delimeter. The expression was as follows:

(ISNULL(F_CreationID) ? "" : F_CreationID) + ";" + (ISNULL(Ind_CreationID) ? "" : Ind_CreationID) + ";" + (ISNULL(ISWC_CreationID) ? "" : ISWC_CreationID) + ";" + (ISNULL(ISRC_CreationID) ? "" : ISRC_CreationID) + ";" + (ISNULL(ISAN_CreationID) ? "" : ISAN_CreationID) + ";" + (ISNULL(EAN_CreationID) ? "" : EAN_CreationID)

I then script transfom. I the loaded the concatenated column into an array. I then deleted elements and checked if the elements were equal and then redirected outputs appropriately. The script was as follows.

 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        ' 
       

        'Call is IsAlphaNumeric function to check if field contains only semi colons. If true then there is no match
        If IsAlphaNumeric(Row.concatCreationID) Then
            Dim vals() As String = Strings.Split(Row.concatCreationID, ";")
            'Creat an split sting by delimeter and load array 
            Dim ListVals As List(Of String) = vals.ToList()
            'Load array contents to list. List is chosen so we can easily add and remore elements
            Dim g As Integer

            'remove non=empty elements from  list
            For g = ListVals.Count - 1 To 0 Step -1
                If ListVals(g) = "" Then
                    ListVals.RemoveAt(g)
                End If
            Next


            'If list contains only 1 item send it directl to Match output
            If ListVals.Count = 1 Then
                Row.MatchCreationID = ListVals(0)
                Row.DirectRowToMatch()
                'If list contains more than 1 element and List check returns true i.e. elements are the same
                'direct row to Match output
            ElseIf ListVals.Count > 1 And Listcheck(ListVals) Then
                Row.MatchCreationID = ListVals(0)
                Row.DirectRowToMatch()
            Else

                Row.DirectRowToSuggestions()


            End If

        Else
            Row.DirectRowToNoMatch()
        End If


    End Sub
    Public Function IsAlphaNumeric(ByVal strToCheck As String) As Boolean
        Dim pattern As Regex = New Regex("[^;*]")

        Return pattern.IsMatch(strToCheck)
    End Function
    Public Function Listcheck(ByVal ListToCheck As List(Of String)) As Boolean

        Listcheck = True

        For I As Integer = 0 To ListToCheck.Count

            If ListToCheck(0) <> ListToCheck(I) Then
                Listcheck = False
                Exit For

            End If

        Next

    End Function

It seems to have done the trick. Many thanks for your help

 

[Alan0568]

Good stuff, glad you are sorted.