Retrieving all Topic Profile data from Radian6 API

Once we get the Topic Profile detais, we can retrive all the tables related to the particular topicfilterid.

Below is the step to retrive topic related data

Create variables as shown below:

Create SQL task the get all Topicprofile ids which will be passed along with pages to get data.

apply the following script in SQLStatement area in SQL Task Editor and go to Result Set to get the 
Variable name as User::TopicProfileId.

 WITH cte AS(SELECT topicFilterId
, 1 AS PageNo
 FROM rad.TopicProfile
 UNION 
 SELECT topicFilterId
, 2 AS PageNo
 FROM rad.TopicProfile
 UNION 
 SELECT topicFilterId
, 3 AS PageNo
 FROM rad.TopicProfile)

 SELECT topicFilterId, PageNo FROM cte


Create For Each Loop Container as shown below

now create Script Task and provide the auth_user, auth_pass and auth_appkey in ReadOnlyVariables
and click EditScript and apply the following script.

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

#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();
            }
        }
        public string topicprofile
        {
            get
            {
                return Dts.Variables["User::rowid"].Value.ToString();
            }
        }
        public string Pages
        {
            get
            {
                return Dts.Variables["User::Pages"].Value.ToString();
            }
        }
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/data/topicdata/realtime/24/"+topicprofile+"/1,2,3,4,5,6,7,8,10,9,11,12,13,14,16,15/"+ Pages +"/1000?includeWorkflow=1&includeSpam=0&merged=1&token=" + token + "0&extendedMediaTypes=2,3,4";
            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\profiles\topicProfile_"+topicprofile+"_"+ Pages +".xml", responseXml);
            System.IO.File.WriteAllText(@"M:\SSIS\rad\profiles\topicProfile.xml", responseXml);
            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

}
}

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

Get in to DFT, Using XML Source, select the XML file and Generate XSD. From that source, you can map to all the required tables as shown below: