+ Start a Discussion
BillbayBillbay 

How to integrate: retrieve data from a MSSQL server db on premise and update my salesforce app?

Hi,

I have never done an integration before. Building my first interface to a business application running on-premise with a MS SQL Server database. This app contains data that I need to bring into my salesforce app.

I am looking to build an interface/integration such that data is retrieved on a schedule from the MS SQL db and have it updated onto my Salesforce app.

Can someone point me to a go-to place where I can figure this out?

Thanks,
BillBay
Tad Aalgaard 3Tad Aalgaard 3
Check out the following.

https://developer.salesforce.com/page/Data_Loader

https://developer.salesforce.com/page/Using_Data_Loader_from_the_command_line
Jayant JadhavJayant Jadhav
@Billbay,

At this point I can suggest you to use CLI data loader to sync your MS SQL db data to SFDC system. This process is useful  only if you are sync data from MS SQL db data to SFDC system and not bi-directional.

Plz let me know if you are looking this kind of solution. I will provide you more details on this.
BillbayBillbay
Hi Jayant,

Please coud you share more information. I only need a uni-directional flow from MS SQL to SFDC. However, would be great to have this automated as a scheduled run as opposed to triggering the process manually.

Thanks!
 
Jayant JadhavJayant Jadhav
@Billbay,

Plz start with the data loader document provided by SFDC. It is good to go through whole document but I will point out page numbers for you based on your required.

Link : https://na10.salesforce.com/help/pdfs/en/salesforce_data_loader.pdf

Page Numbers and topics as below:

 22,  topic : Data Loader Process Configuration Parameters.
 30,  topic : Data Loader Command Line Operations.
 31-35,  topic : Configuring Database Access
 37, chapter : Command Line Quick Start

When you start with CLI data loader configuration, you will relialize that, creating system related password and keys will take lot of time and if you make small mistake during this process you will rceive exception messages. For this situation I like to sugest one more tool which is used for CLI configuration. Plz use this link for Cliq tool.(http://code.google.com/p/dataloadercliq/).

For automated process you can use window's scheduler. Create .sh file for all process execution and use window's scheduler to schedule at specific time.

Below is the sample code for .sh file which I have implemented for SQL server to SFDC direct connection using JDBC driver.
 
SET DLPATH="C:\Program Files (x86)\salesforce.com\Data Loader"
SET DLCONF="C:\Program Files (x86)\salesforce.com\Data Loader\cliq_process\caseExport\config"
SET DLDATA="C:\Program Files (x86)\salesforce.com\Data Loader\cliq_process\caseExport\write"
SET DBDATA="C:\Program Files (x86)\salesforce.com\Data Loader\bin\sqljdbc4.jar"
call %DLPATH%\Java\bin\java.exe -cp %DLPATH%\*;%DBDATA% -Dsalesforce.config.dir=%DLCONF% com.salesforce.dataloader.process.ProcessRunner process.name=caseExport > "C:\Program Files (x86)\salesforce.com\Data Loader\cliq_process\caseExport\config\log.txt"

 
Jayant JadhavJayant Jadhav
@Billbay,

Let me know above steps works for you, If yes then mark this as best answer. This will help others to solve similar issues.
James CrennanJames Crennan
Hey all - I'm chairman at DreamFactory (http://www.dreamfactory.com). We have a secure, cloud hosted, HTTPS proxy built for API integration with behind-the-firewall data stores like SQL server. DreamFactory is OSS and you can kick the tires or reach out and our team will be happy to get your data flowing.