Perform API Operations Using the CData SharePoint Task

The CData SSIS Data Flow components allow you to easily import and export data from various data sources. But in some cases, the type of operation you need does not fit the SSIS Source or SSIS Destination models.

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 to execute the DownloadDocument stored procedure, you can follow the same process to execute the stored procedures of any CData SSIS Component to perform other actions — e.g. retrieving OAuth credentials, uploading attachments to emails, authoring reports from CRM applications, and more.

Connect to SharePoint

1. Open Visual Studio and create a new Integration Services Project.
2. Add a new CData SharePoint Task to the Control Flow screen.
3. Open the CData SharePoint Task.
4. In the CData SharePoint Task Designer, 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. Click Test Connection to ensure the connection is configured properly.

Configure the CData SharePoint Task

After configuring the connection to SharePoint, configure the task to call a stored procedure. Continue with the Connection Manager tab.

1. Set SQLSourceType to "Direct Input."
2. Set Command Type to "Stored Procedure."
3. Set Stored Procedure to the desired stored procedure to execute. For example, [CData].[SharePoint].[DownloadDocument]
4. On the Parameter Mapping tab, add or assign a variable to be used as the input and output parameters for the stored procedure.

Assign Values to Variables

With the connection, SQL statement, and stored procedure parameters configured, you only need to assign values to the configured variables to execute the stored procedure.

1. Click Variables in the SSIS menu.
2. For each variable, assign a value.

Run the Package

At this point, you are ready to run the package. You can add additional tasks to follow the execution of the stored procedure, using the result of the stored procedure to influence decisions and perform other tasks. Because the CData SharePoint Task is built to work with SSIS, you'll get native integration with your API operations.

SSIS Sample Project

To quickly get started executing SharePoint API operations within SQL Server Integration Services, download the fully functional sample package. Before running the demo, you will need to change your connection details in the 'Connection Manager' and edit the values for the package variables.