Multi-Select fields on a Sharepoint list using OWSSVR.dll

Question

I am in the process of creating a massive SSIS project to move the data from a series of Sharepoint Lists to a new Dynamics CRM Implementation (On-line). The SSIS package uses an OData Source to pull the data from the lists.

There are two columns on my list that do not show up on the OData call. These two columns have Multi-Select or Multi-Lookup values. REST services do not support multi-select choice fields. (Found on another thread: The methods you are attempting only work with choice columns that are NOT multi-select. Unfortunately, multi-select choice columns are not supported by the REST interface. Same holds true for SharePoint 2013 apparently.)

Therefore, I need to try to access the data in these columns on the SharePoint List using the OWSSVR.dll. The list I am having issues with gives me a "This page can’t be displayed" or a "This site can’t be reached" depending on the browser I use.

I verified the List ID by grabbing It from the list settings. Since it didn't work, I went to another SharePoint List I already migrated to verify the URL format. The other list works and brings back the data in XML format.

I am wondering if the OWSSVR.dll has the same restriction on Multi-select values. Any ideas or suggestions?

Psuedo URLS (restricted access site):

Works: http://dummy.sharepointSite.Com/cases/_vti_bin/owssvr.dll?Cmd=Display&List={b703d405-48c8-4211-9137-e1b50bdb0330}&XMLDATA=TRUE

Broken: http://dummy.sharepointSite.Com/cases/_vti_bin/owssvr.dll?Cmd=Display&List={8e148584-b5be-48f5-9343-85d23a7731cc}&XMLDATA=TRUE

Answer 1

I figured out a way to do this without using OWSSVR. I have to set up the User Context to the SharePoint site then retrieve the list of items.

Methods:

public static ClientContext SetupSPContext(string documentLibrarySiteURL, 
                        string userName, string password, string domain = "")
{
    ClientContext clientContext = new ClientContext(documentLibrarySiteURL);
    SecureString pwString = new SecureString();

    foreach (char c in password.ToCharArray()) { pwString.AppendChar(c); }
    if (!String.IsNullOrWhiteSpace(domain))
        clientContext.Credentials = new NetworkCredential(userName, pwString, domain);
    else
        clientContext.Credentials = new SharePointOnlineCredentials(userName, pwString);

    Web web = clientContext.Web;
    clientContext.Load(web);
    clientContext.ExecuteQuery();
    return clientContext;
}

public static ListItemCollection GetListItems(ClientContext context, string listName)
{
    ListCollection listCollection = context.Web.Lists;
    List targetList = context.Web.Lists.GetByTitle(listName);

    CamlQuery query = new CamlQuery();
    query.ViewXml = "<Query><OrderBy><FieldRef Name='fieldName' /></OrderBy></Query>";
    ListItemCollection collListItem = targetList.GetItems(query);

    context.Load(collListItem);
    context.ExecuteQuery();

    if (collListItem.Count == 0)
    {
        return null;
    }
    else
    {
        return collListItem;
    }
}

Code in SSIS Script Component:

//In the PreExecute method: 
//  Variables were defined for the class and not in the preExecute Method
ClientContext spContext = SetupSPContext(Variables.spLocation, Variables.spUserName, 
                                Variables.spPassword, Variables.spDomain);
ListItemCollection listItems = GetListItems(spContext, "List Name");

//Inside the Individual loop inside SSIS (Input0_ProcessInputRow):
ListItem listItem = GetListItem(listItems, fieldValue);

//Multiple Lookup Code
var values = (FieldLookupValue[])listItem["fieldName"];
var finalValue = "FieldName values:  ";

if (values.Length > 0)
{ 
    foreach (var value in values)
    {
        finalValue = value.LookupValue + "; ";
    }
}

//Multiple Select
if (listItem["fieldName2"] != null)
{
    var valuesTwo = (string[])listItem["fieldName2"];
    string combinedValues = "fieldName2 Values:  ";

    foreach (var value in valuesTwo)
    {
        combinedValues += value + "; ";
    }
}