Following my third Dreamforce presentation on SQL data connectivity to Salesforce, I'm hearing more projects loading data into the Salesforce Platform. The sales ecosystem at Salesforce continues to grow and shifting data gravity to the platform for sales operations. With this shift, it's making more and more sense to move data into the platform.
Once the data is loaded into the platform, shops are leveraging the same DataDirect ODBC drivers to perform tasks such as real-time SQL data federation using SQL Server Linked Server and even some self service data connectivity using MS Access against Salesforce. While this all works seamlessly, there is a lot of work that went into making the driver efficiently write data into the platform.
Note: Screenshots are for SQL Server 2008 R2. Similar steps apply to SQL Server 2012.
1. Download trial of both the 32-bit and 64-bit DataDirect Connect and Connect64 XE for ODBC Salesforce drivers.
2. Create a 32-bit and 64-bit ODBC System DSN to Salesforce.com with the SAME name (use shortcut from driver program group to load corresponding 32-bit or 64-bit ODBC Administrator). I used the name "Salesforce" as an example in screenshot below and specified a location to write the local schema files in the "Database" property to write to C:\ with file prefix "sumit.sandbox2".
3. If you're using SSIS 2012 or higher and inserting large numbers of records, check the "Enable Bulk Load" box on the bulk tab of the ODBC data source to transparently leverage Salesforce.com's BULK API for optimal insertion. Large means "a few hundred thousand records" per Salesforce's "Best Practices for Deployments with Large Data Volumes".
4. Create a new connection manager using the Provider, .NET Provider\ODBC Data Provider using your Salesforce login and test the connection.
5. Create new ADO.NET destination and select the Connection manager configured for Salesforce. For SSIS 2012, there are native ODBC sources and destinations which are pretty cool when you upgrade.
6. Map your source and destination columns. Note: The driver supports CREATE TABLE operations against Salesforce to create objects and fields in case you're using replication or SQL tasks.
7. Run the package, green is good!
That is not possible today with Salesforce. However, DataDirect Cloud connectivity is being leveraged by RollBase for an amazing data driven aPaaS experience providing instant access to on-premise data, as well as Salesforce.com.
1. Download free 15 day trial of the DataDirect Connect XE for ODBC Salesforce drivers to load your data into the Salesforce Platform.
2. Or call 1-800-876-3101 to speak live with a Systems Engineer to learn more about our thoughts on data connectivity and aPaaS.
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
Copyright © 2019 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.