API Response Pagination - SSIS

In my previous posts, I demonstrated how to paginate API response in Power Query. But what if we need to do a pagination via SSIS? As far as I know, at this stage there is no generic Rest connector available in SSIS. Therefore, we have to use script component/task to read response from the Rest API.

To show you the approach in a quick way, let's write some codes to replicate the scenario I demonstrated in this post. Firstly I created below function:

private static string callAPI(HttpClient client, string key, string endPoint)
    {
        string jsonBody;

        var header = new MediaTypeWithQualityHeaderValue("application/json");
        client.DefaultRequestHeaders.Accept.Add(header);

        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Key", key);

        HttpResponseMessage response = client.GetAsync(endPoint).Result;

        jsonBody = response.Content.ReadAsStringAsync().Result;

        return jsonBody;
    }

Clearly, this function will extract response body from the Rest API (here we assume the API uses HTTP header "Key" for authorization purpose).

So now we have the base method to call the API, what we need to do is creating a recursive routine, so that we can read the next page until nothing returns:

            bool tryNextPage = true;

            using (HttpClient client = new HttpClient())
            {
                while (tryNextPage)
                {
                    string jBody = callAPI(client, key, endPoint);

                    if (jBody.Length > 50)
                    {
                        ResponseBuffer.AddRow();
                        ResponseBuffer.JsonBody.AddBlobData(System.Text.Encoding.UTF8.GetBytes(jBody));

                        int page = int.Parse(endPoint.Substring(endPoint.Length - 1, 1));
                        
                        endPoint = endPoint.Substring(0, endPoint.Length - 1) + (page + incremental).ToString();
                    }
                    else
                    {
                        tryNextPage = false;
                    }
                    
                }

                
            }

As you can see, I defined a Boolean variable to control the While loop. And in each loop, I read length of the Json response to determine if we should read next page.

Simple approach, isn't it? Though it looks like we have to write more lines of code when compare to power query, SSIS does have its own advantages, such as data quality control. Until my next post, enjoy the pagination.

Power BI/Power Query: API Response Pagination Part 3 - Missing Indicator

This is the 3rd post about pagination in Power BI/Power Query.

In part 1 and part 2 we have discussed how to paginate Restful response if the response contains a total item Count or a next page Token. So what if the response only returns us records, without any indicator about the next page?

Let's take a look on Basecamp todo API. If we call the API at the end point /api/v1/projects/{project id}/todos.json?page=1, the begin of the response and the end of the response look like below screenshots:

begin of the response

end of the response

Sorry for the messy screenshots, but the point is, this API doesn't give us a token for next page. We have to query the next page to ensure our API call pulls all records we need. You might have hundreds of ways to do the job, but my first impression is let's create a recursive function. Let's think about the problem:

1. We receive the first page from the response and merge the records into a table "T";
2. Then we move on to the next page
2.1 If the next page contains records then we repeat step 1 (merge records into "T") and step 2
2.2 If there is no records in the next page, then we stop and return the table "T"

It is simply a classic recursive scenario. In Power Query, the recursive function is all about the "@". Let's look at the codes

