SSIS Salesforce ODBC sources and targets for SQL Server Integration Services

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



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 ProviderODBC 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!



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.

Continue reading...