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);

Sharepoint and Integration Services

In some cases the best way to import data to our portal is not to use BDC due complexity that it involves. Somebody could think that with SSIS we can insert elements in a SharePoint list OOB, but it is not true. The Web Service Task permits to interact with an external web service but is very limited because are not compatible with all web services. The Lists.asmx web service from SharePoint is not supported and you have to take a workaround to import information to SharePoint.

The solution is trivial: you can generate a proxy class to communicate with your service with de WSDL.exe tool included in the command line tools of VS2005. Then you can add a Script Task to your SSIS project and add to the script a new class with the code that you have generated with WSDL.exe. After the necessaries "add references" you can interact with your WSS or MOSS through its web services.

And to upload document… you can use the UploadData of the WebClient class.

Enjoy it!

Starting a SharePoint Timer Job manually

During 2 days we had the SharePoint environment down due a transaction log too large. The root problem was the recycle bin. We threw a process that updated all the files on the site with the unlimited versioning enabled in its Document Libraries. The process executed each 5 minutes and every execution duplicated the info in the site as an old version of each document. After take off-line the database, detach it and attach it again without the transaction log (see the post that explain it) we could recover the space necessary to work. Then we delete each old version of the documents and send it to recycle bin. After set the quarentine period to 1 day we did not want to wait 1 day to get the space free. For that reason, we made this simple code to execute the SPJob that actually deletes the files in the database when it has been deleted of the second level recycle bin.

SPSite site = new
SPSite("http://<URL>");

foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)

{

if (job.Name == "job-recycle-bin-cleanup")

{

    job.Execute(new
Guid("C864BB7F-7346-4538-9720-2AADB2ED5247"));

}

}

 

The guid that ypu have to pass is the guid of the content database. I retrieve right clicking on the content database in the central administration (remember to substitute the hex code of the hyphens '-'). In this example the guid retrieved was: DatabaseId=%7BC864BB7F%2D7346%2D4538%2D9720%2D2AADB2ED5247%7D

Related Links:
http://experienciasnet.blogspot.com/2006/09/shrinking-log-file-in-sql-server-2005.html

How to make your own WSP

When I asked myself this question I thought in the VS2005 extensions for SharePoint. I discovered then that it was only supported for an environment with stand alone installation. I could deploy a solution only the first time (it only gives me an error about a feature not installed). The successive times I get an "Object reference not set to an instance of an object" and VS2005 didn't create the WSP file. Then I find the post about the Timer Job in WSS and a link to the method to deploy the solution without de extensions for VS2005. It seems complex, but it isn't.

Note: I had to activate the feature from the command line (stsadm).

Related links:
http://www.andrewconnell.com/blog/articles/UsingVisualStudioAndMsBuildToCreateWssSolutions.aspx
http://www.andrewconnell.com/blog/articles/CreatingCustomSharePointTimerJobs.aspx (An example of WSP)

STSADM “Command Line Error”

Today I was deploying a solution to my server through the command line. After pasting command that I had copied from a web I received the "Command Line Error". The solution is to write the command without paste. The reason is the encoding of the pasted text. I found the explanation in the blog in the related links.

Related Links:
http://blogs.msdn.com/sharepoint/archive/2007/02/24/updated-installation-guide-for-the-20-server-admin-application-templates-for-wss-3-0.aspx

 

70-541 – My first SharePoint certification

Today I have passed the 70-541 exam. It is about development in WSS and it is not difficult if you have the key concepts clear. The questions are 80% about the API (managinitems, lists and sites) and the rest is about deploying the solution.