let
    ufnQuery = (n) =>
        let
            jsonDoc= Json.Document(
                        Web.Contents(
                            "https://basecamp.com/{domain}/api/v1/projects/{project id}/todos.json?page=" & Number.ToText(n), 
                            [Headers=[Authorization="Basic {the key}"]]
                        )
        ),
        tmpTbl = Table.FromList(jsonDoc, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        tmpTbl,

    fnRecursive = (tbl, n) =>
        if Table.RowCount(ufnQuery(n)) > 0 then @fnRecursive(Table.Combine({tbl, ufnQuery(n)}), n+1) else tbl,

    tbl = ufnQuery(1),
    result = fnRecursive(tbl, 2)  
in
   result

As you can see, the key function in codes above is the 2nd function, in the function body, we check amount of records returned from the call, and if the amount is greater than 0, we recursively (@) call the function by passing the merged table and next page number. Simple enough, right?

Power BI/Power Query: API Response Pagination Part 2 - Paginate by Page Cursor

This is the 2nd post of the topic API Response Pagination in Power BI/Power Query. You can find the first post here.

In the first post we have seen an API pagination example in Power Query, in which the API response provides us with the total record count, then we can calculate amount of pages we need to query. So today let's see another typical scenario in API response: the response contains an cursor pointing to the next page. (Here the cursor could be a direct link brings you to the next page, or it could be a page token you need to apply to your next API call).

The example I demonstrated today is Youtube Search API, and the channel I am going to search is VicRoad (as this post is focusing on result pagination, I am not going to explain how to prepare the API call :P). Below is a well-formatted Json response returned from the call. As you can see, the key field (the cursor) we are going to use is "nextPageToken".

Similar to the last post, the first step is preparing a base call function, as shown below. However, this time we return a list, which contains the result set of the current call, as well as the nextPageToken field we can use for drilling calls.

Now here is the core function of today's topic: List.Generate(). Basically you can treat this function as the FOR loop: we initialize a variable, give it a stop condition, specify the incremental action to the variable, and finally populate the return list result. Below is what I wrote:

So what we did is quite clear now: we create a helper function to call the API; Inside the List.Generate() function, we call the function to repeatedly populate records on different pages by using nextPageToken value. So what we need to do is just turning the result to table? Hold on for one second!

Let's review this piece of codes. So initially we call the API without page token, and the return result set contains records from page 1 and token for page 2, hence the second call gives us records from page 2 and token for page 3...

Initial Token	Resultset	Next Token
""	page 1	page 2
page 2	page 2	page 3
page 3	page 3	page 4
page 4	page 4	page 5
...	...	...
last page	last page	null

Now you see the problem: our condition for generating the list is "for each NOT NULL nextPageToken". We haven't gone through the last page!

Fixing the problem is quite simple, we can just manually query the last page, and then combine the result sets:

Put all together:

let    
    ufnGetList = (pgToken) =>
        let 
            result = Json.Document(
                        Web.Contents("https://www.googleapis.com/youtube/v3/search?part=snippet&channelId=UCkT7-rjW_Foojt0ZRtbXMdA&maxResults=50&type=video&key=[put your api key here]&pageToken=" & pgToken)
                        ),
            nextPageToken = try result[nextPageToken] otherwise null,
            items = result[items],
            record = [items = items, nextPageToken = nextPageToken]
        in 
            record,

    resultSet = List.Generate(
                    () => ufnGetList(""),
                    each _[nextPageToken] <> null,
                    each ufnGetList(_[nextPageToken]),
                    each [nextPageToken = _[nextPageToken], items = _[items]]
                    ),

    lastPageToken = List.Last(Table.FromRecords(resultSet)[nextPageToken]),
    lastResultSet = ufnGetList(lastPageToken)[items],

    fullResultSet = Table.Combine({Table.FromRecords(List.Combine(Table.FromRecords(resultSet)[items])), Table.FromRecords(lastResultSet)})
    
    
in
    fullResultSet

Now Run the query we should have all the records we need.

Power BI/Power Query: API Response Pagination Part 1 - Paginate by Item Count

It has been a while since my last post, lots of things happened in these few month, changed my career path, obtained my certification... Finally I got a chance to sit down and restart the blog. So today's topic is pagination in REST API response.

As you can see in the past I had done a few posts of how to consume API results in SSIS via script components, such as this post. But in Power BI, it might not be as easy as what we did in the script component, because there is no direct way to do a loop when reading paginated response.

Generally there are two types of paginated result set:

-- Good: A Json response contains an indicator to provide you with the information of the next page, it could be a link for the next page, or it could be a token for you to issue the next call, or it could be a total item count so you need to do a calculation of how many pages you need to go through. This is today's topic.

-- Cursed: A Json result set does not have pagination information, so you need to query next page every time until the the response gives us nothing.

So, let's prepare today's post here. To demonstrate the logic, I am using the v3 SET (/api/v3/lego/sets) API from rebrickable. The API itself is quite simple, but you need to create an account, then generate a API key from your account profile to call the API.  It is a simple process so I won't cover it here.

After we obtain the API key, it is time to open the Power Query window in Power BI

From opened query window, select Web as the data source

In the pop up window, select Advanced option, and then input API endpoint and key value, as you can see below, I set page size to 100, and the initial page is 1.

Click the OK button and the query should run automatically and give you the result. As you can see the response is quite clear: it provides you total items in the result set, as well as the link you should use to visit the next page. In this example, I am going to use the "count" indicator to do the loop.

 

Now Power Query has built the basic routine for us. We need to manually modify the query
to return the result page by page. So firstly we open the query edit window by click Advanced Editor

Our query looks like screenshot captured below

In the query editor window, I transform the query into a function, nothing special, it simply accepts a page number and then return the Json response.

Here is the key part:

• We pull the total item count from the initial call 
• Then we generate a list for page numbers 
• For each list item, we call the ufnCallAPI function

And the result returned from the query:

(note: 100/page might be too small to issue the call, if it is too slow, you can try to change the page_size parameter to 1000 in the function definition, as well as the pageRange list boundary to {1..Number.RoundUp(totalItems/1000)} )


As you can see, though there is no loop function available in Power Query (at this stage), we can still retrieve paginated API responses by using provided paging indicator. In my next post, I will cover the topic of how to handle no indicator response in Power Query. Enjoy.

Power BI & Telstra Wifi Hotspot API

A while ago I wrote the post to show how to call Telstra Wi-Fi Hotspot API through C#. Today let’s see how easy we can achieve the same result (even better) by using Microsoft Power BI.


Power BI, in short, is an powerful BI tool for data extraction, transformation, and visualisation.  I would highly recommend it if you haven’t tried it yet.  At this stage you can sign it up for free, by applying a trial office 365 license.


Now back to the topic, today my plan is to call the API by using Power Query embedded in Power BI, and then display the result on the workspace. To do so let’s open Power BI Desktop, and press Edit Queries button on the ribbon.

On the Query Editor window (Power Query), open dropdown menu by press New Source button, then select Blank Query option.

Now click Advanced Editor button on the ribbon to open the editor window

Now Advanced Editor window pops up, and we can start to type in Power Query statements. To demonstrate how powerful the power query is, I decided to split the procedure into two: create a function to get oAuth token, and then call the Wi-Fi Hotspot API.


To create the function in Power Query, firstly let’s write below query (replace consumer key and consumer secret)

 let  
   authUrl = "https://api.telstra.com/v1/oauth/token",  
   authKey = "client_id={consumerkey}&client_secret={consumer secret}&grant_type=client_credentials&scope=WIFI",  
   getJson = Web.Contents  
       (authUrl,  
         [  
           Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],  
           Content = Text.ToBinary(authKey)  
         ]  
       ),  
   jsonDoc = Json.Document(getJson)  
 in  
   jsonDoc  

