SharePoint and Integration Services

I am going to explain a simple way to load a SharePoint list with the information stored in a SQL table or other source accessible from SSIS. The objective is to create a mechanism that updates the content of the SharePoint list periodically with the data stored in a table. The solution is obvious but I will expose the different approximations to the solution and the alternative that I implemented.

BDC: My first thought was use the BDC to expose the information through the SharePoint site, but it was too complex (I only wanted to show the info in a table) and this solution was not compatible with WSS.

Object Model: We can create an assembly that updates the content with the SharePoint API. This solution would be acceptable if the SQL Server and the SharePoint server was in the same machine, but this scenario is too simple for a real situation. In addition, it involves deploying the assembly through all the servers in the farm (using a feature) and creating a SPJob to execute it periodically.

SSIS Web Service Task: This would be the ideal solution, but this SSIS task is unable to invoke the SharePoint web services L.

Custom SSIS task: Whether the out-of-the-box task in SSIS is unable to invoke a web service, why we cannot create a custom SSIS task? Well, I prefer to wait until a future version of SSIS that include it J.

SSIS Script task:
This is the solution that I adopted. It consists in the extraction of the information with a common SSIS package and, at the moment of the load of the content to the SP list, a SSIS script task inserts the data in the SharePoint list through the WSS web services. The main problem is how to invoke the web service and for that reason I used the wsdl.exe to create a proxy class which was able to invoke the web service. Then I added this proxy class to the script task and gave the appropriate permission to the list in order to the SQL Agent identity was able to invoke the web service. So, the solution only consists in a SSIS package that an SQL Server job executes periodically.

This is my solution, but I am sure that it is not the best solution to all cases. In my case the information was about 15.000 rows and the load of all content was completed in 30 minutes. In others cases it may be unacceptable and the only acceptable solution would be the BDC.

Related Links:
http://msdn2.microsoft.com/en-us/library/7h3ystb6(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/lists.aspx

Add a new SPGroup programmatically to SharePoint

Recently I have developed a web application to interact with SharePoint. Its objective was to hide the SharePoint UI to the final users in order to they can administer the site collection without any knowledge of SharePoint. Users have rights to manage users and groups but we wanted that they did not navigate through the administration pages of the site. For that reason the web application uses the API to do all the operations. At the moment the most complex operation has been the "add new group" due his poor documentation. This is the code that I have used to accomplish this operation:

//Add the group to the SPWeb web
web.SiteGroups.Add(groupName, owner, defaultuser, description);

//Associate de group to the SPWeb. Now it will display in the quick launch bar
web.AssociatedGroups.Add(web.SiteGroups[groupName);
web.Update();

//Assignment of the roles.
SPRoleAssignment assignment = new SPRoleAssignment(web.SiteGroups[groupName]); SPRoleDefinition roleApp = web.RoleDefinitions["Aprobar"]; assignment.RoleDefinitionBindings.Add(roleApp);
SPRoleDefinition roleCol = web.RoleDefinitions["Colaborar"]; assignment.RoleDefinitionBindings.Add(roleCol);