Connect to Salesforce Data as a Linked Server

  • dataintegration

    Mr or Mrs. 500

    Points: 510

    Comments posted to this topic are about the item Connect to Salesforce Data as a Linked Server

  • SQL-DBA

    Hall of Fame

    Points: 3004

    So basically you need to buy the SF ODBC driver to do this, right?

    http://www.cdata.com/drivers/salesforce/order/odbc/

  • Sam Lachterman

    SSC Veteran

    Points: 217

    Yes, which is completely ignored by the author. The website for cdata doesn't even list the price for the product with SQL Server remoting (you have to request a quote), so it can't be cheap.

    Subtle content marketing?

  • Robert Sterbal

    SSChampion

    Points: 10988

    I think this is what Steve was referring to about the gray line between content and marketing.

    Standard

    Professional

    Enterprise / Cloud / OEM

    For use with desktop clients like Excel, Access, Word,etc.

    $99

    (with subscription)

    Professional

    Enterprise / Cloud / OEM

    For use with Web apps, BI, Analytics, ETL, server tools.

    $499

    (with subscription)

    Enterprise / Cloud / OEM

    For BI, ETL, Analytics Tool Vendors, Cloud and Linked Server deployments.

    Request Quote

    412-977-3526 call/text

  • stevefromOZ

    SSC-Forever

    Points: 43646

    So basically the 'article' is an install guide/set up instructions for a very specific driver? We use DBAmp for the same purpose and the provide a nice guide but I won't be publishing it here 🙂

    Steve.

  • dataintegration

    Mr or Mrs. 500

    Points: 510

    I may be mistaken, but I believe that DBAmp allow you to replicate salesforce data to a local instance of SQL server, correct? Replication/sync is a different problem entirely.

    This solution is different - it offers the ability to create a SQL server linked server (https://msdn.microsoft.com/en-us/library/ms188279.aspx?f=255&MSPPError=-2147217396) with live access to salesforce data (no local replication). Basically it makes salesforce data look like SQL (or MySQL), without doing any kind of local copying etc.

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Absolutely it lets you do that. *But* it also works effectively like an OLEDB driver, so you can easily create a linked server (or multiples, one for PROd, sandboxes etc) that is then usable within SQL. To add to that, the folks from DB Amp even give you some added functionality n stored procs and an external executable, basically allowing for use of the SF Bulk API's (which believe me, if you've ever tried to do any type opf update or insert with Salesforce, you want to be using the bulk API's).

    So i guess I'm saying yeah, it can be used strictly for replication, but is that all it can do? No, not at all.

    Steve.

  • Damir Khamitov

    SSC Journeyman

    Points: 84

    As I understand Eric shows how to connect to a standalone Salesforce database server.

    Is there a way to create a linked server to "Salesforce as a Service" database at https://login.salesforce.com ?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the information.

  • Sam Lachterman

    SSC Veteran

    Points: 217

    As a customer, DbAmp makes a great product and has great support.

  • dataintegration

    Mr or Mrs. 500

    Points: 510

    Hi Steve,

    Yes, I know what you mean about the Bulk API - the CData driver uses it internally (and transparently) as well.

    One thing that might not be obvious about the article - the linked server feature is common across all of the CData ODBC drivers. So, while this article focuses on Salesforce, the same linked-server capabilities can also be used for NetSuite, DynamicsCRM (on-prem & online), QuickBooks, SharePoint, SugarCRM, etc.

  • tnpich

    Ten Centuries

    Points: 1191

    Does this require the API available from the SalesForce Enterprise edition or is independent of that?

  • stevefromOZ

    SSC-Forever

    Points: 43646

    doesn't require anything local other than the driver (whether that be cdata or dbamp or whoever). these interface with the API, but that's in the cloud.

    Steve.

  • AD's

    SSC Veteran

    Points: 218

    Hi,

    I thought of doing a POC to test the speed of CDATA ODBC driver from SSIS.

    Here is some analysis (please note it is on non-prod environment.)

    I used it in SSIS for fetching 8 column from salesforce (Account) table to SQL database.

    It took around 8 minutes for inserting 828,000 records in SQL table from SF table.

    All columns are string/datetime/bool.

    Is it normal speed or am I missing something?

    Thanks

  • Scott Coleman

    One Orange Chip

    Points: 27442

    We use DBAmp, and one of the reasons we chose it was the licensing model. You pay for each SalesForce production instance, not per SQL Server. So we can pay once and access our Salesforce data from as many SQL servers as we like, plus all of the sandboxes (of the same production org). All access by any developer or application goes through a SQL Server linked server, so there's no extra charge per developer or app deployment.

    Access to the bulk API is another great feature. We can download a copy of every table nightly without worrying about hitting any transaction count limits.

    But to provide connectivity to mobile apps you would have to publish a web service that went through your SQL Servers. If that was your goal you might prefer the CData royalty-free distribution model that would let mobile apps go straight to Salesforce.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply