StackOverflow August 2016 Contributions

Below are links to questions I have contributed to on StackOverflow.  If you have time or any of the questions interest you take a moment to review the question and answers.  If you feel one of the answers appropriately answer the question, then please vote that answer up to better serve the community and future users looking for an answer to a similar question.

Iterate Over the Results of an Azure SQL Stored Procedure in an Azure Logic App

Introduction

If you need to quickly create a business workflow or other B2B integration, look no further then Azure Logic Apps. Azure Logic Apps is a cloud based workflow and integration service. With the multitude of built in connectors you can easily automate many common business workflows. The Azure SQL Database connector and execute stored procedure action were of interest to me when I needed to implement an automated email notification. The output of a stored procedure from a source system would provide me a list of users who would receive reminder emails. Azure Logic Apps provides the capability of iterating over results of an action, however iterating over the results of a stored procedure is not as straight forward as some of the other examples. In this post I am going to use the automating email notifications example to illustrate what I needed to do to iterate over the results of a stored procedure. Continue reading “Iterate Over the Results of an Azure SQL Stored Procedure in an Azure Logic App”

StackOverflow July 2016 Contributions

Below are links to questions I have contributed to on StackOverflow.  If you have time or any of the questions interest you take a moment to review the question and answers.  If you feel one of the answers appropriately answer the question, then please vote that answer up to better serve the community and future users looking for an answer to a similar question.

StackOverflow June 2016 Contributions

Below are links to questions I have contributed to on StackOverflow.  If you have time or any of the questions interest you take a moment to review the question and answers.  If you feel one of the answers appropriately answer the question, then please vote that answer up to better serve the community and future users looking for an answer to a similar question.

Running an Azure Data Factory Pipeline on a Weekday Schedule Using an Azure Function

Introduction

I have written a few posts about different aspects of Azure Data Factory.  I use it as the main workhorse of my data integration and ETL projects.  One major drawback I have found with Azure Data Factory is the scheduling system, it’s not as flexible as I and many others would like it to be.  With that being said there are certainly ways to adapt and get more control of an Azure Data Factory pipeline execution.  In my post Starting an Azure Data Factory Pipeline from C# .Net, I outline the need to kick off a pipeline after a local job has completed and how this can be attained by utilizing the SDK to programmatically set the pipelines Start/End dates.  You may not have that requirement specifically, but let’s say you want to only run a pipeline during the weekday or another specific schedule, this can be accomplished by utilizing the same code from my prior post and scheduling a local console app.  However, I thought it would be more fun to utilize Azure Functions to kick off a pipeline on a weekday schedule to provide a fully cloud based solution. Continue reading “Running an Azure Data Factory Pipeline on a Weekday Schedule Using an Azure Function”

Run Oracle RightNow Analytics Report with C#

Introduction

In a previous post Get All Users from Oracle RightNow SOAP Api with C#, I gave a simple example of how to get object data using the QueryCSV method of the api.  There is another helpful method available on the Oracle RightNow Api which allows you to run an Analytics Report and receive a csv result set.  By having the ability to execute these reports from the api provides you the opportunity to structure the data in a form that directly meets your needs.

Prerequisites

  • You will need access to a Oracle RightNow instance
  • Oracle RightNow report defined
  • Visual Studio. Visual Studio Community 2015
  • Create a new console application
  • Add service reference to RightNow (follow previous post here)

Visual Studio

In order to run the following sample method, you will need to have a service reference defined for the Oracle RightNow system you would like to access.  I briefly went through this in the previous post so please follow up there on how to accomplish that.

        private static void run_report(int analyticsReportId)
        {
            var analyticsReport = new AnalyticsReport();
            var reportID = new ID
            {
                id = analyticsReportId,
                idSpecified = true
            };
            analyticsReport.ID = reportID;

            //analyticsReport.Filters = new[]
            //{
            //    new AnalyticsReportFilter
            //    {
            //        Name = "UpdatedRange",
            //        Operator = new NamedID {ID = new ID {id = 9, idSpecified = true}},
            //        Values =
            //            new[]
            //            {
            //                $"{new DateTime(2016, 01, 20, 00, 00, 00).ToString("s")}Z",
            //                $"{new DateTime(2016, 01, 20, 23, 59, 59).ToString("s")}Z"
            //            }
            //    }
            //};

            byte[] fileData;

            var _client = new RightNowSyncPortClient();
            _client.ClientCredentials.UserName.UserName = "";
            _client.ClientCredentials.UserName.Password = "";

            var clientInfoHeader = new ClientInfoHeader { AppID = "Download Analytics Report data" };
            var tableSet = new CSVTableSet();

            try
            {
                tableSet = _client.RunAnalyticsReport(clientInfoHeader, analyticsReport, 10000, 0, ",", false, true, out fileData);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw;
            }
            var tableResults = tableSet.CSVTables;

            var data = tableResults[0].Rows.ToArray();
        }

