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