SSIS Salesforce DataFlow Source and Destination Components
dotConnect for Salesforce provides advanced Devart Salesforce Source and Devart Salesforce Destination components along with Salesforce Connection Manager for Data Flows in SQL Server Integration Services (SSIS) projects.
SSIS Salesforce Source Component
Devart Salesforce Source component is a data flow component that queries data from the Salesforce.com (or Database.com) cloud database and makes it available for other DataFlow components.
SQL for Querying Data
Our Salesforce Source component allows querying data with a subset of SQL-92. The local SQL engine of dotConnect for Salesforce allows you to query Salesforce Leads, Contacts, Opportunities, Accounts and other objects using complex WHERE conditions, JOINs, etc. - all the SQL benefits, not available with SOQL.
Advanced Visual Editor
Devart Salesforce Source provides a powerful editor dialog box that allows you to tweak it visually.
This editor dialog box displays all available Salesforce objects in the Salesforce Objects tree. You can also view object properties in this tree and the metadata of the objects and properties in the Properties box. To ease query creation, you can drag a Salesforce object from the Salesforce Objects tree to the Edit Query box, and the query to the corresponding Salesforce object will be built automatically. You can also drag an object to already created query in Edit Query, and the name of this object will be added to the query text.
SSIS System Variables
Devart Salesforce Source editor also displays SQL Server Integration Services (SSIS) system variables, saving your time, since you don't need to search their names in the documentation any more. They can also be dragged to the query editor to insert their names to the query.
Batch Size and Deleted Records
In this editor you can also specify the batch size for the Devart Salesforce Source to achieve best performance for the DataFlow operation. You can also specify whether to include deleted records to the query.
Devart Salesforce Source editor also allows you to quickly preview the data, returned by the specified query to make sure that you have entered it correctly.
SSIS Salesforce Destination Component
Devart Salesforce Destination component is a data flow component that loads data into an object of the Salesforce.com (or Database.com) cloud database.
Native Bulk Protocol Support
Our Salesforce Destination component uses Salesforce native bulk protocol to load data, thus providing high perfomance data loading. It supports the whole set of data load operations: insert, update, delete, and upsert.
Per-row Error Processing
Devart Salesforce Destination provides an advanced error processing capabilities. It can return a set of columns, containing original loaded columns, record IDs for the records, that are inserted successfully, error code, error columns, and error description for the columns, that caused an error. Thus errors can be processed for each row that caused an error, and you can get the Salesforce IDs of records, that were inserted successfully.
Inserting Related Objects
With Devart Salesforce Destination you have two ways of inserting related objects. The first one is the usual way of using external id fields. For this, you need to add an external id field to the parent Salesforce object and use it to load the primary key values of the loaded parent data to it. Then child objects may refer to this external id field.
However this way is not always convenient because it requires database modification (adding an external id field). Devart Salesforce Destination provides a new unique way to load related objects: using cached ids. You don't need to modify database when using this way. Devart Salesforce Destination inserts parent data first and caches the IDs of inserted rows. Then, when inserting child data, it uses these cached IDs to create references.
Devart Salesforce Connection Manager
Our Salesforce Connection Manager allows you to setup a connection to the Salesforce.com or Database.com online database.
The availability of Salesforce system fields is controlled by the Salesforce Connection Manager. Just set the SystemObjects connection property in its editor to make such fields as "ISDELETED", "CREATEDBYID", "CREATEDDATE", etc. available for Devart Salesforce Sources.
Connection manager also caches Salesforce metadata, that is used in Devart Salesforce Destination and Devart Salesforce Source. With this metadata cached you can select Salesforce objects and their properties visually in the editors of Salesforce Source and Salesforce Destination instead of typing their names manually. In the Salesforce Connection Manager editor you can refresh this metadata if necessary.