sharepoint - SSIS 脚本组件源 - 通过 Odata 从 SharePoint 365 列表中读取

Question

如果我的问题没有得到充分描述，我深表歉意。我是 .Net/C#/SSIS 新手。如果已经回答，也很抱歉，我已经尝试在这里和谷歌搜索几个小时但没有成功。

背景:我需要从 SharePoint 365 列表中提取数据行，并将某些列反透视为可导入 SQL Server 表的格式。我意识到 SSIS 有一个 Odata Source 和内置的 Unpivot 组件，我已经成功地将它们用于概念验证。

但是，我认为我需要一个自定义脚本组件，因为要从源 SharePoint 列表中取消透视的列数是可变的。每个月左右，都会增加一个新的栏目(它涉及SharePoint中的一个财务预测“工具”，以及最新的预测月份变化)。我的理解是，源列必须在设计时在 SSIS 中定义，所以如果我的源列发生变化，我能想到解决这个问题而不每月手动更改 SSIS 数据流的唯一方法是以编程方式组合 Odata 源和 unpivot函数到自定义脚本组件中。

我理解或可以弄清楚逆轴逻辑。我正在努力解决的部分是如何实际连接到并公开一个给定的列表，它的数据行/列作为列表，我可以循环遍历并执行到输出列的映射。

我请求指导的“起点”是这样的:1) 使用标准 SSIS Odata 连接管理器创建并成功连接到有问题的 SharePoint 站点。2) 在视觉设计器上创建了标准的“脚本组件”，type = source。3) 从脚本组件属性中，将 Odata 连接管理器与名称“myConnection”相关联。4) 需要帮助 -> 在脚本组件中，打开一个到特定列表的连接，读取它的内容，并执行 unpivot 逻辑。

出于说明目的，假设源是一个 SharePoint 列表，其中包含两个名为 Study 和 Site 的“固定”字符串列，以及名称与月末日期匹配的可变数量的列(例如 9/30/2016、10/31/2016 等)包含整数值。我想将研究和站点源列映射到同名的目标列，并逆透视月份列，其中列名映射到 ProjectionMonth，整数值映射到 ProjectionValue。

这是我想到的基本算法(我知道这是不可编译的 - 这就是我需要你帮助的地方!):

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;

                }
        }
    }

}

编辑:例如，假设源 SharePoint 列表具有以下内容:

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

我想让脚本组件连接到列表，读取它的内容，并返回以下非透视数据集以最终加载到 SQL Server 中:

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

Answer 1

所以这可能不是一种理想的实现方式，它没有利用我想要的标准 SSIS Odata 连接管理器......但它在技术上确实完成了工作并且现在对我来说已经足够好了.

如果您有任何建议的反馈/改进/等，将会感兴趣。

#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);
            }
        }
    }
}