Retrieving Blog Post data from Radian6 API

After retrieved the Topic profile related tables, we can retrieve the blog related tables using the ID column
from the "Article" table.

Below is the step to retrieve Blog Post related data:

Create variables as shown below:

Create SQL task the get all IDs from Article table which will be passed along with Topic Profile Id 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::PostIdResultSet.

SELECT CAST(ID AS VARCHAR(20)), TOPICPROFILEID FROM RAD.ARTICLE
WHERE media_provider <> 'TWITTER'

//here we need separate authentication to access Twitter account hence we don't use Twitter account ids.

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 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.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 PostIds
        {
            get
            {
                return Dts.Variables["User::PostIds"].Value.ToString();
            }
        }
        public string TopicProfileId
        {
            get
            {
                return Dts.Variables["User::TopicProfileId"].Value.ToString();
            }
        }
        
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/post/workflow/" + PostIds + "/" + TopicProfileId + "";
            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\Evangelism\Blogdetails.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.