+ Start a Discussion
rcraven1rcraven1 

Data Loader Batching to SQL Server DB

Hi,
 
Hopefully this is the right place to post data extraction questions?
My task is to be able to automate the export of salesforce.com object tables into a MS SQL Server (currently 2005) database tables for backups and adhoc local reporting.  In addition, I need to automate the import process for a nightly sync between a few local information tables and salesforce. 
 
My requirements are:
 
- Process should be fully automated
- Process should be able to execute daily (nice to have incremental rather than full copy for export)
- On success or failure of extraction, some individuals should be notified
 
Currently, I've been trying to use Data Loader version 7, which now has the functionality to connect to databases through a JDBC connector.  However, the examples given are for connecting to Oricale, and I'm not sure what provider / connection string I need to use to connect to MS SQL Server? 
 
For my requirements, I heard about dbAmp and was wondering whether it's a better fit for what I'm doing? 
 
Your comments are more than appreciated,
Many thanks,
Rob
forceAMPforceAMP

Over 120 companies are using DBAmp to build replicate table copies. One big difference is that DBAmp uses the bulk loading interface to load data into the tables, making it faster with SQL Server than Data loader with SQL Server.

Also, DBAmp has a incremental refresh capablity that keeps you from having to download the entire table again tomorrow.

Bill Emerson

DBAmp Developer

SF EngineersSF Engineers

Hi,,

 

I am using Data Loader 9.0, to fetch data from SF. I need a code to access the SF data through Data Loader and store it on my SQL Server, which would be updated automatically every one hour.

Any reference materials or suggestions on how to accomplish this is highly appreciated.

 

Thanks in advance.

rcravenrcraven
DBAmp has one security issue.  Let me know if this is not the case, but doesn't DBAmp need to be installed directly on the database box and needs direct access to the internet?  This is a major security concern for a production environment.
 
I've recently reviewed Scribe as an alternative however, it seems overkill for something simple like this.
rcravenrcraven

[SoapBox] You would think that there would be better documentation with this kind of upgrade of the Dataloader?  That said, I don't think I can do any better...

You have to dive into the examples installed with the DataLoader.  If you didn't change the install path it should be located here:

C:\Program Files\salesforce.com\AppExchange Data Loader 8.0\samples

You will need to use "encrypt.bat" in \bin to give you a hashed value of your password, this will be placed into the "sfdc.password" value when you configure the "process-conf.xml" file. 

Note: I believe that there is another way to do this by placing the hashed value into the key file and then updating the following config line in "process-conf.xml". 
<entry key="process.encryptionKeyFile" value="c:\dataloader\samples\conf\sample.key"/>
If you are not going to use the key file then just delete this line from the "process-conf.xml" file, and only use the sfdc.password value and pass the hash value into it.

The important thing to note is that you need to configure the "process-conf.xml" file.  The bean id value is what you need to pass to parameter 2 of the "process.bat" file (you will use another bat file to call "process" and pass in the parameters ea. separated by a space).  Where you call process, pass the path for where "process-conf.xml" resides, and then pass in the bean id.

Example:

Echo off
process c:\Salesforce_Backup\conf csvAccountExtract


*When this parameter is pass in the "config.properies" file is ignored and the "process-conf.xml" file is used.

Notes regarding batch files:
 - Use the "Pause" command after the process call to see what is happening
 - Long file names which included spaces need to be surrounded in double quotes.

Hope this helps!

forceAMPforceAMP
DBAmp uses standard https to communicate with salesforce. If you can login to salesforce from the server, you are set.
COZYROCCOZYROC

The most flexible and affordable solution on the market is COZYROC SSIS+ library. It is a third-party extension library for SQL Server Integration Services (SSIS) and includes Salesforce adapters and many other complimentary technologies. The pricing starts at $399.95/Year and the product doesn't require programming skills.

Osvald MarkusOsvald Markus
As an alternative to all services that were mentioned before, try Skyvia (https://skyvia.com/data-integration/integrate-salesforce-sql-server). It is a cloud-based service for totally no-coding integration where you can easily set up the update (night-based in your case) with no extra efforts, incremental updates included.