Scenario

Recently a client had a requirement to upload documents to their SharePoint site. They wanted the ability to drop numerous files in a FTP folder and have them uploaded to SharePoint automatically.

Solution

We decided to solve this using a Custom SSIS Task that uploads files to the required Microsoft Office SharePoint Server 2007 (MOSS) Document Library.

We therefore used the Integration Services Project under the Business Intelligence Project types within Microsoft Visual Studio to create the SQL Server Integration Services (SSIS) task.

Custom SSIS Task

The Custom Task is a Class Library written in your favourite .NET language. The required references are to the Microsoft.SqlServer.Dts.Design and the Microsoft.SqlServer.ManagedDTS DLLs which are installed with SQL Server 2005. The class has to inherit from the Task base class and have the DtsTaskAttribute applied. To implement the custom behaviours, the required methods and properties of the base class can be overridden.

You can create your own public variables which will surface in the implementing package as properties on your Task. In this case, we will expose the ImportFile name, the SharePoint SiteName and the DestinationUrl. These will be set as properties on the Custom Task within the hosting package.

Overriding the Validate method provides the ability to verify that certain settings and connections are correct and available. This method is executed numerous times during design time and provides visual cues on the design surface if any property or connection has failed validation. Validate is also executed before the task is run to ensure that all settings and data sources pass validation. To fire the component validation events, use the IDTSComponentEvents interface that is passed as a parameter to the Validate and Execute methods, for example:

public override DTSExecResult Validate(Connections connections, 

                                        VariableDispenser variableDispenser, 

                                        IDTSComponentEvents componentEvents, 

                                        IDTSLogging log)

{

    if (Directory.Exists(_sourceDirectory))

    {

        return DTSExecResult.Success;

    }

    else

    {

        componentEvents.FireError(0, 

                                    "File Upload Task-Validate", 

                                    "The directory does not exist", 

                                    "", 

                                    -1);

        return DTSExecResult.Failure;

    }

}

The Execute method is called by the Integration Services runtime. It contains the main functionality and business logic of the task. It is also responsible for providing feedback to the containing package regarding the result of the task execution.

If the Task requires a customised user interface, you can develop it by providing a class that implements the IDtsTaskUI interface.

Interaction with SharePoint

In our production environment, SharePoint and SQL Server are hosted on separate servers. Therefore in order to communicate with SharePoint, we leveraged the Windows SharePoint Services (WSS) Web Services. Since we will be uploading files to SharePoint, we will be calling the Copy Web Service.

First we will create a Web Reference to the Web Service which is hosted on your SharePoint server here: http://[yourservername]/_vti_bin/copy.asmx. As mentioned earlier, the SiteName will be exposed as a configurable property in aid of flexibility. Therefore, when we create a new instance of the service, we can set the server name to the value that was supplied in the containing package:

CopyService.Copy service = new CopyService.Copy();

service.Url = SiteName + "/_vti_bin/Copy.asmx";

service.Credentials = CredentialCache.DefaultCredentials;

The specific operation to upload a file to a location in SharePoint is called CopyIntoItems and requires the following parameters (from MSDN):

SourceUrl: A String that contains the absolute source URL of the document to be copied. It is not used in the copy operation but is stored with the document on the server as the CopySource property.

DestinationUrls: An array of Strings that contain one or more absolute URLs specifying the destination location or locations of the copied document. The URL has to include the destination filename.

Fields: An array of FieldInformation objects that define and optionally assign values to one or more fields associated with the copied document.

Stream: An array of Bytes that contain the document to copy using base-64 encoding.

Results: An array of CopyResult objects, passed as an out parameter.

After ensuring that all required objects are available, the web service can be called. Ensure that this is done with a proper exception handling  framework in place.

uint copyResult = service.CopyIntoItems(ImportFile, 

                                        DestinationUrls, 

                                        fieldInfoArray, 

                                        bytes, 

                                        out resultArray);

If copyResult is 0, then you can inspect the resultArray for any ErrorMessages that might have been generated. Depending on whether you deemed the operation successful, you can either fire an error event (componentEvents.FireError) and return DTSExecResult.Failure or write a success message (componentEvents.FireInformation) and return DTSExecResult.Success.

Using the Task

To call the custom task successively for each file within a folder, we will place it within a ForEach Loop container in the hosting SSIS package.

This container enumerates files in a specific folder and saves the current file name to a variable. Our custom task then uses the value in this variable as its SourceUrl. The other properties of the task can be set as per normal using a fixed value or a variable using expressions.

Executing the Task

The SSIS package will be executed by a SQL Server Agent Job that runs within SQL Server. 

Before we can create the Job, we need to set up a Proxy with the correct credentials to interact with SharePoint. If we do not do this, the web service initialisation will fail, because the caller is not a recognised SharePoint user. We need to create the appropriate credentials first. In SQL Server Management Studio, under Security > Credentials, create a new Credential specifying the SharePoint account you want the package to execute under. To create the Proxy, go to SQL Server Agent > Proxies > SSIS Package Execution and create a new Proxy. Provide a name as well as the name of the Credential we just created. Under Active to the following subsystems section, check the box next to SQL Server Integation Services Package. Click OK.

We can now add a new Job to the Jobs folder under SQL Server Agent. First specify a name and under Steps on the left, add a new Step. Enter the Step Name and the Type will be SQL Server Integration Services Package. From the Run As dropdown list, select the recently created Proxy. In the General tab, we will specify the location of the SSIS package. The package’s configuration file can be set on the Configurations tab. Additional values can be set on the other tabs. In Advanced on the left, success and failure condititions can be set. Click OK to save the Step.

Additionally, you can set a schedule for when the Job should be executed as well as creating Alerts and Notifications and specifying local or multiple Target servers. Click OK to save the Job.

The Job is now setup to execute under the correct security context with regard to SharePoint. 

Conclusion

The flexibility and extensibility of SSIS packages and tasks coupled with the ability to interact remotely with SharePoint, allows for powerful solutions to interesting problems.