Download files from a SharePoint site using the CData SSIS Components

The CData SSIS Data Flow components enhance SQL Server Integration Services by enabling users to easily import and export data from various sources. However, some operations may not fit the typical SSIS Source or Destination model.

This article shows how to use the DownloadDocument stored procedure included in the CData SSIS Components for SharePoint to download files from SharePoint. The CData SSIS Components surface additional functionality available in the underlying API as stored procedures. While this article uses the CData SSIS Components for SharePoint, you can follow the same process to execute the stored procedures of any CData SSIS Component.

See Call Stored Procedures from Script Components for an example of invoking a stored procedure at run-time in a script component.

See the "Getting Started" chapter in the help documentation for an example of calling stored procedures using source components at design-time.

1. Open Visual Studio and create a new Integration Services Project.
2. Add a new Data Flow Task to the Control Flow screen and open the Data Flow Task.
3. Add a CData SharePoint Source to the Data Flow Task.
4. In the CData SharePoint Source, add a new Connection Manager and add your credentials for the SharePoint site. For this demo you will want to set "Show Hidden Columns" to True as the sample code uses a hidden column.
5. In the Source Component editor, select the Table or View option in the Data access mode menu. In the Table or View menu, select the name of the Document Library that you are going to back up and close the wizard.
6. Add a Script Component to the Data Flow Task. Select the Transformation type and in the Data Flow task drag an output arrow from the 'CData SharePoint Source' to the transformation component.
7. Open the Script Component and select all the columns in the Input Columns pane.
8. After you have selected the input columns, click the Edit Script button in the Script tab. This will open a new Visual Studio window with a project in it.
9. In the 'Input0_ProcessInputRow' method, you can add code to call the DownloadDocument stored procedure. Below is an example for this method:

   public override void Input0_ProcessInputRow(Input0Buffer Row) {
     if(Row.ContentType.Equals("Folder"))
       return;
     String connString = "Offline=False;Password=mypassword;User=myuser;Url=your-sharepoint-site;SharePointEdition=your-edition";
     String downloadDir = "C:\\\Users\\\your-user\\\Documents\\\SharePointBackup\\\";
     SharePointConnection conn = new SharePointConnection(connString);
     SharePointCommand comm = new SharePointCommand("DownloadDocument", conn);
     comm.CommandType = CommandType.StoredProcedure;
     comm.Parameters.Clear();
     String file = downloadDir + Row.LinkFilenameNoMenu.ToString();
     comm.Parameters.Add(new SharePointParameter("@File", file));
     String list = Row.ServerUrl.ToString().Split('/')\[1].ToString();
     comm.Parameters.Add(new SharePointParameter("@Library", list));
     String serverurl = Row.ServerUrl.ToString();
     serverurl = serverurl.Substring(list.Length + 1);
     comm.Parameters.Add(new SharePointParameter("@RemoteFile", serverurl));
     comm.ExecuteNonQuery();
   }

After saving your changes to the script component, you can execute the project and find the downloaded files in the download directory.

SSIS Sample Project

To help you with getting started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional sample package. Before running the demo, you will need to change your connection details in both the 'Script Component' code and the 'Connection Manager'.