Retrieving Ooyala Content (Assets) data from API

Ooyala is a venture-backed, privately held company that provides online video technology products and services. Ooyala provides powerful and easy-to-use RESTful APIs that enable you to programmatically access Backlot, whether you want to upload videos, manage your assets, or completely integrate your content management system.

To start work with Ooyala backlot API, we need username and password to log in http://www.ooyala.com/backlot/web in which we can get the API_Key and Secret_Key. These keys are used to authenticate the programming code to retrive data.

We can also view the data using the following URL by providing the API_Key and Secret_Key.
https://api.ooyala.com/docs/api_scratchpad?url=&method=GET&api_key=

Below is the Developer documentation URL:
http://support.ooyala.com/developers/documentation

The following figures will illustrate the process flow of retrieving the Ooyala Content data from OoyalaAPI:

create variables as shown below:

create ScriptTask and 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
{
       ..........
         * */
        #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>
        public ArrayList asstesParam;
public void Main()
{
Call_RestFectchData();
Dts.TaskResult = (int)ScriptResults.Success;
}
        string Call_RestFectchData()
        {
            string responseXml = string.Empty;
            

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

               // OoyalaAPI api = new OoyalaAPI("ZzZGMxOo_dNZEF6BH2MfXVqweXLU.C0AdU", "2_XzbDXJwNTKBRW3yLPu_bxtr-CxrBEZBHedKrrE");
                OoyalaAPI api = new OoyalaAPI(api_key,secret_key);

                Dictionary<String, String> parameters = new Dictionary<String, String>();
                parameters.Add("include", "metadata,labels");
                parameters.Add("limit", "500");


                Hashtable values = api.getHashtable("assets", parameters);


                asstesParam = (ArrayList)api.getHashtable("assets", parameters)["items"];
                getpage_token(values);

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


                XmlDocument test1 = JsonToXML(s2);
                System.IO.File.WriteAllText(@"M:/Ooyala/Ooyala_Asset_Unconverted.xml", test1.InnerXml);




            }
            catch (Exception exc)
            {
                responseXml = "ERROR: " + exc.Message;
            }

            return responseXml;
        }
        public string getpage_token(Hashtable values)
        {
            string page_token = string.Empty;
            foreach (DictionaryEntry entry in values)
            {
                if (entry.Key.ToString() == "next_page")
                {
                    Dictionary<String, String> parameters = new Dictionary<String, String>();
                    OoyalaAPI api = new OoyalaAPI("ZzZGMxOo_dNZEF6BH2MfXVqweXLU.C0AdU", "2_XzbDXJwNTKBRW3yLPu_bxtr-CxrBEZBHedKrrE");
                    page_token = entry.Value.ToString();
                    string splitvariable = "page_token=";
                    string[] s = page_token.Split(new[] { splitvariable }, StringSplitOptions.None);
                    page_token = s[1];
                    parameters.Add("include", "metadata,labels");
                    parameters.Add("limit", "500");
                    parameters.Add("next_page", page_token);
                    parameters.Add("page_token", page_token);
                    Hashtable valuesnextpage = api.getHashtable("assets", parameters);
                    ArrayList asstesParam1 = (ArrayList)api.getHashtable("assets", parameters)["items"];
                    foreach (var item in asstesParam1)
                    {
                        asstesParam.Add(item);
                    }
                    string assets = api.get("assets", parameters);

                    return getpage_token(valuesnextpage);
                }
            }
            return page_token;
        }
        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 add XML Task to convert the output file in to readable file as shown below:

Create DFT Task and add XML Source task and select the source files and generate the XSD file as shown below:

From this XML source, we can map the columns to the corresponding tables.