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.