APIs require up-to-date and strong documentation, increased security levels, comprehensive testing, routine versioning, and high-reliability to be appropriately utilized. With the CData SSIS Data Flow components you can 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. This article uses the CData SSIS Components for SharePoint to execute the DownloadDocument stored procedure, but 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.

1. 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.

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

1. 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.

Learn More

APIs have the potential to transform businesses. Forward-thinking companies are reducing costs and time spent by “building their solutions with best-of-breed components, which they access via APIs.

With the CData SSIS Components you can simplify data movement, integration, & transformation with codeless connectivity. Synchronize or replicate cloud applications and databases, import & export to various files and cloud storage solutions, connect back-office processes through SSIS workflows, and so much more.