After a while I have decided to create the lookup functionality in T-SQL. I have achieved this by using left outer joins and returning the same column for each join with a different alias. My code is as follows:

WITH testCTE (AutoMatchID, TITLE, [FINGERPRINTID], LabelCode, SetCatalogueNumber, ISAN, ReferenceNumber, DSPID,CATALOGUENUMBER,RESOURCEID, [INTERNATIONALSTANDARDRECORDINGCODE],[INTERNATIONALSTANDARDWORKCODE],[EANBARCODE],ParameterSetID ,RECID,[TableID])

AS

(

--- USAGEJOURNALTRANS_ONLINE

SELECT B.AutoMatchID

, A.TITLE

, NULL AS [FINGERPRINTID]

, NULL AS LabelCode

, NULL AS SetCatalogueNumber

, NULL AS ISAN

, NULL AS ReferenceNumber

, A.DSPID

, A.CATALOGUENUMBER

, A.RESOURCEID

, A.[INTERNATIONALSTANDARDRECORDINGCODE]

, A.[INTERNATIONALSTANDARDWORKCODE]

, A.[EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_ONLINE] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102787

UNION ALL

--USAGEJOURNALTRANS_CL

SELECT B.AutoMatchID

, A.TITLE

, NULL AS [FINGERPRINTID]

, NULL AS LabelCode

, A.SetCatalogueNumber

, NULL AS ISAN

, NULL AS ReferenceNumber

, NULL AS DSPID

, A.CATALOGUENUMBER

, NULL AS RESOURCEID

, NULL AS [INTERNATIONALSTANDARDRECORDINGCODE]

, NULL AS [INTERNATIONALSTANDARDWORKCODE]

, NULL AS [EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_CL] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102791

UNION ALL

--USAGEJOURNALTRANS_FOREIGN

SELECT B.AutoMatchID

, A.TITLE

, NULL AS [FINGERPRINTID]

, NULL AS LabelCode

, NULL AS SetCatalogueNumber

, A.ISAN

, A.ReferenceNumber

, NULL AS DSPID

, A.CATALOGUENUMBER

, NULL AS RESOURCEID

, A.[INTERNATIONALSTANDARDRECORDINGCODE]

, A.[INTERNATIONALSTANDARDWORKCODE]

, A.[EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_FOREIGN] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102793

UNION ALL

--USAGEJOURNALTRANS_INDUSTRY

SELECT B.AutoMatchID

, A.TITLE

, NULL AS [FINGERPRINTID]

, A.LabelCode

, NULL AS SetCatalogueNumber

, NULL AS ISAN

, A.ReferenceNumber

, NULL AS DSPID

, A.CATALOGUENUMBER

, NULL AS RESOURCEID

, NULL AS [INTERNATIONALSTANDARDRECORDINGCODE]

, NULL AS [INTERNATIONALSTANDARDWORKCODE]

, NULL AS [EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_INDUSTRY] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102785

UNION ALL

--USAGEJOURNALTRANS_LIVEEVENTS

SELECT B.AutoMatchID

, A.TITLE

, A. [FINGERPRINTID]

, NULL AS LabelCode

, NULL AS SetCatalogueNumber

, NULL AS ISAN

, NULL AS ReferenceNumber

, NULL AS DSPID

, NULL AS CATALOGUENUMBER

, NULL AS RESOURCEID

, NULL AS [INTERNATIONALSTANDARDRECORDINGCODE]

, NULL AS [INTERNATIONALSTANDARDWORKCODE]

, NULL AS [EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_LIVEEVENTS] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102783

UNION ALL

--USAGEJOURNALTRANS_MEDIA

SELECT B.AutoMatchID

, A.TITLE

, A. [FINGERPRINTID]

, NULL AS LabelCode

, NULL AS SetCatalogueNumber

, NULL AS ISAN

, NULL AS ReferenceNumber

, NULL AS DSPID

, A. CATALOGUENUMBER

, NULL AS RESOURCEID

, A. [INTERNATIONALSTANDARDRECORDINGCODE]

, NULL AS [INTERNATIONALSTANDARDWORKCODE]

, NULL AS [EANBARCODE]

, B.ParameterSetID

, B.RECID

, B.[TableID]

FROM [DAX_ACC].[dbo].[USAGEJOURNALTRANS_MEDIA] A INNER JOIN [AutoMatch].[dbo].[AID] B

ON A.RECID = B.RECID

AND B.[TableID] = 102781

)

(

SELECT A.*

, COALESCE(B.[CREATIONID],'') AS F_CreationID

, COALESCE(C.[CREATIONID],'') AS Ind_CreationID

, COALESCE(D.[CREATIONID],'') AS ISWC_CreationID

, COALESCE(E.[CREATIONID],'') AS ISRC_CreationID

, COALESCE(F.[CREATIONID],'') AS ISAN_CreationID

, COALESCE(G.[CREATIONID],'') AS EAN_CreationID

FROM testCTE A

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] B

ON B.[CRNUMBER] = A.[FINGERPRINTID] AND B.[CRNUMBERTYPEID] = 'fingerprin'

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] C

ON c.[CRNUMBER] = A.[FINGERPRINTID] AND C.[CRNUMBERTYPEID] = 'Ind Conv'

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] D

ON D.[CRNUMBER] = A.[INTERNATIONALSTANDARDWORKCODE] AND D.[CRNUMBERTYPEID] = 'ISWC'

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] E

ON E.[CRNUMBER] = A.[INTERNATIONALSTANDARDRECORDINGCODE] AND E.[CRNUMBERTYPEID] = 'ISRC'

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] F

ON F.[CRNUMBER] = A.[ISAN] AND F.[CRNUMBERTYPEID] = 'ISAN'

LEFT OUTER JOIN [ReferenceData].[dbo].[CRALTNUMBERS] G

ON G.[CRNUMBER] = A.[EANBARCODE] AND G.[CRNUMBERTYPEID] = 'EAN'

)

I would like to run the data through script component and loop through the _CreationID columns per row which are not null or blank and check that the column values are the same. However I am struggling to write the vb code int the script editor. I think the process should be

loop through columns whose names contain _creationID and are not blank/null

load values into an array

check if all elements in array are have the same value

if the elements have the same value create new output column with value 'matched'

if the elements are not matched create new output column with value 'non_matched'

My code thus far is:

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

Columnval = columnValue.GetValue(Row, Nothing).ToString()

End If

Next

'

End Sub

End Class

Any ideas?