Connect to SharePoint Online from SSIS (Select)
We have SharePoint List Adapter available at Codeplex but problem is it does not connect to SharePoint Online.
Problem is that SharePoint Online supports claim based authentication but SharePoint List Adapter available at Codeplex does not support claim based authentication.
If we try to connect to SharePoint Online using SharePoint List Adapter available at Codeplex it gives error below,
The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized.
So how to fetch List data from SharePoint Online?
I figured out best way is to create .net code to do so and use Script Component as either Source or destination depending on whether we need to select data or insert\update data to List.
We can also use Script Component as transformation in case we need to delete records.
We will consider scenario where we need to Insert and Select data from List.
For testing purpose we will consider List with following Schema,
So we have six columns mainly,
Title, City, Company, Initials, Job Title, First Name
First we will see how to select data from SharePoint Online List.
For select purpose we have to choose Script Component as Source in data Flow task.
Make sure you import Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll in script component as without this two dlls you will not be able to interact with SharePoint.
I have used SecureString for secure password, we need to import System.Security to use SecureString Class.
We need to make sure that we add Output Columns with appropriate Data Types so that we do not run into Data Type Mismatch errors.
I also created code to fetch needed fields with data types, we can use this code to decide what data type to choose.
Code:
using (ClientContext cont = new ClientContext("https://<my site>.sharepoint.com/"))
{
SecureString Password = new SecureString();
foreach (char c in "<Password>".ToCharArray()) Password.AppendChar(c);
cont.Credentials = new SharePointOnlineCredentials("<user>@<my site>.onmicrosoft.com ", Password);
Web web = cont.Web;
cont.Load(web);
cont.ExecuteQuery();
List sstest = web.Lists.GetByTitle("SPO_Script");
CamlQuery query = new CamlQuery();
query.ViewXml = @"<View>
<ViewFields><FieldRef Name='Title' /><FieldRef Name='WorkCity' /><FieldRef Name='Company' /><FieldRef Name='Initials' /><FieldRef Name='JobTitle' /><FieldRef Name='FirstName' /></ViewFields>
</View>";
ListItemCollection items = sstest.GetItems(query);
cont.Load(items);
cont.ExecuteQuery();
ListItem item = items[0];
string cols = "";
foreach (var fields in item.FieldValues)
{
cols=cols+fields.Key+":"+fields.Value.GetType()+"\n";
}
System.IO.File.AppendAllText("D:\\SPO_Columns.txt",cols);
}
I have used CAML query but instead you can use “CamlQuery query = CamlQuery.CreateAllItemsQuery();
” to fetch all fields.
Above code will give few more system columns as well.
Output of code will be something like,
Title:System.String
WorkCity:System.String
Company:System.String
Initials:System.String
JobTitle:System.String
FirstName:System.String
MetaInfo:System.String
_ModerationStatus:System.Int32
_Level:System.Int32
ID:System.Int32
UniqueId:System.Guid
owshiddenversion:System.Int32
FSObjType:System.String
Created:System.DateTime
Modified:System.DateTime
FileRef:System.String
Once you have created OutputColumns for Script Component we can add code to fetch data and push to output buffer.
InputOutput section of Script Component will look like,
In Script Component we will have three functions,
PreExecute(),PostExecute() and CreateNewOutputRows()
For the purpose of select we will not be changing anything in PreExecute and PostExecute method, if you want you can do clean-up in PostExecute and Initial Setup in PreExecute.
In CreateNewOutputRow function we will add our logic to fetch List data and pushing it into Buffers.
Code:
public override void CreateNewOutputRows()
{
using (ClientContext cont = new ClientContext("https://<my site>.sharepoint.com/"))
{
//Connect to sharepoint Online
SecureString Password = new SecureString();
foreach (char c in "<Password>".ToCharArray()) Password.AppendChar(c);
cont.Credentials = new SharePointOnlineCredentials("<user>@<my site>.onmicrosoft.com ", Password);
Web web = cont.Web;
cont.Load(web);
cont.ExecuteQuery();
//Load List
List sstest = web.Lists.GetByTitle("SPO_Script");
//CamlQuery query = CamlQuery.CreateAllItemsQuery();
CamlQuery query = new CamlQuery();
query.ViewXml = @"<View>
<ViewFields><FieldRef Name='Title' /><FieldRef Name='WorkCity' /><FieldRef Name='Company' /><FieldRef Name='Initials' /><FieldRef Name='JobTitle' /><FieldRef Name='FirstName' /></ViewFields>
</View>";
ListItemCollection items = sstest.GetItems(query);
cont.Load(items);
cont.ExecuteQuery();
foreach (ListItem item in items)
{
Output0Buffer.AddRow();
Output0Buffer.WorkCity= item.FieldValues["WorkCity"].ToString();
Output0Buffer.Company= item.FieldValues["Company"].ToString();
Output0Buffer.FirstName = item.FieldValues["FirstName"].ToString();
Output0Buffer.JobTitle = item.FieldValues["JobTitle"].ToString();
Output0Buffer.Initials = item.FieldValues["Initials"].ToString();
Output0Buffer.Title = item.FieldValues["Title"].ToString();
}
Output0Buffer.SetEndOfRowset();
}
}
When you run this Script Component it will generate output with all rows we have in our list.
You can then push this rows in either SQL or any other destination that SSIS supports.
We will do insert operation and second part of this blog.
Let me know if you have any suggestions or questions regarding code that I have written or content of blog.
Second Part of blog: