Matt How

Matt How's Blog

Extracting Data from Salesforce using SSIS

This blog looks at a couple of methods of extracting data from Salesforce and gives a demo of how to output tables and queries into flat files.

 

Salesforce is HUGE! How do I find what I’m looking for?

Thankfully SFDC make it easy for us to find the tables that custom objects as they are suffixed with “__c” (e.g. “Assessment_Questions__c”). This convention carries through to Column level as well, meaning that when a Salesforce standard table is used, such as SFDCs Event table, any custom columns are also suffixed with “__c”.

 

Can I use SQL?

Salesforce utilises a modified version of SQL called SOQL (Salesforce Object Query Language). There are some Keyword similarities such as starting a query with “SELECT” and calling a table with “FROM” but if you want to limit a query result to a fixed number of rows you need to add “LIMIT n” to the end of the query string, not the start!

Additionally, SFDC provide some handy functions to make SOQL slightly easier on us developers. I have listed a few that I used below but a full documentation site is available here - https://help.salesforce.com/articleView?id=customize_functions.htm&type=0

Function

Usage

Example

LAST_YEAR

Provides a data range from Jan 1st of the previous year to Dec 31st of the previous year.

SELECT AccountId FROM Customers WHERE CreatedDate > LAST_YEAR

LAST_N_MONTHS: n

Provides a data range from the last day of the previous month and continues for the past n months.

SELECT Id FROM Account WHERE CreatedDate > LAST_N_MONTHS:12

BLANKVALUE

Similar to ISNULL. Will provide a substitute value if the field is null

SELECT BLANKVALUE(AddressLine2,”N/A”)

FROM…

 

How do I connect to Salesforce?

In order to access the Salesforce system, you will need a username, password and security token. From my research there are two viable methods for connecting to Salesforce using SSIS which are using an API component or using a linked server. Both have their pros and cons but generally I have found the API route to be more efficient. There are a number of 3rd Party providers around and I have listed a few of these below.

Provider

Comments

Link

C – Data (API)

 

http://www.cdata.com/drivers/salesforce/ssis/

Task Factory (API)

Forces you to down load all 50 components. Can use SSIS variables and SOQL

http://pragmaticworks.com/Products/Task-Factory/Free-Trial

Kingsway Soft (API)

Simple interface and can use SSIS variables. Auto generates SOQL. Cant edit error output from main component

http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-salesforce/download

DB – Amp (Linked Server)

Allows you to use OLE DB Connection manager

http://forceamp.com/trial.htm

 

You can use these steps below to access SFDC and start retrieving data.

1.      Download an SFDC SSIS component. For this demo I have used the Kingsway Soft component available from the link above.

 

2.      Run through the install wizard and once complete, open Visual Studio.

3.      Create a new SSIS project and add a Data Flow Task. Rename this to Kingsway Soft Demo.

 

clip_image002[12]

 

4.      Go into the data flow and right click in the toolbox, hit “Refresh Toolbox”. Now you should see 2 new components called “Salesforce Destination” and “Salesforce Source”.

 

clip_image005[12]

 

5.      Drag a Salesforce Source onto the design surface and double click to edit the properties. Drop down in “Connection Manager” and select “New”. Fill out the details required using the below connection info.

 

Username: mih@adatis.co.uk

Password: S4lesforce

Security Token: oKar72dhKcBAWlq0i4M0RF7ua 

 

clip_image007[12]

 

6.      Test the connection, and click “OK” once it has connected successfully. From the component properties screen use the Source Type to select “Object Query”. Add this SOQL statement into the text editor below. Click “OK”.

 

clip_image009[12]

TIP!! – If you select “Object” from the Source Type drop down and choose your table you can then switch the source type back to “ObjectQuery” and the full SOQL statement to retrieve all columns will be generated automatically.

 

7.      Add a flat file destination and configure the connection manager. Enable the data viewer between your SFDC component and the flat file destination and preview the data that is being extracted from SFDC.

 

clip_image011[12]

 

8.      From here can then repeat the process to build up a package that will bring down all the files that you might need for your solution.  

There are additional tools such as Salesforce Workbench that will allow developers greater access to the underlying solution but these will be covered in a different blog. If this solution does or doesn’t work for you then let me know!

Comments (1) -

  • Callum Green

    1/19/2017 9:54:10 AM | Reply

    Great blog.  Really easy to follow examples and definitely taught me something!

Loading