As you can see it is pretty simple to execute the analytics report on the RightNow api.  When calling the QueryCSV function you need to define a SOQL query, here we need to populate the AnalyticsReport object to be executed.  Reports that require filters need to populate the filter array on the AnalyticsReport object, you can see an example of a date range filter commented out in the method above.  Finally, the returned values from the report utilize the same result objects returned from the QueryCSV method.

Conclusion

For my current integration projects, I have the need to pull data from various api’s, having the ability to pull data already structured helped to speed up the process. Hopefully seeing this sample and knowing that this ability exists will provide another option for gathering data from the Oracle RightNow api.

Azure Data Factory Copy Activity Storage Failure Error

I was recently tasked with migrating local integration jobs into the cloud.  The first job I began to tackle was an integration job between Salesforce and a local financial system.  The integration workflow is to first download the newest entries of a specific object from salesforce, then to push that into an on premise sql server staging table.  Once the data is copied locally another job within the financial system will process the staged data. 

In order to accomplish this task, I decided to use Azure Data Factory, with the on premise gateway to connect to the local sql database.  If you have seen some of my other posts I have used Azure Data Lake Store to land my data, with this pipeline I decided to use Azure Blob Storage.  Since the Azure Blob Storage api has the ability to store Append Blobs, I was able to follow a similar pattern as I followed using the Azure Data Lake Store and append the Salesforce data to a blob as I fetched the data.

Once I had some sample Append Blobs in my container my next step was to setup the Azure Data Factory copy activity to get that data transferred to the on premise sql server staging tables.  This was where I began to run into issues.  After a lot of verification and testing, it turns out Append Blobs are not supported in Azure Data Factory.

Here are a few things to look out for to rule out this issue:

  • In the blob container blade, it will show the BlobType, check the type of the blobs you are trying to work with in Azure Data Factory.clip_image001
  • I also ran into an issue where the data set which was pointing to the AppendBlob would not validate.
  • When running the Azure Data Factory copy activity against an Append Blob you will see the following error:
    Copy activity met storage operation failure at ‘Source’ side. Error message from storage execution: Requested value ‘AppendBlob’ was not found.
    This can be a bit misleading if you are not aware AppendBlobs are not supported.

StackOverflow May 2016 Contributions

Below are links to questions I have contributed to on StackOverflow.  If you have time or any of the questions interest you take a moment to review the question and answers.  If you feel one of the answers appropriately answer the question, then please vote that answer up to better serve the community and future users looking for an answer to a similar question.

Accessing the Office 365 Reporting Service using C#

Introduction

As with other cloud offerings from Microsoft, there is so much data and meta data being collected there is often a need for reporting on that information.  Within Office 365 administration portal you can see the various reports that are offered, however it may be necessary to pull that data into your own application or reporting tool.  Thankfully you can access that underlying data through Powershell commandlets or by using the Office 365 Reporting service.  This post will focus on a few simple example methods I used to explore the Office 365 Reporting service using C#.

Prerequisites

Visual Studio

I have uploaded the full source to github so I will not be posting the full example methods in this post, if you would like to download the full source you can get it here.  Each method has local variables for your Office 365 administrator account username and password.  One of the example methods I would like to highlight is the run_all_reports method.  This was probably the most helpful method when exploring the different reports.  This allowed me to quickly loop through each report available and dump the first set of results to an xml file.  I could then inspect each report result to see what data was available and which report data I needed to pull into my own application.

