Using SQL Server as a backup for critical business data provides an essential safety net against loss. In addition, backing up data to SQL Server enables business users to more easily connect that data with features like reporting, full-text search, analytics, and more.
This example demonstrates how to use the CData SSIS Tasks for Salesforce inside of a SQL Server SSIS workflow to transfer data directly from Salesforce account into a Microsoft SQL Server database.
Importing data with CData SSIS Tasks for Salesforce (SSIS 2008)
Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task from the Toolbox onto the Control Flow screen as shown below.
Step 3: Create a new connection manager, of the Salesforce type and configure the Salesforce credentials.
Step 4: Add a new Salesforce Source control and a ADO.NET destination.
Step 5: Configure the Salesforce Source control's connection manager and choose the connection manager previously created.
Step 6: Once the connection manager has been selected, specify the query to use for the data extraction. In this article, we will get all accounts. Note the query highlighted below.
Step 7: Close the Salesforce Source control's wizard and connect it to the ADO.NET Destination.
Step 8: Open the ADO.NET Destination and add a New Connection. Enter your SQL Server instance and database information here.
Step 9: Set your Data access mode to "table or view" and select the table or view to populate.
Step 10: Configure any properties you wish to on the Mappings screen.
Step 11: Close the ADO.NET Destination Editor and run the project.
After the SSIS Task has finished executing, your database will be populated with data from Salesforce.
SSIS Sample Project
Note: Before running the demo, you will need to change your connection details to fit your environment as detailed in Step 3 and Step 8.