Updating sharepoint lists from ssis

16 Feb

The object-model approach is more development intensive and requires familiarity with a myriad of object-model namespaces, hierarchies, classes, and enumerations.

I have a Share Point that has list and I need to be able to update this list with new values from an Excel sheet (using VBA).

However, this data is often isolated from the rest of an organization’s data system.

Is what I am asking possible and if some could point me in the direction of a solution. Update Changes xl List Conflict Dialog End Sub You can synchronize Excel 2007/2010 and Share Point 2007/2010 with a download and install of a Microsoft Excel Addin. Name = LIST_SHEETNAME Set ws = Worksheets(LIST_SHEETNAME) src(0) = SHAREPOINT_SITE & "/_vti_bin" src(1) = SHAREPOINT_LIST src(2) = SHAREPOINT_VIEW Open in datasheet view , delete the existing columns and paste the new ones.Another approach was to create a script task and use web client to hit the service, but its like – why you need ssis then?So, I decided to improve it a little bit to support Share Point online, however decided to drop connection manager.Once you click the download link, save the zip file which contains the file. The SSIS Integration for Microsoft Share Point Setup Wizard will appear. Select the checkbox to agree to the terms of the End-User License Agreement. Next, select one of the three setup types that best suits your needs. The Share Point connection manager allows you to specify how you want to connect to your Share Point server.To add a Share Point connection to your SSIS package, right-click in Connection Manager area, and choose "New Connection..." from the context menu.So first upload the excel file to the document library then run the workflow to update/Add the item in excel to list. The workflow action will read the data from the excel then copy to the list items. You could write Convert class to convert the data to corresponding Share Point field data. The happiest thing that it could also run in sandbox solution. :) Sub Import Sharepoint List() Dim ws As Worksheet Dim src(2) As Variant Dim iz As Integer '--- check if config sheet is available For iz = 1 To Sheets. I have done it before, and I remember that it was easy, because I used Share Point List Data Connectors for SSIS from here: https://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 Guys put a lot of effort there, and I used those adapters in couple of my projects.It worked really well with Taxonomy fields and complex lookup fields, what I think makes those connectors the best out there. I was so disappointed, so I started looking for other projects and I found another implementation, where SP Client Object model was used, but quality of data wasn’t good enough.