none
SQL and SalesForce RRS feed

  • Question

  • Hi guys,does anyone have experience in connecting data from SalesForce? Or anyway in gathering data from it? I am struggling to understand how I can populate my data warehouse with the data stored in SalesForce...

    Many Thanks

     
    • Edited by DIEGOCTN Tuesday, October 4, 2016 1:36 PM
    Tuesday, October 4, 2016 1:35 PM

Answers

  • Hi DIEGOCTN,

    You can download Salesforce.com ODBC Driver, use ODBC Administrator to create your Salesforce.com ODBC Driver data source, then retrieve Salesforce.com Data by using OPENDATASOURCE / OPENROWSET. There is an example for your reference.

    1.In SQL Server Management Studio, run these commands to enable the OPENDATASOURCE / OPENROWSET functions:

    EXEC sp_configure 'show advanced options', 1
    
    RECONFIGURE
    
    GO
    
    EXEC sp_configure 'ad hoc distributed queries', 1
    
    RECONFIGURE
    
    GO


    2.Run:

    SELECT * FROM OPENROWSET('MSDASQL', 'DSN=MY_SALESFORCE_ODBC_DATA_SOURCE;', 'select * from Account;')


    Another method is to retrieve Salesforce.com Data by using a Linked Server in SQL Server, then insert data from Salesforce.com into SQL Server table. Assume that the linked server is named SALESFORCE, the detailed T-SQL statements that transfer data from Account table in Salesforce.com to SQL Server are as follows.

    IF OBJECT_ID('dbo.Account', 'U') IS NOT NULL DROP TABLE dbo.Account;
    
    SELECT * INTO Account FROM OPENQUERY(SALESFORCE,'SELECT * FROM Account')

    Reference:
    http://www.easysoft.com/support/kb/kb01050.html

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support





    Wednesday, October 5, 2016 10:08 AM
    Moderator

All replies

  • Hi Diegoctn,

    The easiest way to move the data from Salesforce to a variety of targets is using Azure Data Factory (ADF).

    ADF now has a easy to use "Copy Wizard" that supports Salesforce (objects or reports). See Sreedhar's article for step by step instructions. https://azure.microsoft.com/en-us/documentation/articles/data-factory-copy-wizard/.

    To access the reports - Choose the "Use Query" option (vs. Existing Tables) in the Source section of the Wizard, and enter the following in the Query window {call "YourExistingSF_ReportName"}. Follow the Wizard to connect to the target of choice.

    You could also setup a scheduled task in Salesforce to the export of a report to a flat file, and then use SSIS to load the resulting flat file into a target database.

    Brian Walker - Microsoft Data Platform Solution Architect

    • Marked as answer by DIEGOCTN Wednesday, October 5, 2016 8:20 AM
    • Unmarked as answer by DIEGOCTN Wednesday, October 5, 2016 8:20 AM
    Tuesday, October 4, 2016 2:24 PM
  • Hi DIEGOCTN,

    You can download Salesforce.com ODBC Driver, use ODBC Administrator to create your Salesforce.com ODBC Driver data source, then retrieve Salesforce.com Data by using OPENDATASOURCE / OPENROWSET. There is an example for your reference.

    1.In SQL Server Management Studio, run these commands to enable the OPENDATASOURCE / OPENROWSET functions:

    EXEC sp_configure 'show advanced options', 1
    
    RECONFIGURE
    
    GO
    
    EXEC sp_configure 'ad hoc distributed queries', 1
    
    RECONFIGURE
    
    GO


    2.Run:

    SELECT * FROM OPENROWSET('MSDASQL', 'DSN=MY_SALESFORCE_ODBC_DATA_SOURCE;', 'select * from Account;')


    Another method is to retrieve Salesforce.com Data by using a Linked Server in SQL Server, then insert data from Salesforce.com into SQL Server table. Assume that the linked server is named SALESFORCE, the detailed T-SQL statements that transfer data from Account table in Salesforce.com to SQL Server are as follows.

    IF OBJECT_ID('dbo.Account', 'U') IS NOT NULL DROP TABLE dbo.Account;
    
    SELECT * INTO Account FROM OPENQUERY(SALESFORCE,'SELECT * FROM Account')

    Reference:
    http://www.easysoft.com/support/kb/kb01050.html

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support





    Wednesday, October 5, 2016 10:08 AM
    Moderator