Content

• Video Tutorial
• Step-By-Step
• Properties
• SOQL Query Examples
• Setting UI
• See Also
• References

Video Tutorial

Step-By-Step

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
4. Double click on the Data Flow task to see Data Flow designer surface.
5. Here, In Visual Studio, drag and drop the ZS SalesForce Source in the design surface.
   
6. Now, we need SalesForce Connection. Click here to create SalesForce Connection.

How to read data from SalesForce table using SalesForce Source(Query Mode).

1. Here, we are getting data using Query Mode with Dynamic expression value.
2. Lets, create a Variable with correct DataType and Value, use following image.
   
3. Double click on SalesForce Source to configure it.
4. Select connection we have created before, set AccessMode to Query, Enter Following Query and Replace Variables if differ name. Click here for more SOQL Query Examples.

   SELECT Id, Name, BillingCountry, NumberOfEmployees
   FROM Account
   WHERE Name LIKE '{{User::Name}}' 
   and BillingCountry = '{{User::BillingCountry}}'
   and NumberOfEmployees >= {{User::NumberOfEmployees}}
   

   
5. Click on Preview button to see Data Preview.
6. Click on OK button to save configure setting UI.
7. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
   
8. Now single click on the SalesForce Source, once you see blue arrow from source ... connect it to Trash Destination.
9. Double click on ZS Trash Destination to Configure it.
   
10. Click on OK button to save Trash Destination configure setting UI.
11. Thats all, just run or execute your package and see data.
    

How to read data from SalesForce table using SalesForce Source(Table Mode).

1. Double click on SalesForce Source to configure it.
2. Select connection we have created before, set AccessMode to Table and Select Tables from table dropdown list.
   
3. Click on Preview button to see Data Preview.
4. Click on OK button to save configure setting UI.
5. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
   
6. Now single click on the SalesForce Source, once you see blue arrow from source ... connect it to Trash Destination.
7. Double click on ZS Trash Destination to Configure it.
   
8. Click on OK button to save Trash Destination configure setting UI.
9. Thats all, just run or execute your package and see data.
   

Properties

SOQL Query Examples

SOQL Query - Basic Query

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)

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

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

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

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

SOQL Query - Object and field alias

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

SOQL Query - GROUP BY / Aggregate function

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

SOQL Query with DateTime / Use of variable

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

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)

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

Setting UI

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)

References

• How to export SQL Server data to Salesforce in SSIS
• How to read Salesforce data and load into SQL Server (SOQL Query Examples)
• How to set Lookup Fields for Salesforce Insert / Update / Upsert Operations