Process HTML table by HTMLAgility Pack

Ocassionaly I need to deal with data inside a html table. For example, a report generated from Salesforce: though it is in ".xls" format, it is actually a html file. From what I tried, HTMLAgility pack can do quite a lot jobs for you, as demonstrated below.

Firstly I need a report from Salesforce. To do so, I log into my developer account, and navigate to Reports -> Account and Contact Report. From here I select New Accounts report, and press Export Details button to save the report into my local drive.

After save the report, when we open the report, an error message pops up and tells us the file doesn't look like an Excel file.

Ignore the error by pressing Yes button, the file is opened, and everything seems to be fine.

Now close the file and open it by your favorate text editor. Now you see it is actually a html file.

To import such a file by SSIS is not a simple task, as the file cannot be used as an Excel Data Source. What I usually did, is using HTMLAgility  pack in C# to generate the data source.

As usual, the coding task itself is simple, understanding how the HTMLAgility pack deals with the document object is important. Basically it will treat every html tag as a node, then drill down to the lowest child node. After understand this point, it is just a matter of dealing exceptions, for instance, we do not want to process a report with column header only.  Here is the example of using HTMLAgility pack when I need to import Salesforce report

       string filepath = @"D:\sforcerpt\report1458540550328.xls";  
       if (File.Exists(filepath))  
       {  
         HtmlDocument doc = new HtmlDocument();  
         doc.Load(filepath);  
         DataTable dt = new DataTable();  
         foreach (HtmlNode n in doc.DocumentNode.ChildNodes)  
         {  
           if (n.Name == "table" && n.HasChildNodes && n.ChildNodes.Where(x => x.Name == "tr").Count() >= 2)  
           {  
             int NbrOfRows = n.ChildNodes.Where(x => x.Name == "tr").Count();  
             //column headers  
             HtmlNode headerRow = n.SelectSingleNode("/table[1]/tr[1]");  
             foreach (HtmlNode header in headerRow.ChildNodes)  
             {  
               dt.Columns.Add(header.InnerText);  
             }  
             //content rows  
             for (int i = 2; i <= NbrOfRows; i++)  
             {  
               string path = "/table[1]/tr[" + i.ToString() + "]";  
               HtmlNode contentRow = n.SelectSingleNode(path);  
               DataRow dr = dt.NewRow();  
               for (int j = 1; j <= contentRow.ChildNodes.Count; j++)  
               {  
                 dr[j - 1] = contentRow.ChildNodes[j - 1].InnerText;  
               }  
               dt.Rows.Add(dr);  
             }  
           }  
         }  
         foreach (DataRow r in dt.Rows)  
         {  
           Console.WriteLine("Account ID: {0}; Account Name: {1}", r["Account ID"].ToString(), r["Account Name"].ToString());  
         }  
         Console.ReadLine();  
       }  

SSIS & Salesforce - Retrieve Salesforce Entity Data by SOAP API

As advertised, Salesforce is the world No. 1 on demand CRM platform. It is quite popular nowadays, across different sizes and areas of businesses. From our clients, the platform is being used by sport utilities, government agencies, energy retailers, and computer and service retailers.

Unfortunately it still has its own problems, for example, data quality problem. Due to the openess of the Salesforce platform, it is nearly impossible to control the data quality from its own features, at least from our clients, data report generated from Salesforce is in horrible quality, definitely cannot be used for reporting/analysis purpose.

So what we could do is extracting the raw data from Salesforce, and do some cleansing task, load into SQL Server database, a standard ETL procedure. However it is difficult to find a free plugin/component you can use in your SSIS to extract Salesforce data. So what I would like to do, is to show how to retrieve the data from sales force through its API. (Keep in mind there are many ways to access Salesforce data, here is only one of them)

To begin our work, we need to have the API. Log into the Salesforce platform,  go to the Setup page, on the left hand side, under the Build - Develop list view, you should see the API option.

Click this link, a list of WSDL options should be shown on the main panel. They are for different design purpose, as described on the page. What we need is the Enterprise WSDL, as it contains the most basic types we need in this example. Right click the link "Generate Enterprise WSDL", and save the file to your local folder.

Now before go any further, a quick notification about the WSDL. The downloaded WSDL can NOT be used in the project directly. If we add the WSDL as a web service reference into the project, as shown below

When you compile and run the project, you will get error message like below

In short it is because the ListViewRecord, this complex type defined in the WSDL does not contain a data type. It is fine for some languages, because the columns "ListViewRecordColumn" contains the data type definition. But from .Net, we have to explicitly identify the data type.

So we need to manually fix this problem: Open the downloaded WSDL file, locate the node "ListViewRecord" as shown below

Under the tag </sequence>, we need to declare its data type by adding

 <xsd:attribute name="tmp" type="xsd:string" />  

Then it should look like

Now the API definition part is done. We need to collect some other information before we go to SSIS part.

To access the Salesforce API, we need to provide our login email, password, and the token. To get the token we need to go to My Setting of the Salesforce platform, on the left hand side, expand options under Personal node, you should see the option "Reset My Security Token"

By click it, on the main panel of the page, you will see the warning message

Click the button "Reset Security Token", you will see the confirmation message like below, and a system email will shot into your associated email address.

