Connect to SharePoint Online from SSIS (Insert)
In previous part we saw that how we can use Script Component as Source to pull data from SharePoint Online and push it to any destination that SSIS supports.
In this post we will see how we can take data from SQL and push it into SharePoint Online List.
Schema of SharePoint List:
We will pull data from SQL table and push it in this step.
I used OLEDB Source in SSIS as source and pulled data from SQL.
For pushing data in SharePoint Online I used Script Component as destination.
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.
For destination we do not have to configure any input columns as it will pick it from buffer, only thing is we have to select columns that we want to push in input column section of Script Component.
In Script task we will have three methods mainly which we need to take care of,
PreExecute(), PostExecute() and Input0_ProcessRow(Input0Buffer Row)
In PreExecute we will open connection and use that connection in rest of the code.
We can open connection in ProcessRow as well but that will cause performance issue as for each row it will open new connection.
It will be much more faster if we open connection in pre-execute and close it in PostExecute.
We need to create object of ClientContext and Web classes in ScriptMain class so that all methods can access those objects.
Code:
ClientContext cont;
Web web;
Code in PreExecute:
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 = cont.Web;
cont.Load(web);
cont.ExecuteQuery();
base.PreExecute();
Code in PostExecute:
cont.Dispose();
base.PostExecute();
Input0_ProcessRow method is executed for each row passed by source to destination.
We want to push each of this rows in SharePoint online.
I have hard coded values for all columns other than “Title” for simplicity but you can choose appropriate columns from Row object.
Code in Input0_ProcessRow:
List sstest = web.Lists.GetByTitle("SPO_Script");
ListItemCreationInformation newItem = new ListItemCreationInformation();
ListItem listItem = sstest.AddItem(newItem);
listItem["Title"] = Row.CategoryName;
listItem["WorkCity"] = "Atlanta";
listItem["Company"] = "ATT";
listItem["Initials"] = "JD";
listItem["JobTitle"] = "Software Engineer";
listItem["FirstName"] = "Judith";
listItem.Update();
cont.ExecuteQuery();
When you execute this package it will fetch data from SQL and push it in SharePoint Online List “SPO_Script”.
I have covered select and insert in this two blog post but we can achieve update and delete as well with equal simplicity. You need to change operation and use ExecuteQuery method of ClientContext object.
Link below has all operations covered
https://www.codeproject.com/Articles/399156/SharePoint-2010-Client-Object-Model-Introduction
If you need help with CAML query use below tool it will give you complete code with CAML query,
https://biwug-web.sharepoint.com/CamlDesigner2013/CamlDesigner2013.zip
Possible Issues you can run into:
When you create custom columns for List there is possibility that SharePoint gives random unique name to such column for identification purpose.
Consider you create column with name “IDs” but as SharePoint already has built in column with same name it will give random name to this custom columns.
Consider it gives name as “c8ea”, in such case if you try to fetch a field with name “IDs” it will not fetch data saved in your custom column. For that you have to use “c8ea” as field name.
If you want to find internal name for column you need to hover on column name in List Setting.
So that’s all I have for you all at this point.
Please go through both parts of this blog to understand it better.
Let me know if you have any suggestions or questions regarding code that I have written or content of blog.
First Part of Blog:
Comments
Anonymous
June 05, 2014
Thank you for sharing can you pls guide me how will work update? Regards, Anoop NagaooanAnonymous
June 05, 2014
Hi Anoop, I have created SSIS source and destination where if you select option as update it will do the work for you. ssisconnectorsharepointonline.codeplex.com You need to have VS 2010 or VS 2012 to use this. For update you need to have source with updated rows and key column and for those key columns it will update columns which have different values in source. -DilkushAnonymous
June 05, 2014
With your guidance i have tried insert its working fine but while updating i am not able to update from sql to SPonline... I am not able to find Rowcont from DB ....can share the code for Upade ? int i = 0; while (i < items.Count) { ListItem item = items[items.Count - 1]; //if (Convert.ToString(item["Title"]) == Convert.ToString(Row.Title)) { //item["Title"] = "My Updated value"; item["Title"] = Row.Title; item.Update(); cont.ExecuteQuery(); } i++; return; } Regards, Anoop nagappanAnonymous
June 05, 2014
Also This will work in online and SharePoint 2013 as well right. Regards, Anoop NagappanAnonymous
June 05, 2014
I will give you little snippet of code i think you can use that to do update... i had complete code but its all mess so coudn't find... So first we will get all data from SP CamlQuery query = CamlQuery.CreateAllItemsQuery(); ListItemCollection itemcoll = null; itemcoll = sst.GetItems(query); cont.Load(itemcoll); cont.ExecuteQuery(); Then you will have for loop for each incoming row from source and we will get ID from all those incoming rows If ID is not 0 that means most probably item is present in SP and it is update call. So we will retrieve particular row with that ID try { if (tempid != 0) { tempitem=itemcoll.Where(x => (int)x.FieldValues["ID"] == tempid).Single(); tempitem = itemcoll.GetById(tempid); cont.ExecuteQuery(); } } catch (Exception ex) { } If this tempitem is null that means this ID which we are providing does not have corresponding row in SP then it will do insert.... if tempitem is not it will do update if (tempitem == null) { listItem.Update(); cont.ExecuteQuery(); } else { foreach (var x in listItem.FieldValues) { if ((x.Key != "ID")) { if (sst.Fields.Where(field => field.InternalName == x.Key).Single().TypeAsString == "User") { tempitem[x.Key] = listItem[x.Key]; } else { try { tempitem[x.Key] = listItem[x.Key]; } catch (Exception ex) { tempitem[x.Key] = listItem[x.Key]; } } } tempitem.Update(); } cont.ExecuteQuery(); } here listitem contains new values.... instead of list you can directly use incoming row and mention Row.ColumnName I hope this helps. -DilkushAnonymous
June 08, 2014
Thank You for the valuable input i just tryed the below code its working List sstest = web.Lists.GetByTitle("SSIS"); CamlQuery query = new CamlQuery(); query.ViewXml = @"<Query> <Where> <Or> <Eq> <FieldRef Name='Title'></FieldRef> <Value Type='Text'>Test</Value> </Eq> <IsNotNull> <FieldRef Name='Title'></FieldRef> </IsNotNull> </Or> </Where> </Query>"; ListItemCollection items = sstest.GetItems(query); cont.Load(items); cont.ExecuteQuery(); int count = items.Count; if (myturn < count) if (Row.Title != items[myturn]["Title"]) { //ListItem item = items[count - i]; //item.DeleteObject(); //item.Update(); items[myturn]["Title"] = Row.Title; //items[myturn]["Email"] = Row. items[myturn].Update(); } myturn++; cont.ExecuteQuery(); if (myturn > count) { ListItemCreationInformation newItem = new ListItemCreationInformation(); ListItem listItem = sstest.AddItem(newItem); listItem["Title"] = Row.Title; listItem.Update(); } cont.ExecuteQuery(); }Anonymous
June 08, 2014
I tried to update from one data base columns to one SharePoint list column.... so multiple column how i can insert and update .... can you guide me on this Now i tried listItem["Title"] = Row.Title; so i have to insert listItem["Email"] = Row.Title; so this Row. title will not have email value what will do in this case? Thank you for your guidance and support. Regards, Anoop NagappanAnonymous
June 08, 2014
Anoop you can put lisItem["Email"]= Row.Email or whatever is the name of column which holds email value. -DilkushAnonymous
June 09, 2014
but i am getting below error Column 'Email' does not exist. It may have been deleted by another user. /AnoopWorkLocation/Lists/SSIS but the list having the column called Email Row.Email is outo genarated right? bufferwrapper.cs can we able to edit?Anonymous
June 09, 2014
You need to check at sharepoint end for proper column name. You can use CamlDesigner2013 to get CAML query for your list. -DilkushAnonymous
June 10, 2014
Now its working fine, i just delete and recreated list now its working ... Thank you Dilkush for your all quick response and this article really valuable information you have been shared. Regards, Anoop NagappanAnonymous
June 10, 2014
Hey not a problem Anoop... glad to know that I could help. -DilkushAnonymous
June 17, 2014
The comment has been removedAnonymous
June 19, 2014
The comment has been removedAnonymous
November 23, 2014
hi, I have a query regarding person/group datatype. I am pulling data from sql and pushing to sharepoint destination. source side, we have varchar as datatype where as in destination it has person/group datatype. how to map this?Anonymous
November 25, 2014
The comment has been removedAnonymous
February 23, 2016
Hi Dilkush, I need to download a flat file from O365 library with have status Y and load them into Db using SSIS. Once it is done then I need the Status to be changed as N. Please help