tim laqua dot com Thoughts and Code from Tim Laqua

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;
using System.Net;
using System.IO;
using System.Runtime.Serialization.Json;
using System.Runtime.Serialization;
using System.Text.RegularExpressions;
 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        // loginURL should be a url that returns JSON containing a sessionID
        string loginURL = Variables.loginURL;
 
        // Get the sessionID
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(loginURL);
        request.Timeout = 300000;
        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        StreamReader reader = new StreamReader(response.GetResponseStream());
        string json = reader.ReadToEnd();
        Match m = Regex.Match(json, "\"sessionID\": \"(.+?)\"");
        string sessionId = m.Groups[1].Value;
 
        // Need to support pagination, api has a 2000 item limit per req
        XmlNodeList items;
        int rowsPerPage = 1000;
        int page = 0;
 
        // Loop until we find the last page
        do {
            // The taskURL has 3 tokens:
            // {0} = The first record to return for the page we're requesting
            // {1} = The number of records to request
            // {2} = The sessionID
            string taskURL = string.Format(
                Variables.taskURL, 
                page * rowsPerPage + 1, 
                rowsPerPage, 
                sessionId);
            request = (HttpWebRequest)WebRequest.Create(taskURL);
            response = (HttpWebResponse)request.GetResponse();
 
            // Toss the JSON response in to XML so we can work with it better
            XmlDocument xd = new XmlDocument();
            XmlDictionaryReader xr = JsonReaderWriterFactory.CreateJsonReader(
                response.GetResponseStream(), 
                XmlDictionaryReaderQuotas.Max);
            xr.Read();
            xd.LoadXml(xr.ReadOuterXml());
 
            // Put each item returned in to a new Buffer row
            items = xd.DocumentElement.SelectNodes("/root/data/item");
            foreach (XmlNode item in items)
            {
                TaskDataBuffer.AddRow();
                TaskDataBuffer.ProjectId = 
                    item.SelectSingleNode("project/ID").Value;
                TaskDataBuffer.ProjectName = 
                    item.SelectSingleNode("project/name").Value;
 
                // This particular feed allows assignedTo to be empty/null
                if (item.SelectSingleNode("assignedTo/name") != null)
                {
                    TaskDataBuffer.TaskAssignedToName = 
                        item.SelectSingleNode("assignedTo/name").Value;
                }
                else
                {
                    TaskDataBuffer.TaskAssignedToName_IsNull = true;
                }
 
                TaskDataBuffer.TaskId = 
                    item.SelectSingleNode("ID").Value;
                TaskDataBuffer.TaskName = 
                    item.SelectSingleNode("name").Value;
                TaskDataBuffer.TaskPlannedCompletionDate = 
                    item.SelectSingleNode("plannedCompletionDate").Value;
                TaskDataBuffer.TaskPlannedStartDate = 
                    item.SelectSingleNode("plannedStartDate").Value;
                TaskDataBuffer.TaskProgressStatus = 
                    item.SelectSingleNode("progressStatus").Value;
            }
            page++;
        } while (items.Count == rowsPerPage);
    }
}