Retrieving Topic Profile list from Radian6 API

The first step in Radiant6 is to get topic profiles from API. To access the API, we need three important 
credentials those are UserName(auth_user), Password(auth_pass) and ApplicationKey(auth_appkey). We can get the appkey from the following site by providing the username and password. 

First, get the username and password for the "https://login.radian6.com/" site. Use this username and password as auth_user and auth_pass. Then get the username and password for the "https://secure.mashery.com/login/socialcloud.radian6.com/" site where in you can get the application key and use that as an auth_appkey in the authorisation part.

//When you have demo login credentials then use the demo site: https://demo-login.radian6.com/

Below is the steps to get topic profiles:

Create variables for the auth_user, auth_pass and auth_appkey as shown below

now create Script Task and provide the parameters in the ReadOnlyVariables as shown below
and click EditScript and applied the code as given below:

------------------------------------------
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//using System.Web.UI;
//using System.Web.UI.WebControls;
using System.Net;
//using System.Web.Script.Serialization;
using System.Text.RegularExpressions;
#endregion

namespace ST_1adf35b5c0614ec086d2bffb896ab7c3
{
    /// <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
{
       // string auth_user = Dts.Variables["CustomerCount"].Value;
        #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>
public void Main()
{
            string token = "";
            string Json = Call_Rest(token);
            //Response.Write(Json);
            token = Regex.Match(Json, @"\<token\b[^>]*\>\s*(?<token>[\s\S]*?)\</token\>", RegexOptions.IgnoreCase).Groups["token"].Value;
            // Response.Write(token);
            Json = Call_Rest(token);
            string topic = Call_RestFectchData(token);
            Dts.TaskResult = (int)ScriptResults.Success;
}
        public string auth_user
        {
            get
            {
                return Dts.Variables["User::auth_user"].Value.ToString();
            }
        }
        public string auth_pass
        {
            get
            {
                return Dts.Variables["User::auth_pass"].Value.ToString();
            }
        }
        public string auth_appkey
        {
            get
            {
                return Dts.Variables["User::auth_appkey"].Value.ToString();
            }
        }
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/topics?includeReactivation=true";
            using (WebClient client = new WebClient())
            {

                if (token == null || token.Trim() == "")
                {
                }
                else
                {

                    client.Headers.Add("auth_token", token);
                    client.Headers.Add("auth_appkey", auth_appkey);

                }

                try
                {
                    responseXml = client.DownloadString(Url);

                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }
            System.IO.File.WriteAllText(@"M:\SSIS\rad\TopicProfile.xml", responseXml);
//here the TopicProfile xml file will be generated in the provided path
            return responseXml; 

            
        }
        string Call_Rest(string token)
        {
            //REST call:
            string auth_token = token;
            string responseXml = "";
            string url = "https://api.radian6.com/socialcloud/v1/auth/authenticate";


            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3;
            using (WebClient client = new WebClient())
            {
                //add HTTP headers (for auth)
                client.Headers.Add("auth_appkey", auth_appkey);
                if (auth_token == null || auth_token.Trim() == "")
                {
                    client.Headers.Add("auth_user", auth_user);
                    client.Headers.Add("auth_pass", auth_pass);

                }
                else
                {
                    url = "https://api.radian6.com/socialcloud/v1/topics";

                    client.Headers.Add("auth_token", auth_token);
                }

                //send web request; get web response as XML
                try
                {
                    responseXml = client.DownloadString(url);

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

                }
            }

            return responseXml;
        }

        #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 get into DFT and create XML Source task. In XML Source Editor, select the Data access mode as XML file location and brows the xml file location. Then click Generate XSD button to create XSD and provide the path to save it. Map the requried columns with the corresponding table.