public void run_all_reports()
{
    var username = "";
    var password = "";

    foreach (var report in Reports.ReportList)
    {
        var ub = new UriBuilder("https", "reports.office365.com");
        ub.Path = string.Format("ecp/reportingwebservice/reporting.svc/{0}", report);
        var fullRestURL = Uri.EscapeUriString(ub.Uri.ToString());
        var request = (HttpWebRequest)WebRequest.Create(fullRestURL);
        request.Credentials = new NetworkCredential(username, password);

        try
        {
            var response = (HttpWebResponse)request.GetResponse();
            var encode = System.Text.Encoding.GetEncoding("utf-8");
            var readStream = new StreamReader(response.GetResponseStream(), encode);
            var doc = new XmlDocument();
            doc.LoadXml(readStream.ReadToEnd());

            doc.Save($@"C:\Office365\Reports\{DateTime.Now:yyyyMMdd}_{report}.xml");

            Console.WriteLine("Saved: {0}", report);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The get_report_list method simple hits the service endpoint and grabs the definition of all the available reports in the api.  Note that depending on your permission level you may not see all the reports available.  Finally, the run_report_messagetrace method is an example of pulling one report and mapping it back to an object using the HttpClient and Newtonsoft Json libraries.

Conclusion

With any cloud offering today there will generally be some reporting capabilities built in, however these reports are usually not enough.  Luckily more and more services are exposing their source data through api’s.  The Office 365 reporting service is just one example of that and from the sample methods above you can see how quickly it is to get access to this data.

Get All Users from Oracle RightNow SOAP Api with C#

Introduction

This post will be another example from my various system integration work.  Like in a previous post about getting users from the jira api, here I will give a simple example of utilizing the RightNow SOAP Api to get a list of all users.  RightNow does have a REST Api, however I have no control over the instance I must integrate with and unfortunately it is disabled.  It has certainly been a bit challenging extracting data from the RightNow Api, hopefully this example will be a simple jumping off point to help you explore the other objects in the api.  Here is a link to the documentation I have used when accessing the RightNow SOAP Api.

Prerequisites

Visual Studio

Once you have a new console app created, the first thing you will want to do since this is a SOAP Api, is right click on references and Add Service Reference (Gasp! Can’t remember the last time I did that).

SvcReference

Enter the address of the Oracle RightNow instance you need to connect to, replacing <XXX> in the url https://<XXX>.custhelp.com/cgi-bin/<XXX>.cfg/services/soap?wsdl.

Now that we have the service reference we can begin making queries against the SOAP API.  There are methods available to fetch data from the api, the object query or tabular query.  In this example I could have used the object query because it is a simple query, however I always use the tabular query (CSV Query) because the tabular query allows for more complicated ROQL queries.  In general, you will most likely be writing more complicated queries to gather data anyway.

public static void GetUsers()
{
	var _client = new RightNowSyncPortClient();

	_client.ClientCredentials.UserName.UserName = "";
	_client.ClientCredentials.UserName.Password = "";

	ClientInfoHeader clientInfoHeader = new ClientInfoHeader();
	clientInfoHeader.AppID = "CSVUserQuery";

	var queryString = @"SELECT
							Account.ID,
							Account.LookupName,
							Account.CreatedTime,
							Account.UpdatedTime,
							Account.Country,
							Account.Country.Name,                                    
							Account.DisplayName,
							Account.Manager,
							Account.Manager.Name,
							Account.Name.First,
							Account.Name.Last                                    
						 FROM Account;";


	try
	{
		byte[] csvTables;

		CSVTableSet queryCSV = _client.QueryCSV(clientInfoHeader, queryString, 10000, ",", false, true, out csvTables);

		var dataList = new List();
		foreach (CSVTable table in queryCSV.CSVTables)
		{
			System.Console.WriteLine("Name: " + table.Name);
			System.Console.WriteLine("Columns: " + table.Columns);
			String[] rowData = table.Rows;

			foreach (String data in rowData)
			{
				dataList.Add(data);
				System.Console.WriteLine("Row Data: " + data);
			}
		}


		//File.WriteAllLines(@"C:\Accounts.csv", dataList.ToArray());


		Console.ReadLine();
	}
	catch (FaultException ex)
	{
		Console.WriteLine(ex.Code);
		Console.WriteLine(ex.Message);
	}
	catch (SoapException ex)
	{
		Console.WriteLine(ex.Code);
		Console.WriteLine(ex.Message);
	}
}

In the RightNow API the Account object represents the user in the system.  The first step here is to setup the soap service client, by passing in the credentials in order to authenticate with the soap service.  Once the client is configured to make the call you pass in a query for the object you would like to get back in CSV format, in this case the Account object.  Once the result comes back you can iterate through the rows within the table.

Also note that the result is a CSVTableSet this is important because you can define a query with multiple statements, this will return a result table for each statement.

An example might be something like this:

            var queryString = @"SELECT
                                    Account.ID,
                                    Account.LookupName,
                                    Account.CreatedTime,
                                    Account.UpdatedTime,
                                    Account.Country,
                                    Account.Country.Name,                                    
                                    Account.DisplayName,
                                    Account.Manager,
                                    Account.Manager.Name,
                                    Account.Name.First,
                                    Account.Name.Last                                    
                                 FROM Account;
                                SELECT 
                                    Account.ID, 
                                    Emails.EmailList.Address,
                                    Emails.EmailList.AddressType,
                                    Emails.EmailList.AddressType.Name,
                                    Emails.EmailList.Certificate,
                                    Emails.EmailList.Invalid                                    
                                 FROM Account;
                                SELECT 
                                    Account.ID, 
                                    Phones.PhoneList.Number,
                                    Phones.PhoneList.PhoneType,
                                    Phones.PhoneList.PhoneType.Name,
                                    Phones.PhoneList.RawNumber                                                                      
                                 FROM Account;";

Conclusion

Getting the users from the Oracle RightNow api is a simple example to get up and running, while also getting some exposure to ROQL and the api.