Click Done button and the oAuth result should be shown on Power Query window directly, as shown below

So far everything is good. What we need to do now is to introduce parameters into the query, and then parameterise the query. To do so firstly create new parameters

For demonstration purpose, I treat consumer key and consumer secret as parameters, as shown below

Now change our query a bit, I have highlighted changes in the screenshot attached below

Once you done everything and click Done button, the function window appears

Now you can test the function by entering consumer key and consumer secret then pressing the Invoke button.

The function should work as expected. Time to write the 2nd script. Following the same step to open a blank query window and input statements like below.

 let  
   accessToken = fnGetToken("{consumerkey}", "{consumersecret}")[access_token],  
   latitude = "-37.8103713989",  
   longitude = "144.99530273",  
   radius = "1000",  
   wifiUrl = Text.Format("https://api.telstra.com/v1/wifi/hotspots?lat=#{0}&long=#{1}&radius=#{2}", {latitude, longitude, radius}),  
   accessTokenHeader = "Bearer " & accessToken,  
   getWifiJson = Web.Contents(wifiUrl, [Headers = [#"Authorization" = accessTokenHeader]]),  
   wifiJson = Json.Document(getWifiJson),  
   table = Table.FromList(wifiJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
   wifiTable = Table.ExpandRecordColumn(table, "Column1", {"lat", "long", "address", "city", "state"}, {"lat", "long", "address", "city", "state"})  
 in  
      wifiTable   

Now things could be a little bit tricky. Depends on your Power BI desktop setting, you might get the result without any issue. But in most cases, you could see two different types of warnings: connection warning and privacy warning. In short it is because we are passing oAuth token from a different Url (https://api.telstra.com/v1/oauth/token vs https://api.telstra.com/v1/wifi/hotspots). Let's see how we can resolve these conflicts:


The first warning you might see is the connection warning.

Click the button “Edit Credential” will lead us to a popup window. Because we retrieve our token by passing consumer key and consumer secret into the function, we can simply select Anonymous method and then select the Url https://api.telstra.com/v1/oauth/token as the target.

The next window is about data privacy.

On the Privacy Level screen, select the Url https://api.telstra.com/v1/oauth/token, and then select “Public” as the scope.

After all these steps, the data table should be generated automatically

Now we can go back to the Power BI by clicking the button “Close & Apply” at the top left corner. Drag a map visual onto the workspace, drop Lat and Long columns onto the corresponding map fields, and that is it

Telstra WiFi API Quick Demonstration

Update on 16/06/2016: VS 2015 project for this post can be accessed on my personal Github repository.


It has been a while since I blogged the SMS API published by Telstra (you can find it here). After that post I spent quite a long time to blog other stuff. Recently I re-visited Telstra's Dev centre and found this interesting WiFi API. So let's try this API today.

Basically the API will accept query parameters of latitude, longitude, and a radius, then it will give us a list of available Telstra WiFi hotspots around the coordination.


The first step to use this API is to get the API key. Similar to the steps I described in my SMS post, it should be quite simple to get a new app created. Just be aware, the product to be ticked is WiFi API, as demonstrated below.

After the registration, expand the app pannel by pressing its name, you should see the consumer key and consumer secret are available now.

Time to look at C# implementation of the API.


Create a Winform project in Visual Studio, and I have a form created: 3 textboxes to accept latitude, longitude, and radius. A calculate button to call the WiFi API, and finally a list box to display all returned WiFi hotspots' addresses (keep in mind that, at this stage the API will return maximum 10 hot spots).

Before starting of the code task, I added two references into my project: one is System.Net.Http, which will be used to issue request and receive response; another one has been heavily used in my previous posts: Newtonsoft.Json. It will help us parse the json string returned from API call.


One thing we can reuse from previous SMS post is the authentication method: pass in consumer key and consumer secret, and then return a token and its expiry seconds:

     private async Task<TelstraToken> GetAccessToken(string consumerkey, string consumersecret)  
     {  
       TelstraToken Token = new TelstraToken();  
       string AccessUrl = @"https://api.telstra.com/v1/oauth/token";  
       HttpClient authClient = new HttpClient();  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>  
       {  
         {"client_id", consumerkey},  
         {"client_secret", consumersecret},  
         {"grant_type", "client_credentials"},  
         {"scope", "WIFI"}  
       });  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(AccessUrl),  
         Content = httpContent  
       };  
       try  
       {  
         var ResponseMessage = await authClient.SendAsync(Request);  
         var Response = await ResponseMessage.Content.ReadAsStringAsync();  
         if (ResponseMessage.IsSuccessStatusCode)  
         {  
           var AuthToken = JsonConvert.DeserializeObject<object>(Response);  
           JObject jObj = JObject.Parse(AuthToken.ToString());  
           Token.AccessToken = jObj["access_token"].ToString();  
           Token.ExpiredDt = DateTime.Now.AddSeconds(double.Parse(jObj["expires_in"].ToString()));  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return Token;  
     }  

You may question the TelstraToken object from the method above: It is just a simple object to host token value/expiry date time. Beside this simple object, I also created another helper class for Hotspot returned from API. Below are the definitions for these two classes:

     internal class TelstraToken  
     {  
       internal string AccessToken;  
       internal DateTime ExpiredDt;  
       internal TelstraToken() { }  
       internal TelstraToken(string _accessToken, DateTime _expiredDt)  
       {  
         this.AccessToken = _accessToken;  
         this.ExpiredDt = _expiredDt;  
       }        
     }  
     internal class TelstraWifiHotSpot  
     {  
       internal string latitude;  
       internal string longitude;  
       internal string address;  
       internal string city;  
       internal string state;  
       internal TelstraWifiHotSpot(string _latitude, string _longitude, string _address, string _city, string _state)  
       {  
         this.latitude = _latitude;  
         this.longitude = _longitude;  
         this.address = _address;  
         this.city = _city;  
         this.state = _state;  
       }  
       public override string ToString()  
       {  
         string fullAddress = string.IsNullOrWhiteSpace(address) ? string.Empty : address;  
         fullAddress += ", ";  
         fullAddress += string.IsNullOrWhiteSpace(city) ? string.Empty : city;  
         fullAddress += " ";  
         fullAddress += string.IsNullOrWhiteSpace(state) ? string.Empty : state;  
         return fullAddress;  
       }  
     }  

Now all preparations are done. The only missing part is the button click event:

     private async void btnCalculate_Click(object sender, EventArgs e)  
     {  
       List<TelstraWifiHotSpot> lst = new List<TelstraWifiHotSpot>();  
       if (_token == null || _token.ExpiredDt < DateTime.Now)  
       {  
         _token = await GetAccessToken("your consumer key", "your consumer secret");  
       }  
       string latitude = txtLatitude.Text;  
       string longitude = txtLongitude.Text;  
       string radius = txtRadius.Text;  
       string url = string.Format("https://api.telstra.com/v1/wifi/hotspots?lat={0}&long={1}&radius={2}", latitude, longitude, radius);  
       HttpClient client = new HttpClient();  
       client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _token.AccessToken);  
       HttpRequestMessage request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Get,  
         RequestUri = new Uri(url)  
       };  
       HttpResponseMessage responseMessage = await client.SendAsync(request);  
       var message = await responseMessage.Content.ReadAsStringAsync();  
       var jMsg = JsonConvert.DeserializeObject<object>(message);  
       foreach (var j in (JArray)jMsg)  
       {  
         string wifiLatitude = ((JObject)j)["lat"].ToString();  
         string wifiLongitude = ((JObject)j)["long"].ToString();  
         string wifiAddress = ((JObject)j)["address"].ToString();  
         string wifiCity = ((JObject)j)["city"].ToString();  
         string wifiState = ((JObject)j)["state"].ToString();  
         TelstraWifiHotSpot hotspot = new TelstraWifiHotSpot(wifiLatitude, wifiLongitude, wifiAddress, wifiCity, wifiState);  
         lst.Add(hotspot);  
       }  
       if (lst.Count > 0)  
       {  
         lstHotspots.DataSource = lst;  
         lstHotspots.DisplayMember = lst.ToString();  
       }  
     }  

After we put everything together and run the project, you should be able to find the hotspots Telstra provided:

Salesforce - Retrieve Salesforce Entity Data by REST API

In my previous post I have shown how to retrieve Salesforce entity data through SOAP API. The Salesforce platform can also be accessed through REST API.

To get the Salesforce REST API works, the first thing we need to do is to create an app in Salesforce. Go to Setup page, on your left hand side, expand Create node under the Build section, then click Apps link.

Now on the main panel, there is a section named "Connected Apps", click the New button next to it.

On the next screen, firstly we need to fill in some basic information, as shown below

Then go to the API section, tick the checkbox "Enable OAuth Settings". Immediately there will be OAuth settings shown on the screen. To demonstrate the REST API, here I just make the callback URL as a test URL, and select full access as the authentication scope. In a real production environment, the access scope should be limited to specific account/group level.

Now once we save it, we should see the confirmation message as below

The activation of the app should not take too long, but meanwhile, we need to collect some information for the API coding task. Open the App and you should see the App details page like below. The consumer key and consumer secret are what we need for our coding task

Time to look at the coding side. In my Simpro post (part2) and Telstra post (here) I used webclient to post the message. Actually we can also use HttpClient to do the same task.

Assume what I want to do is to get a list of leads in Salesforce. The final output of the work looks like below.

Because we are going to use HttpClient, we need to add System.Net.Http into our reference list of the C# project.

Now the remaining coding is just like other REST API, obtain the access token, and submit the request, then receive the response. One thing could be important to you, is the version of your Salesforce when you build the query string. In a normal scenario, the version should not affect your query string. But it is always a good practise to check the version/document.

Code to access lead entity is shown below

     private async void btnLogin_Click(object sender, EventArgs e)  
     {  
       HttpClient authClient = new HttpClient();  
       string ConsumerKey = "<ConsumerKey>";  
       string ConsumerSecret = "<ConsumerSecret>";  
       string Url = @"https://login.salesforce.com/services/oauth2/token";  
       string username = "<username>";  
       string password = "<password>";  
       string token = "<token>";  
       string loginpassword = password + token;  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>  
         {  
           {"grant_type", "password"},  
           {"client_id", ConsumerKey},  
           {"client_secret", ConsumerSecret},  
           {"username", username},  
           {"password",loginpassword}  
         }  
       );  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(Url),  
         Content = httpContent  
       };  
       var responsemessage = await authClient.SendAsync(Request);  
       var response = await responsemessage.Content.ReadAsStringAsync();  
       if (responsemessage.IsSuccessStatusCode)  
       {  
         var authToken = JsonConvert.DeserializeObject<object>(response);  
         string instance_url = ((JObject)authToken)["instance_url"].ToString();  
         string oauToken = ((JObject)authToken)["access_token"].ToString();  
         string sfQuery = instance_url + "/services/data/v34.0/query?q=SELECT+name+from+Lead";  
         HttpRequestMessage QueryRequest = new HttpRequestMessage(HttpMethod.Get, sfQuery);  
         QueryRequest.Headers.Add("Authorization", "Bearer " + oauToken);  
         var QueryResponse = await authClient.SendAsync(QueryRequest);  
         string QueryResponseResult = await QueryResponse.Content.ReadAsStringAsync();  
         JObject sfObject = JObject.Parse(QueryResponseResult);  
         JArray sfArray = (JArray)sfObject["records"];  
         foreach (JObject o in sfArray)  
         {  
           listBox1.Items.Add(o["Name"].ToString());  
         }  
       }  
       else  
       {  
         MessageBox.Show(response.ToString());  
       }  
     }  

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;  
         }  
       }  
     }  
   }