6

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.

Improve this question
2
  • "ETL" is a generic term meaning "Extract/Transform/Load" That term pretty much defines what all scripts that you'd run to extract data from a database and put it into another. That said, your question is a bit unclear. Are you asking specifically about Clover ETL in your title, or do you want to know general best practice on this?
    – Charles Koppelman
    May 8, 2015 at 17:38
  • I'm asking if anyone else creates SQL databases from Salesforce Data Exports or if they use tools which grab data through the Salesforce API and sync directly. I know some ETL tools (like Clover) don't like the idea of processing a folder full of CSV files that each have unique data formats.
    – kd7iwp
    May 8, 2015 at 18:48

2 Answers 2

Reset to default
0

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.

Improve this answer
1
  • 2
    Thanks for sharing. It makes sense to use the App Exchange ones, we will likely go with that.
    – kd7iwp
    Jun 30, 2015 at 16:06
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .