Integration development teams often need to connect different systems using Microsoft SQL Server Integration Services (SSIS). In this blog, I will demonstrate how to connect with Salesforce.com using SSIS.
There is no robust way available in Visual Studio tools to perform read/write operations on Salesforce objects. There is a widely used 3rd party plugin from CozyRoc to establish communication and perform task operations in SSIS. After installing CozyRoc, SForce connection manager, source and destination components will appear under the SSIS toolbox in Visual Studio.
1. Create new SSIS project in Visual Studio.
2. Right click on Connection Managers and click New Connection Manager.
3. Select SForce as Connection manager type and click Add button.
4. In the Salesforce Connection Manager, under Server tab, provide the Salesforce User name and Password. In the Advanced tab, provide the Server URL. Click OK to create a connection manager.
5. Create and open a Data Flow task. Drag-and-drop the Salesforce Source component from the SSIS toolbox to the Data Flow task pane.
6. Double click on “Salesforce Source 1” to open Properties. Under the Connection Managers tab, select the SForce connection “Salesforce Connection Manager 1” that we created in step 4.
7. Go to Component Properties, select InputType “Statement”. For a runtime filter, define a variable and populate a value.
8. For Statement, write a SOQL and use variable as filter.
Following this process, we can connect to Salesforce and fetch data by using filters created on runtime.