Summary
As my background is in SQL Server databases I find it a lot easier to query data in SQL. The process in this post will show you how to carry out a manually run download of data from multiple objects in Salesforce or Dynamics CRM – using trial versions of KingswaySoft Tools in SSIS (SQL Server Integration Services).

I’ll be connecting to a trial Salesforce instance and downloading Contacts to SQL Server. Whenever I say Salesforce it can be interchanged with Dynamics CRM – the KingswaySoft CRM Components only slightly differ in how the objects and fields are selected.

I’ll assume that you have no prior knowledge of Visual Studio or SSIS, although you will need know how to connect to and run queries against a SQL database.

Requirements
You’ll need:
* A Salesforce or Dynamics CRM instance (this can be a trial)
* A Windows PC/Server with SSIS installed.
* A SQL Server instance (this can be hosted on a VM, or directly in Azure) with an empty database.

I’ll be using a VM running Windows 10 with the below items installed:
* SQL Server 2012 SP4
* Visual Studio 2015 Update 3
* SQL Server Data Tools for Visual Studio 2015
These are the versions I had had installed on my VM – newer versions of Visual Studio and SQL Server Data Tools should run fine, though I haven’t tested these.

Install KingswaySoft SSIS Integration Toolkit
We’ll install a trial version of this to allow us to connect to Salesforce. The only limitation that the trial version has is that you can only run it in the development environment. This means that the solution will not work if we deploy the SSIS package to a server – this will require a license.

Download links
SSIS Integration Toolkit for Salesforce –
https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-salesforce/download
SSIS Integration Toolkit for Microsoft Dynamics 365 –
https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download
On the download form KingswaySoft requires a corporate/business email.

If you haven’t got such an email you can use a temporary email from https://10minutemail.com/

Installation
Once you have the installation files just install the toolkit taking the default options.


Create A Visual Studio Project
In Visual Studio create a new project of type ‘Integration Service Project’

If this options isn’t available ensure that SSIS and SQL Server Data Tools are installed correctly.

Add and Configure Connection Managers
We will make two connections here – one to Salesforce, and the second to SQL Server.

Connection to Salesforce
Right-click on the “Connection Managers” area on the bottom of the screen and select “New Connection…”

In the next dialog select the “Connection Manager for Salesforce” then click “Add”

You will now need to choose the Instance Type, and enter your (Salesforce) User Name & Password, and Security Token.
The Security Token is used to ensure that if someone has your Salesforce account details they can’t connect to the Salesforce data connector without it.
If you know your Security Token then enter it, otherwise request a new Token. This is the Salesforce Help Page for Security Tokens – https://help.salesforce.com/articleView?id=sf.user_security_token.htm&type=5
Click on “Test Connection” and you should see a message stating that the Test Connection Succeeded. If not, check the details you have entered.

Connection to SQL Server
Right-click on the “Connection Managers” area on the bottom of the screen and select “New OLE DB Connection…”

On the next dialog click “New” to make a new data connection, then enter the details for your SQL Server connection.
My connection is shown below, connecting to the local SQL Server (this is the dot in the Server Name) using SQL Server authentication and a database called “SFDB-Blog”
Click on “Test Connection” and you should see a message stating that the Test Connection Succeeded. If not, check the details you have entered.
Click on “OK” to close the Connection Manager dialog

You should now have 2 connections in the “Connection Managers”. Don’t worry about what they’re named – that’s not important.

CREATING THE SALESFORCE SOURCE COMPONENT
From the SSIS Toolbox drag a “Data Flow Task” to the package

Right-click on the Data Flow Task and rename it to “DFT – Download Contacts”
Double-click on “DFT – Download Contacts” to edit it. You will now be in the Data Flow edit screen:

From the SSIS Toolbox drag a “Salesforce Source” component to the Data Flow

Double-click the newly added “Salesforce Source”. Click on the Connection Manager dropdown and select the value Salesforce Connection Manager.
For the Source Object select the object you want to download. I will be downloading the Contact object.

Click on the “Columns” on the left and select the fields you want to download. As a general rule of thumb it’s better to download only the fields you want – but always download the Id column (the record unique identifier). You may want to click on the top check box to uncheck all fields initially.

Click the ‘OK’ button to close the dialog

CREATING THE SQL DESTINATION COMPONENT
From the SSIS Toolbox drag a “OLE DB Destination” component to the Data Flow. Rename the newly added “OLE DB Destination” to “OLEDB – Contacts”.
Click on “Salesforce Source” component, and then drag the connector arrow to the “OLEDB – Contacts” component. It should now look as below:

Double-click “OLEDB – Contacts” to open the edit dialog.
We will now use the component to generate the SQL to create the SQL Server table to hold the data. Click the “New button” and the SQL will be displayed:

Copy this SQL and paste it into a connection to your SQL Server – I’m using SSMS (SQL Server Management Studio). In the script change the text “OLEDB – Contacts” to the name you want to give to the SQL table – I’ll be using “Contacts”. Execute the query to create the SQL table.

Back in Visual Studio – Cancel the open script dialog, and in the main dialog on the table dropdown select your new table.

Click on the ‘Mappings” on the left to ensure that all columns are mapped correctly – it should look similar to below where all columns are connected from left to right:

Click “OK” to close the dialog.

FIRST RUN OF DOWNLOADING DATA
We are now ready to run and test the first table downloading data from Salesforce.
Click the “Start” button

After a few seconds (or minutes depending on the number of records in your Salesforce data object) it should complete successfully:

To ensure it has worked correctly view the data in your SQL table:

CLEAR DATA BEFORE RUN
When the routine runs we want to clear the existing SQL data otherwise it would append duplicate data to the SQL table.
To do this click on the “Control Flow”, and drag an “Execute SQL Task” component to the main pane, above the component “DFT – Download Contacts”:

Right-click on the newly added “Execute SQL Task” and rename it to “SQL – Truncate Contacts”
Double-click on ” SQL – Truncate Contacts” to edit it. For the “Connection” select the SQL connection, and in the “SQLStatement” enter as below (using your SQL table name):

Click OK to close the dialog, and as then connect the components by dragging the arrow from “Execute SQL Task” to “DFT – Download Contacts”
Click on the “Start” button to run the routine and once complete it should look as below.


ADDING MORE DOWNLOAD TABLES
You can repeat the above process to add additional tables. I’ve added the Account table – as shown below:

WRAP UP
I hope you found this post beneficial – Thanks for reading 🙂

Photo by Markus Spiske on Unsplash