Connect to SharePoint Online from SSIS (Select)

  • Article

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:

https://blogs.msdn.com/b/dilkushp/archive/2013/10/01/connect-to-sharepoint-online-from-ssis-insert.aspx

Comments

  • Anonymous
    October 01, 2013
    The comment has been removed

  • Anonymous
    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 removed

  • Anonymous
    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/.../2724471

  • Anonymous
    July 10, 2014
    this worked great. Ty!!

  • Anonymous
    November 02, 2014
    The comment has been removed