Retrieving Ooyala Engagement data from API

To retrieve Engagement data, we need to provide an ID which will be available from EmbedCode_Property table as text column. 

The following figure will illustrate the process flow of retrieving the Ooyala Engagement data from OoyalaAPI:

Select all the distinct Ids in SQL Task and have it in a result set. Create variable for it 
as an object.

Create For each loop container to have Script Task and DFT. In Script task provide the User:apikey and 
User:secretkey parameters in the ReadOnlyVariables and click the Edit Script button.

Provide the below mentioned code which will retrieve data using the referrence support of Json and OoyalaAPI cs files.

Here we need to add "Newtonsoft.Json.Net20.dll". Download the Json referrence file from the web and save it  in the corresponding foler. 

Go to Solution Explorer - Right Click Reference - Select Add Reference - Browse the Json file and add it.
Also add the JSON.cs and OoyalaAPI.cs files.

------------------------------------------------------------------

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
//using Newtonsoft.Json;
using System.IO;
using System.Security.Cryptography;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Collections;
using System.Web;
using System.Xml;
using System.Xml.Linq;
using Newtonsoft.Json.Bson;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Schema;
using Newtonsoft.Json.Serialization;
using Newtonsoft.Json.Utilities;
//using System.Xml;

using System.Runtime.Serialization.Json;
using Newtonsoft.Json;
#endregion

namespace ST_e00b761ae09948d5aef4ab0b0a0e6a78
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
        #region Help:  Using Integration Services variables and parameters in a script
        ..........
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        
        XmlDocument test1;
public void Main()
{
           
Call_RestFectchData();
Dts.TaskResult = (int)ScriptResults.Success;
           
}
        string Call_RestFectchData()
        {
            string responseXml = string.Empty;
            

            try
            {
                string api_key, secret_key, ContentKey;
                DateTime PlayDate;
                api_key = (string)Dts.Variables["User::apiKey"].Value;
                secret_key = (string)Dts.Variables["User::secretKey"].Value;
                ContentKey = (string)Dts.Variables["User::ContentKey"].Value;

                PlayDate = (DateTime)Dts.Variables["User::PlayDate"].Value;
                var date = PlayDate.ToString("yyyy-MM-dd");

                OoyalaAPI api = new OoyalaAPI(api_key,secret_key);

                Dictionary<String, String> parameters = new Dictionary<String, String>();
               
                Hashtable values = api.getHashtable("analytics/reports/asset/" + ContentKey + "/engagement/total/" + date ,parameters);

                var s2 = JsonConvert.SerializeObject(new { items = values });

                test1 =JsonToXML(s2);
                System.IO.File.WriteAllText(@"M:\ooyala\PlayHistory\Ooyala_PlayHistory.xml", test1.InnerXml);
                
              
            }
            catch (Exception exc)
            {
                responseXml = "ERROR: " + exc.Message;
            }

            return responseXml;
        }
        
        public XmlDocument JsonToXML(string json)
        {
            XmlDocument doc = new XmlDocument();

            using (var reader = JsonReaderWriterFactory.CreateJsonReader(Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max))
            {
                XElement xml = XElement.Load(reader);
                doc.LoadXml(xml.ToString());
            }

            return doc;
        }
        }
    
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}

-------------------------------------------------------------
Now create DFT Task and add XML Source task and select the source files and generate the XSD file. From this XML source, we can map the columns to the corresponding tables.