Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Conditional Lookup

Author  Topic 

sauce1979
Starting Member

47 Posts

Posted - 2013-03-13 : 08:53:41
Currently working on a project for a client which involves me building a matching solution in SSIS. One of the packages I am building needs a conditional lookup. i.e. Do a lookup on a column if it is not blank/null. There are total of 5 columns. If all 5 are blank then don't do the lookup. If 3 are filled do the lookup on the 3 fields etc. Anybody have experience implementing this sort of logic in SSIS?

sauce1979
Starting Member

47 Posts

Posted - 2013-03-18 : 18:29:51
I 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





Seems to have done the trick

Go to Top of Page
   

- Advertisement -