SSIS Script Component Source - Read from SharePoint 365 List via Odata

Question

I apologize if my question is not adequately described. I am a .Net / C# / SSIS newbie. Also apologize if already answered, I've tried searching here and Google for a few hours without luck.

Background: I need to pull data rows from a SharePoint 365 list and unpivot certain columns into a format ready for import to a SQL Server table. I realize that SSIS has an Odata Source and built-in Unpivot component, and I've successfully used those for proof of concept.

However, I believe I need a custom script component because the nummber of columns to unpivot from the source SharePoint list is variable. Each month or so, a new column will be added (it relates to a financial forecasting "tool" in SharePoint, and the latest forecasted month changes). My understanding is that source columns must be defined in SSIS at design time, so if my source columns are changing, the only way I can think to address this without manually changing the SSIS data flow each month is to programatically combine the Odata source and unpivot functions into a custom script component.

I understand, or can figure out, the unpivot logic. The part I am struggling with is how to actually connect to and expose a given list and it's data rows / columns as lists that I can loop through and perform my mapping to the output columns.

My "starting point" for requested guidance is this: 1) Created and successfully connected to the SharePoint site in question using standard SSIS Odata Connection Manager. 2) Created standard "Script Component", type = source, on the visual designer. 3) From script component properties, associated the Odata connection manager with the name of "myConnection". 4) Need help -> within the script component, open a connection to a specific list, read it's contents, and perform unpivot logic.

For illustration purposes, assume the source is a SharePoint list with two "fixed" string columns titled Study and Site, and a variable number of columns with names matching month-end dates (e.g. 9/30/2016, 10/31/2016, etc.) that contain integer values. I want to map the study and site source columns to destination columns of the same name and unpivot the month columns where column name is mapped to ProjectionMonth and the integer value is mapped to ProjectionValue.

Here's the basic algorithm I have in mind (I realize this isn't compilable - that's where I need your help!):

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;      // from MSDN tutorial, but I don't know how to adapt to Odata/SharePoint 365 list
    SqlDataReader sqlReader;    // not sure how to adapt

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.myConnection;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);  // This is from MSDN tutorial, but I don't know how to adapt to Odata

    }

    public override void PreExecute()
    {
        //Not sure how to adapt to odata to read specific SharePoint list
        SqlCommand cmd = new SqlCommand("SELECT * FROM <some sharepoint list>", sqlConn);
        sqlReader = cmd.ExecuteReader();

    }

    public override void PostExecute()
    {
        sqlReader.Close();  // Not sure how to adapt.
    }

    public override void CreateNewOutputRows()
    {

        string myStudy;
        string mySite;
        string myProjectionMonth;
        string myProjectionValue;

        // This is a rough representation of the logic needed.
        // I realize that the actual code to access column values / names depends on the class(es) I need to use, but not sure what those classes are / how to access
        foreach (myListRow in sqlConn.rows)
        {
            myStudy = myListRow.Columns["Study"].value;
            mySite = myListRow.Columns["Site"].value;

            foreach (myColumn in myListRow.Columns)

                if (DateTime.TryParse(myColumn.Name, out dateValue))
                {
                    myProjectionMonth = myColumn.Name;
                    myProjectionValue = myColumn.Value;

                    Output0Buffer.AddRow();
                    Output0Buffer.Study = myStudy;
                    Output0Buffer.Site = mySite;
                    Output0Buffer.ProjectionMonth = myProjectionMonth;
                    Output0Buffer.ProjectionValue = myProjectionValue;

                }
        }
    }

}

Edit: as an example, assume the source SharePoint list has the following:

Study   Site    9/30/2016   10/31/2016
123     ABC     5           10

I want to the script component to connect to the list, read it's contents, and return the following unpivoted data set for eventual load into SQL Server:

Study   Site    ProjectionMonth     ProjectionValue
123     ABC     9/30/2016           5
123     ABC     10/31/2016          10

Answer 1

So this is probably not an ideal way of doing it, and it doesn't leverage the standard SSIS Odata Connection Manager that I wanted... but it does technically get the job done and it's good enough for me, for now.

Would be interested on any suggested feedback / improvements / etc., if you have any.

#region Namespaces
using System;
using Microsoft.SharePoint.Client;
using System.Security;
using System.Collections.Generic;
#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        // Connect to SharePoint
        ClientContext context = new ClientContext("https://<redacted>.sharepoint.com/Development");
        SecureString passWord = new SecureString();

        foreach (char c in Variables.sharepointPassword.ToCharArray()) passWord.AppendChar(c);
        context.Credentials = new SharePointOnlineCredentials("<redacted>@<redacted>.onmicrosoft.com", passWord);

        // Define the target list
        List EnrollmentList = context.Web.Lists.GetByTitle("Enrollment Projections");

        // Find all fields in the target list
        FieldCollection myFields = EnrollmentList.Fields;
        context.Load(myFields);
        context.ExecuteQuery();

        // Load all rows from the target list
        CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
        ListItemCollection items = EnrollmentList.GetItems(query);
        context.Load(items);
        context.ExecuteQuery();

        //Store valid date fields
        List<Field> myDateFields = new List<Field>();

        foreach (Field tempField in myFields)
        {
            DateTime tempDate;

            if (DateTime.TryParse(tempField.Title, out tempDate))
            {
                myDateFields.Add(tempField);
            }
        }

        string myStudy;
        string mySite;
        string myMonth;
        string myValue;

        foreach (ListItem listItem in items)
        {
            myStudy = listItem["Study"].ToString();
            mySite = listItem["Site"].ToString();

            foreach (Field tempField in myDateFields)
            {
                myMonth = tempField.Title;
                myValue = listItem[tempField.InternalName.ToString()].ToString();

                Output0Buffer.AddRow();
                Output0Buffer.Study = myStudy;
                Output0Buffer.Site = mySite;
                Output0Buffer.ProjectedMonth = myMonth;
                Output0Buffer.ProjectedValue = Convert.ToInt32(myValue);
            }
        }
    }
}