Now we have all the information we need from Salesforce side. But before we go into SSIS, a quick tip may save your time: you may want to set password never expires under a Dev environment. Otherwise every time you change your password, you need to reset your security token.

Now time to look into SSIS. What I would like to do is to use a script component to get the User data from Salesforce.

Firstly drop a data flow task onto the control flow panel.

Within in the data flow task, drag and drop a script component, and select source on the pop up window.

Double click the script component, and go to "Inputs and Outputs" tab. As you can see, I created four output columns for the default output, FirstName, LastName, Title, and Email. They will be used to hold data retrieved from the User entity in Salesforce. For them, I defined their data type as string, length as 255. (You can also see I created another output "Output_Err". Because Salesforce's API provide the login result class, it can be used to show standard or customised error messages. But it will not be covered in this post, as it could be a huge topic)

Now to use the API, we need to add the downloaded, and modified WSDL as a web service into the script project.

Now turn off serialisation assembly option as shown below. Otherwise we will see "no binary code" error in the package.

At this stage, the project configuration is down. The only remaining job is coding.

Add the name space

Then I declared a SforceService to hold the actual service.

Coding is always simple from my understanding, once we know what we need to do

Then drag a Union All component under the Script component, and add a data viewer between these two components

Execute the task, the user records are exported from Salesforce

So now you have the chance to cleanse the Salesforce data, import into SQL Server, and do your own reporting/analysis task.


Codes within the CreateNewOutputRows method:

 public override void CreateNewOutputRows()  
   {  
     string email = "<login email>";  
     string password = "<login password>";  
     string token = "<security token>";  
     Service = new SforceService();  
     LoginResult LoginResult = Service.login(email, password + token);  
     string authurl = Service.Url;  
     Service.Url = LoginResult.serverUrl;  
     Service.SessionHeaderValue = new SessionHeader();  
     Service.SessionHeaderValue.sessionId = LoginResult.sessionId;  
     string sQuery = "SELECT FirstName, LastName, Title, Email FROM User";  
     QueryResult Qr = Service.query(sQuery);  
     if (Qr.size > 0)  
     {  
       foreach (sObject o in Qr.records)  
       {  
         if (o is User)  
         {  
           User u = (User)o;  
           Output0Buffer.AddRow();  
           Output0Buffer.FirstName = u.FirstName;  
           Output0Buffer.LastName = u.LastName;  
           Output0Buffer.Title = u.Title;  
           Output0Buffer.Email = u.Email;  
         }  
       }  
     }  
   }  

SSIS Design Pattern - Handling flat file with variable column numbers

In this post I want to show you how I deal with a flat file in which rows could have different number of columns.

Brief Backgound

When we are working on ETL design, I believe it is quite common that data is coming from various sources, such as SFTP, email attachment, online web portal, etc. Usually in this case I will design one package per data source, and use a parent package to handle the overall project.

However there might be cases client will do this task, I am saying, client extracts data from various sources and combine all into one single file. It sounds good, but sometimes it leads to trouble, for example, there probably will be no insurance about the data quality.

In one of my projects, I were asked to create a customer pool for marketing campaign purpose. Client extracted data from various sources, billing system, sales & marketing department, 3rd party data broker, and then generated one big flat file on a weekly basis.

The nightmare is data quality, some rows could have 4 columns, some rows could have 6 columns... So I wrote below package to solve this problem

Actual Design

Firstly lets look at what is the problem.

I have a SSIS package opened, and within the package I have a Flat File Connection Manager, points to a sample file.

Now I set columns as per normal.

And now I add a Data Flow Task, and within the Data Flow Task, I add some components as shown below

Quite straightforward, isn't it? Now I run it, and found an error:

Open the sample file within Excel, we can see number of columns is the actual reason for the error

While, there are plenty of ways to handle this type of issue:

• Ask client to fix the data problem, control the data quality, as everyone knows the best position to fix data quality is from data source, unfortunately, in my case, it is too perfect to be true. 
• Use some .Net code in script task to handle it. It works and should not be a problem.

Now here is another way to do it, basically I treat the file as one single column file, within the file read number of delimiters for each row, finally I use a Conditional Split Component to split the stream:

Firstly, Lets change the setting in the Flat File Connection Manager. Because we want the file to be treated as one single column file, I select "$$$" as the column delimiter.

Now to avoid truncate error, I change the column length to 8000.

Then the Data Flow Task layout looks like below. The Derived Column Component will read number of delimiters from the file, and Conditional Split Component will direct the stream by this newly created derived column.

In the Derived Columns Component, I write two expressions. The first one is to read number of tokens by using new function TOKENCOUNT from SSIS2012. If you are using SSIS2008, the 2nd expression will work for you. It reads number of delimiters.

SSIS 2012: TOKENCOUNT([Name|LogDate|Phone|OptIn],"|")
SSIS 2008: LEN([Name|LogDate|Phone|OptIn]) - LEN(REPLACE([Name|LogDate|Phone|OptIn],"|",""))

In the Conditional Split Component, I define outputs for correct output which can be loaded directly, dynamic output which can be loaded by a dynamic reader, and output needs to be reviewed manually

Now run the package again, it is what I need :D