SSIS Salesforce ODBC sources and targets for SQL Server Integration Services

Default Blog Top Image
by Sumit Sarkar Posted on December 19, 2013

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.
More from the author

Related Tags

Related Articles

Top 5 Reasons to Use DataDirect with Salesforce
Customers pick Progress DataDirect for Salesforce connectivity because of its security, performance, high availability and more.
Progress DataDirect Achieves Google Cloud Ready—AlloyDB Designation
Progress DataDirect’s Drivers for Google AlloyDB offer a high-performing, secure and reliable connectivity solution for JDBC applications to access data in AlloyDB.
Progress DataDirect Now Connects to Denodo
Progress DataDirect has added Denodo, a data virtualization software platform, to its catalog of connectors.
Prefooter Dots
Subscribe Icon

Latest Stories in Your Inbox

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

Loading animation