Category Archives: Technical

Downloading Salesforce Or Dynamics CRM Data To SQL Server (For Free)

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

Why I Don’t Use Online Backups

In the days when 100MB drives were the norm and Microsoft Office came on 43 floppy disks, in the process of formatting a floppy disk I somehow managed to format my work hard drive.  Computers were single drives then so I lost all of my data and operating system too.

Since then I keep copies of all my important data on USB drives which I keep in work, and bring home monthly to update, the process is described here. ¬†Ideally I’d have to two sets of drives so that the data and backups are never in the same location – I’ll get to that someday. ¬†So for now the backup consists of two drives which are roughly 4TB in total.

4TB might sound like a lot but I have a lot of photos, music and videos to backup.  Using a Canon DSLR regularly along with a GoPro style helmet cam means the storage of photos and music grows rapidly.

This is a breakdown of my usage as of today:
DiskUsage

After a bit of research online I came up with options Backblaze or CrashPlan¬†– they are both reasonably priced and have good reviews. ¬†Unfortunately, CrashPlan was putting an end to the Home Service, ¬†I’m assuming it wasn’t profitable enough for them.

This left me with Backblaze.  It sounded very good, $5 a month for 1 computer Рunlimited backup sizes, unlimited file sizes, and backups of external drives.

I signed up for a 30 day trial, and installed the PC client. ¬†The first thing that surprised me was that you can’t easily choose what to backup. ¬†By default it backs up everything, even your OS drive. ¬†The reason they give for is is that most users don’t want to decide what to backup! ¬†I had several very big folders on my drives which I didn’t want backed up, and managed to exclude them (though exclusion folder names apply to all drives).

After a few days it had only backed up about 10GB, which was a long way to go to get to a complete full backup set.  I wondered whether it was because my PC was connected to the ISP router over a wireless connection.  A connection speed test using a wireless connection and wired connection using a laptop came back with the same results Рmy upload speed was 10Mbps.

Checking the Backblaze client indicated that it was using all the bandwidth available to it, and backing up around 2GB per hour. ¬†Now, my PC is only switched on when I’m using it. ¬†A few years ago it would be switched on 24/7 when I was doing 3D rendering¬†but I don’t do that any more.

So I did a calculation of how long the backup would take to complete using my current bandwidth, if I left it on for 5 hours a day (which is more than I normally use it):
5hrs x 2GB = 10GB/day
2,836GB ÷ 10GB/day = 283 days = 40 weeks

So my first backup wouldn’t complete for 40 weeks! ¬†And this isn’t including the files that get added during that time. ¬†It’s at this point I decided it wasn’t the right time to move online. ¬†The way internet speeds are increasing I’ll have a much quicker upload in the future, and it may be worth paying extra for more bandwidth.

And that’s why I don’t use online backups.

Photo by Thomas Kvistholt

Backup Data to External Drives

Everyone should have a backup of their important data in case your main hard drive fails and everything’s lost forever. ¬†Oh, and it’s not if, it’s when your main hard drive fails. ¬†If the worst does happen you may be lucky and be able to retrieve some files from your drive but it’s hit and miss, and can be very expensive.

This lifehacker post discusses average life times for hard drives, and the average times are between a few months and five years.¬† I backup all important data to 2 external USB drives.¬† I’ll describe the process I use for each backup drive below.

First of all I put a file in the root directory of all the drives (main and backup) so that the backup procedure can test for the existence of drives before running.  The file format I use is Backup-E.TXT where E is the drive letter.

Secondly I create a batch file which does the copying process for each backup drive.
Backup Text File

(text of batch file here)

Lines 7-8 check for the existence of the main and backup drives, and if not found will jump to Line 23 and no backup will occur.
Line 23 is the command that actually backs up the data using the excellent Robocopy which can be installed from Microsoft Technet.  It only copies updated files so the whole process is much quicker than copying the full data again.

If the backup runs successfully it will show the Start and Finish times so that you can see how long the backup took.  I normally start it and leave it for an hour or two.

And there we have it, all data backed up. ¬†Don’t forget to take the drives to an offsite location. ¬†I keep mine at my work office but it could be a family members or friends house.

Photo by Patrick Lindenberg