How to read Salesforce Data in SSIS (SOQL query Examples)

In our previous post, we saw how to export SQL data to Salesforce. In this post, we will see how to use SSIS Salesforce Source connector to read data from Salesforce.com and load into SQL Server. Salesforce source supports Table mode and SOQL Query mode to read data from Salesforce.

Salesforce Video Tutorial 

How to get Salesforce SecurityToken

The first thing you will need to do to sync Salesforce with SQL Server is to get the Salesforce Security Token. You will need it for Salesforce Source Connector or Salesforce Destination to connect to your Salesforce account:

1. Log in to your account using https://login.salesforce.com.
2. On the top right corner, press on your profile picture and click Settings:
   

   Getting Salesforce Security Token for use in Salesforce Connection Manager

3. On the top left corner, under My Personal Information select Reset My Security Token menu item:
   

   Resetting Salesforce Security Token

4. In the main window area click Reset Security Token button:
   

   Resetting Salesforce Security Token

5. Check your email for a new Security Token:
   

   Getting new Salesforce Security Token

 

Reading data from Salesforce using Table Mode

Here is how you can read data from Salesforce in SSIS by simple table mode.

1. Download and Install SSIS PowerPack
2. Open SSIS Package Designer.
3. Goto Data Flow Tab
4. Drag ZS Salesforce Source and drop on the designer
5. Double-click to edit it
6. Create a new salesforce connection by clicking New.
7. Configure your salesforce connection as below. 
   
    
   SSIS Salesforce Connection Manager UI (Used with Salesforce Source, Salesforce Destination, Salesforce API Task, JSON Source and XML Source)
   
   
8. Select Salesforce Table from the drop-down.
9. Click Preview data as below to confirm
   SSIS Salesforce Source - Table mode (get data from Salesforce.com object)
   
   
10. Goto columns tab and check/uncheck columns you want to output.
    
11. That's it now connect your Salesforce source to Destination like SQL Server and run the package.
    
    

Reading data from Salesforce using SOQL Query mode.

Now let's look at few examples of Using SOQL Query mode to read data from Salesforce. Use same steps as previous section except AccessMode should be set to Query.

 
SSIS Salesforce Source - Query mode (query data using SOQL language) 

Now lets look at few examples of Salesforce Query Language.

Using SOQL Query - Basic Query

This example shows how to write simple SOQL query (Salesforce Object Query Language). It uses WHERE clause, ORDER BY and LIMIT clause. Using limit clause it will return only 10 records at maximum.

SOQL is similar to database SQL query language but much simpler and many features you use in database query may not be supported in SOQL (Such as JOIN clause not supported)

For more information about SOQL query syntax check below links : 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm

SELECT Id, LastName, FirstName 
FROM Contact
Where LastName!='Smith'
ORDER BY LastName, FirstName
LIMIT 10

SOQL Query - Parent fields (Child-to-Parent / Many-to-One)

This example shows how to get field value from related parent object using dot operator.

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Id,FirstName, LastName, Account.Name FROM Contact

SOQL Query - Child records (Parent-to-Child / One-to-Many)

This example shows how to get child records for selected parent records (e.g. All contacts for specified accounts).

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Account.Id,Account.Name
    , (SELECT Contact.FirstName, Contact.LastName, Contact.Email FROM Account.Contacts) 
FROM Account

SOQL Query - Child Query with Order By and LIMIT

This example shows how to use child query and limit number or child records for each parent row.

SELECT Name, 
    (SELECT FirstName, LastName FROM Contacts ORDER BY CreatedDate LIMIT 5) 
FROM Account
Where Name='ZappySys'

SOQL Query - Object and field alias

This example shows how to alias Table name or field name in SOQL queries. SOQL has very limited support for alias. You can alias Table name in any query but field alias is only allowed in Aggregation query only (Query with at least one aggregation function).

For more information about Alias in SOQL query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_alias.htm?search_text=Alias
For more information about Alias in Aggregation query check this link: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_alias.htm?search_text=Alias

SELECT c.Id, c.LastName, a.Name 
FROM Contact c,c.Account a
LIMIT 10

SOQL Query - GROUP BY / Aggregate function

This example shows how to use GROUP BY clause along with optional HAVING clause. When you use Aggregate function in SQL Query you can also use alias for field (This is the only time alias can be used). Child query cannot be used when Aggregate functions are used. Refer help file to learn more about other consideration.

For more information about GRPOUP BY check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby.htm

SELECT LeadSource, COUNT(Name) Cnt
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

SOQL Query with DateTime / Use of variable

This example shows how to use variable placeholders anywhere in query to make it dynamic. You can use inbuilt Date Literals (e.g. TODAY, YESTERDAY, LAST_N_DAYS:365). By default when variable with DateTime type is used then its automatically converted to UTC datetime format before sending query to server. If you want to query by UTC Date only (without time portion from variable) then use TO_UTC_DATE rather than TO_UTC_DATETIME format specifier (e.g. SELECT Id, FirstName FROM Contact WHERE CreatedDate < {{System::ContainerStartTime,TO_UTC_DATE}} ). To convert UTC date variable to Local Date use TO_LOCAL_DATE or TO_LOCAL_DATETIME

For more information about query datetime field check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm?search_text=date
For more information about variable placeholder usage check this link : https://zappysys.com/onlinehelp/ssis-powerpack/scr/ssis-format-specifiers.htm

SELECT Id, LastName, FirstName, Email, Account.Name 
FROM Contact
Where LastModifiedDate < {{System::ContainerStartTime,TO_UTC_DATETIME}} AND CreatedDate < YESTERDAY
LIMIT 10

SOQL Query - IN clause with Subquery

This example shows how to use sub query result for IN clause.
For more information check this link: 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm

SELECT Id, Name 
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'a%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )

SOQL Query - Use of special characters (i.e Escape sequence)

This example shows how to use reserved special characters inside string literals using an escape sequence. Anytime you have reserved character in string then use slash (\) in front of character e.g. Sam's Farm would be Sam\'s Farm.
For more information check below links: 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_reservedcharacters.htm

SELECT Id FROM Account WHERE Name = 'Sam\'s Farm'

 

 

 

See Also

• SSIS Salesforce Destination
• SSIS Salesforce API Task
• SSIS Salesforce Connection Manager
• SSIS JSON Source (With Salesforce Connection Support)
• SSIS XML Source (With Salesforce Connection Support)

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/