A Salesforce Object Join for Informatica Cloud with VB.NET

 

With Informatica Cloud there are native connectors to Salesforce to bring in the data that is needed for the given Extract, Transform and Load (ETL) requirements.  These native connectors can bring in a single custom object and can be extended to bring in related objects.  An example of bringing in related objects would be if you want to bring in a certain set of Accounts and then all of the related Contacts.  This can all be done with point-and-click within Informatica Cloud which makes it great for not needing a developer to extend it.

But what if you have two non-related tables that cannot be joined using the standard point-and-click functionality?  I had one such example recently where I had a set of languages and I had to find the matching set of language codes, which were located in a separate custom object.  So basically I would have the value of English or French and I would need to find the language codes of ENG and FRH respectively.

One way to solve this problem would be to change the database schema where my column would not be a picklist, but instead would be a lookup to the language code table.  If I could have made this change in the database schema that could have solved this problem, but changing the field was not possible.  For one thing there was logic based off of this picklist field so it could not be changed to a lookup, and secondly it was a multi-select field so I would have needed to create a many-to-many junction object to solve the problem.

So at this point I was stuck and needed to go down a custom route.

What I decided to do was to use Informatica Cloud to retrieve the language values from the first table in one Data Synchronization Task and then to retrieve the language codes in a separate Data Synchronization Task.  I wrapped these two Data Synchronization Tasks together with a Task Flow so they would be retrieved one after the other.  Now I just needed some logic to join these values together.

I decided to use a similar solution to what I had described in an earlier blog post called ‘Extending Informatica Cloud with a .NET EXE’.  I created a VB.NET exe that could be called in a post-processing command.  The exe would accept three parameters…

1) The csv file produced by the first task which contains the languages.
2) The csv file produced by the second task which contains the language codes.
3) The output file desired.

I want to show you two parts of this VB.NET exe.

1) How to accept input parameters.

This is really easy in a VB.NET exe.  Simply use the System.Environment.GetCommandLineArgs() method call to retrieve an array of string values, which will contain your parameters.

1
2
3
4
5
6
7
8
9
10
Sub Main()
  Dim sLanguageFile as string
  Dim sLanguageCodes as string
  Dim sOutputFile as string
  Dim s() As String = System.Environment.GetCommandLineArgs()
  'The first parameter s(0) is the file path of the exe itself
  sLanguageFile = s(1)
  sLanuageCodes = s(2)
  sOutputFile = s(3)
End Sub

2) How to load up a csv file into a DataTable

Once again with VB.NET this is very simple by using some built-in libraries.  In the following routine the Microsoft.VisualBasic.FileIO.TextFieldParser allows you to easily loop through the csv file.  In this routine a datatable with 50 columns is produced and each column from the csv file is placed into a column in the datatable.  Of course this routine could be improved to pass in the number of columns and types of columns to be created, but for this solution nothing that complicated was needed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Private Function GetCSVFile(ByVal sInputFile As String) As DataTable
        Dim dtValue As New DataTable
        Dim drNew As DataRow
        Dim i As Integer
        Dim currentField As String
        Dim currentRow As String()
        For i = 1 To 50
            dtValue.Columns.Add("Col" & i)
        Next i
        'http://msdn.microsoft.com/en-us/library/cakac7e6(v=vs.100).aspx
        Using MyReader As New Microsoft.VisualBasic.
                FileIO.TextFieldParser(
                sInputFile)
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(",")
            While Not MyReader.EndOfData
                Try
                    currentRow = MyReader.ReadFields()
                    drNew = dtValue.NewRow()
                    i = 0
                    For Each currentField In currentRow
                        i = i + 1
                        drNew.Item("Col" & i) = currentField
                    Next
                    dtValue.Rows.Add(drNew)
                Catch ex As Microsoft.VisualBasic.
                            FileIO.MalformedLineException
                    Console.WriteLine("Line " & ex.Message &
                        "is not valid and will be skipped.")
                End Try
            End While
        End Using
        Return dtValue
    End Function

Finally language values and codes can be placed into a HashTable, which is a name/value key pair data structure.  Whenever a language value needs its corresponding code the language code can be looked up in the HashTable.

This has been another example that with just a little bit of extending the Informatica Cloud platform can be very flexible.

Happy Integrating!

This entry was posted in Technology and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>