SSIS Salesforce ODBC sources and targets for SQL Server Integration Services

SSIS Salesforce ODBC sources and targets for SQL Server Integration Services

December 19, 2013 0 Comments

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.

Why are shops moving data into Salesforce with SQL access?

  • Salesforce Native Reporting for external data
  • Data access via SOQL/APEX from force.com apps
  • Build cloud data marts to consolidate on premise RDBMS which is possible thanks to Progress DataDirect Connect for ODBC and JDBC drivers that expose the platform as a relational database.
  • On-premise Data Replication to the cloud to leverage unused capacity

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.

Getting started loading data into SFDC using SSIS

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".

ssis-sfdc1

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".

ssis-sfdc2

4. Create a new connection manager using the Provider, .NET Provider\ODBC Data Provider using your Salesforce login and test the connection.

ssis-sfdc3

 

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.

ssis-sfdc4

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.

ssis-sfdc5 7. Run the package, green is good! ssis-sfdc6

 

How do you get instant access to on-premise systems from Salesforce?

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.

It's all about the data connectivity

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 Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments
Comments are disabled in preview mode.
Topics
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
232x131_resourceimage
Getting Ahead of the Hybrid Data Curve
Read More
 
570x321__Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Read More
 
232-132_How to Simplify Application Usage & Deployment with Microapps
How to Simplify Application Usage & Deployment with Microapps
Read More