SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Conditional Lookup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sauce1979
Starting Member

46 Posts

Posted - 03/13/2013 :  08:53:41  Show Profile  Reply with Quote
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

46 Posts

Posted - 03/18/2013 :  18:29:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000