RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Sync Your Database to SharePoint Using SSIS : Page 2

Discover how to import and export SharePoint list items using SQL Server Integration Services and the new Collaborative Application Markup Language (CAML).

Creating Your Web Service Proxy
To begin creating the sample application, open Visual Studio and create a new project of type "Class Library," and name it something like "SharePointServices" (see Figure 1).

Next add a web reference to the Lists service of your SharePoint site by following this procedure:
  1. Right click on References and select "Add Web Reference."
  2. In the URL field, enter http://[servername]/[site name]/_vti_bin/Lists.asmx.
  3. Optionally, enter a reference name and click "Add Reference" (see Figure 2).
Figure 1. Class Library Project: Create a new class library project and name it "SharePointServices."
Figure 2. Adding the Reference: Use this dialog to add a reference to a Web service.
Visual Studio automatically runs WSDL.exe in the background to create a proxy class that shields you from the Web services communication details and also provides simple asynchronous access to the Web services methods.

At this point you can add a class with some convenience functions. You're welcome to use the classes available in the downloadable solution, and that you can also find in Listing 1. This article will discuss the helper functions in more detail as it uses them.

Deploying the Class Library to SSIS
To enable SSIS to use your class library, you must sign the assembly, copy it from SharePointServices\bin\Debug\SharePointServices.dll to C:\Windows\Microsoft.NET\Framework\v2.0.50727, and install the assembly to the Global Assembly Cache (GAC).

Figure 3. Creating a Strong Key: Enter the project name in the Create Strong Name Key dialog.
If you aren't familiar with the process for signing an assembly, follow these steps:

  1. Right click on your project.
  2. Select Properties.
  3. Select the "Signing" tab.
  4. Select from the "Choose a strong name key file" dropdown.
  5. Enter the name of the project in the "Create Strong Name Key" dialog box (see Figure 3).
  6. Uncheck "Protect my key file with a password."
  7. Click OK.
  8. Compile the project.
Then, to install the Assembly to the GAC:

  1. Open up a "Visual Studio 2005 Command Prompt" from the start menu.
  2. Change to the directory where you copied the assembly (cd "C:\Windows\Microsoft.NET\Framework\v2.0.50727").
  3. Install it by executing the command:
       gacutil --i SharePointServices.dll
Now when you open up a script task in SSIS you can add a reference to SharePointServices. If you make a change to the file you'll need re-copy the changed file and reinstall it to the GAC.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date