Perform API Operations Using Stored Procedures in CData SSIS Components

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

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 like retrieving OAuth credentials, uploading attachments to emails, authoring reports from CRM applications, and more.

Connect to SharePoint from an SSIS Source Component

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 SQL Statement to configure the Source Component to execute a stored procedure.
6. Set the SQL Query to an EXECUTE statement for the Stored Procedure, e.g.,

   EXECUTE DownloadDocument @File = '', @Library = '', @RemoteFile = ''
   

Configuring a Parameterized Query

1. After you have set the SQL query in the Source component, click the Control Flow tab
2. Select Variables from the SSIS menu and add the appropriate variables for the Stored Procedure (e.g., User, Library, and RemoteFile)
3. In the Control Flow tab, select the Data Flow Task
4. In the Properties pane, click the expand button for the Expressions property
5. In the Property expressions list, select a blank row and choose the SQL Statement for the CData SharePoint Source component
6. Set the Expression to a parameterized EXECUTE statement for the Stored Procedure, including quotes, e.g.,

   "EXECUTE DownloadDocument @File = '" + @[User::File] + "', @Library = '" + @[User::Library] + "', @RemoteFile = '" + @[User::RemoteFile] + "'"
   

After saving your Expression, you can execute the project and find the downloaded files in the download directory.

SSIS Sample Project

To quickly get 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 the 'Connection Manager.'