Is ETL/script the best way to create SQL database from SF?

Question

At our organization we would like to set up a SQL copy of our Salesforce instance for two reasons:

1) the app we are using in Salesforce is shutting down (Common Ground) and we we want to keep a usable archive

2) we would like to run reports that integrate with out other SQL databases.

We do not require real-time data and we want data to flow one-way: Salesforce -> SQL.

We wrote a ruby script which takes the .zip file of Salesforce's Data Export and iterates over the .csv files inside and creates SQL tables from each of them. However, it has proven difficult to recreate the foreign key relationships in SQL.

For ETL, we have a license for Clover ETL which we have used for some time and we are contemplating the idea of replacing the script with a massive Clover graph. This option would be a bit ugly as Clover doesn't appear to like processing a multitude of CSV files in different formats.

As I search I am not finding evidence that others use the Salesforce weekly data export for creating a SQL database and most mirror with something like DBAmp, Capstorm, Informatica, etc. Is it a bad idea for us to try and create our SQL database from data exports? Should we instead be purchasing a Salesforce mirroring tool? We are non-profit and free always is accepted.

EvaG_01 · Answer

If you are lack of time and resources, take a look at 3rd party ETL tools that can significantly speed up the process. I had a positive experience with Skyvia, another one is dbSync: http://appexchange.salesforce.com/appxSearchKeywordResults?keywords=sql%20server&searchType=simpleSearch

Answered by EvaG_01 on January 25, 2021

David · Answer

Well, I would suggest going with an ETL tool. The usefulness of ETL class of tech is much broader these days than what people her suggest. They play a role of data integration technology which allows both data extract/export but also ingest. And these days when businesses need to connect all data together the need for data extract from SF today may turn into a need to pump data back to SF later.
Look, for example, at this BLOG post which outlines that: http://blog.cloveretl.com/integrating-legacy-systems-with-salesforce.

In short - unless what you need is really just a replica of your data (but what for ?) then go for some more generic tech for data exchange - ETL / DataIntegration. There is a good introductory site for this: http://dataintegration.info

Cate Amos · Answer

ETL is a good way to go forward. But I suggest you to go for replication as ETL is not the best tool if data processing is huge and time is of essence.

Since Replication tools are comparatively expensive, you can opt for replication tools if your data is huge and if the cost of it is affordable.

user21582 · Answer

For your requirement i.e to sync from Salesforce to SQL Database, you can go with either a replication or an ETL tool without having weekly reports created. It is time consuming, complicated and also involves a lot of manual work.

You can go with replication tools available in Salesforce App Exchange. But on the contrary it might be a bit costly.

ETL deals mainly with pulling data out of multiple relational tables, understanding the meaning and relationship between the tables, combining, merging, or joining that data, and augmenting it with data from their sources.

Is ETL/script the best way to create SQL database from SF?

4 Answers

Add your own answers!

Ask a Question