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:
Comments
Anonymous
October 01, 2013
The comment has been removedAnonymous
October 09, 2013
Hey there, this is a good workaround, is there any way we can see the complete main.cs? im getting errors when trying to import Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll . Best Regards.Anonymous
November 21, 2013
The comment has been removedAnonymous
November 21, 2013
Im using bids 2010 (sql server data tools) Make sure package is configured to run in 32 bit mode as sgarepoint client dlls are not available for 6r bit if im not wrong. You are to load dll and then it is giving error or you are not even able to add references?Anonymous
November 26, 2013
Hello Dilkush, Thanks for this post. I am doing a variation on what you have described above - trying to use Linq to SharePoint on the server itself. I'm using SSDT with sql server 2012 querying SharePoint 2010, all on the same server. I have an issue you may have come across. The vsta project compiles fine, but when I run the package I get the error below. The references to the 4 dts dll's are v4 .Net. I've tried to reference the v2 version but there is a new object in the DTSpipeline dll OutputNameMap that is used in both BufferWrapper and ComponentWrapper. If I step back and use BIDS all works fine. Mush obliged if you can help. Thanks, John Microsoft SharePoint is not supported with version 4.0.30319.1008 of the Microsoft .Net Runtime. at Microsoft.SharePoint.Administration.SPConfigurationDatabase.get_Farm() at Microsoft.SharePoint.Administration.SPFarm.FindLocal(SPFarm& farm, Boolean& isJoined) at Microsoft.SharePoint.SPSite..ctor(String requestUrl) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)Anonymous
November 26, 2013
Few articles which might help... (in case you did come across this) support.microsoft.com/.../2796733 blogs.technet.com/.../sharepoint-2010-management-shell-net-4-0-runtime-version-error.aspx spsd.codeplex.com/.../450471 support.microsoft.com/.../2724471Anonymous
July 10, 2014
this worked great. Ty!!Anonymous
November 02, 2014
The comment has been removed