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.
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 |
|
|
|
|
|