Overview

Devart SSIS Data Flow Components are a set of components for SQL Server Integration Services (SSIS) packages. These components allow loading data from and to various sources in SSIS Data Flows.

ExpandedToggleIcon Components

For each of the supported data sources Devart SSIS Data Flow Components includes a Source component, a Destination component and a Lookup component. Devart SSIS Data Flow Components also include their own Connection Managers for each source. To quickly get started with Devart SSIS Data Flow Components, see the Getting Started section.

Source

Devart Source components allow extracting data from the corresponding sources and passing the data to other Data Flow components. Devart Source components provide convenient editors which allow you to quickly create a query to the data source. For cloud data sources, Devart Source components offer powerful SQL support for querying data.

Destination

Devart Destination components allow loading data to the corresponding sources. Devart Destination components have source-specific optimizations for high-performance data loading for some sources.

Lookup

Devart Lookup components allow joining the input data with the data from other tables in the corresponding data source.

Connection Manager

Devart Connection Manager allow connecting other Devart SSIS Data Flow Components to the corresponding data sources.

ExpandedToggleIcon Data Source Support

Devart SSIS Data Flow Components are offered for the following data sources:

Databases

· Oracle
· MySQL
· PostgreSQL
· DB2

Cloud Data Warehouses

· Azure SQL Data Warehouse
· Amazon Redshift
· Google BigQuery

Cloud CRMs

· Salesforce
· Dynamics CRM
· Zoho CRM
· SugarCRM

Cloud Marketing Applications

· MailChimp
· Salesforce Marketing Cloud

Ecommerce Applications

· BigCommerce
· Magento

Cloud Accounting Applications

· QuickBooks
· FreshBooks

You can find more details in the Compatibility article.

ExpandedToggleIcon See Also

Installation

© 2015 - 2018 Devart


What's New

What's New in Devart SSIS Data Flow Components 1.7

· Marketo is supported
· The ConsentLookup type is supported for the Zoho CRM source

What's New in Devart SSIS Data Flow Components 1.6

· SQL Server Data Tools (SSDT) in Visual Studio 2017 is supported
· Azure SQL Data Warehouse is supported
· Google BigQuery is supported
· Amazon Redshift is supported
· The UseBulkApi parameter, specifying whether to use Bulk API or SOAP API, is added to the Devart Salesforce Destination component (Salesforce)
· The behaviour is changed: now Column Mappings are not reset after Refresh in the Devart Salesforce Destination editor (Salesforce)
· The behaviour is changed: now the Devart Sugar Destination component uses Bulk API (SugarCRM)

What's New in Devart SSIS Data Flow Components 1.5

· Microsoft SQL Server 2016 is supported in SSIS DataFlow components
· The Conditions, PriceAdjuster_Type, PriceAdjuster_Value, WeightAdjuster_Type and WeightAdjuster_Value fields are added to the ProductRules entity (BigCommerce)
· The "Include Deleted Records" option is added to Devart Salesforce Source Editor (Salesforce)
· The new Assignment Rule Id property is implemented in Devart Salesforce Destination (Salesforce)
· The "Use Assignment Rules" connection string parameter for using default Assignment Rule is added to the Salesforce connection (Salesforce)
· The ParallelBatchProcessing option is added to the Devart Salesforce Destination component for specifying the mode used for upload: Parallel or Serial (Salesforce)
· The SSIS Data Flow Components version info is added to the License Info window
· A not sensitive Host parameter is added to SSIS DevartSalesforce connection manager (Salesforce)
· The behaviour of the product installer is changed: now it checks whether another version of the product is installed
· The behaviour is changed: the Password and SecurityToken parameters are now sensitive (Salesforce)


Compatibility

ExpandedToggleIcon Supported Databases

Devart SSIS Data Flow Components are compatible with the following database servers:

Provider | Versions
Oracle | Oracle Servers: 12c, 11g, 10g, 9i, 8i, 8.0 and 7.3, including Personal and Express editions Oracle Clients: 12c, 11g, TimesTen 11g Release 1, TimesTen 11g Release 2, 10g, 9i, 8i and 8.0, both 32-bit and 64-bit
MySQL | MySQL 5.7, 5.6, 5.5, 5.4, 5.1, 5.0, and 4.1, including Embedded servers (starting with 4.1), 64-bit MySQL servers, Percona, and MariaDB
PostgreSQL | PostgreSQL 7.1, 7.2, 7.3, 7.4, 8.0, 8.1, 8.2, 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, EnterpriseDB, Pervasive Postgres SQL servers
DB2 | DB2 for LUW 9.x and higher

ExpandedToggleIcon Supported Cloud Data Warehouses

The following Cloud CRMs are supported by Devart SSIS Data Flow Components:

Amazon Redshift
Azure SQL Data Warehouse

Azure SQL Data Warehouse

Google BigQuery

ExpandedToggleIcon Supported Cloud CRMs

The following Cloud CRMs are supported by Devart SSIS Data Flow Components:

Salesforce

Dynamics CRM

SugarCRM

Zoho CRM

ExpandedToggleIcon Supported Cloud Accounting Applications

The following cloud accounting applications are supported by Devart SSIS Data Flow Components:

QuickBooks

FreshBooks

ExpandedToggleIcon Supported Cloud Marketing Applications

The following cloud marketing applications are supported by Devart SSIS Data Flow Components:

MailChimp
Salesforce Marketing Cloud (ExactTarget)

Salesforce Marketing Cloud (ExactTarget)

Marketo

ExpandedToggleIcon Supported Ecommerce Applications

The following ecommerce applications are supported by Devart SSIS Data Flow Components:

Bigcommerce

Magento

ExpandedToggleIcon See Also

Data Source Connection


Installation and Requirements

ExpandedToggleIcon Requirements

Prior to installing Devart SSIS Data Flow Components, make sure that the following requirements are met:

· SQL Server Integration Services 2008, 2012, 2014, 2016, or 2017 (SSIS component) is installed. (SQL Server Integration Services 2008 are not supported by Devart SSIS Data Flow Components for Dynamics CRM)
· SQL Server Data Tools or BIDS 2008 (Business Intelligence Development Studio) are installed. (BIDS 2008 is not supported by Devart SSIS Data Flow Components for Dynamics CRM)
· Internet connection is available.
· Devart SSIS Data Flow Components for DB2 requires IBM DB2 .NET Data Provider to be installed on your computer.

To verify that SQL Server Integration Services component and Microsoft SQL Server Integration Services Designer are installed, run SQL Server Data Tools from the Start menu -> Microsoft SQL Server , then on the File menu click New -> Project. This will display the New Project window; locate the Installed templates area and make sure that it contains the Business Intelligence -> Integration Services item.

ExpandedToggleIcon Installation

To install Devart SSIS Data Flow Components, download the installer from Devart Downloads page.

Run the downloaded installer and follow the instructions in the wizard.

In BIDS 2008 (installed with SQL Server 2008), Devart SSIS Data Flow Components are not added to the Toolbox by default. To install our Data Flow components to the Toolbox, perform the following steps:

1. Create an Integration Services Project.
2. Right-click in the Toolbox floating window to call its context menu and select Choose Items. .Net Framework Components tab of the Choose Toolbox Items dialog box is displayed; switch to its SSIS Data Flow Items tab.
3. Locate and select the corresponding Devart Source, Lookup, and Destination items, and click OK. Now these components are added to the toolbox.
4. Drag them to the corresponding toolbox sections for further convenience.

In Microsoft SQL Server Integration Services Designer (installed with SQL Server 2012, 2014, 2016, or 2017), our components are added to the toolbox automatically when you install Devart SSIS Data Flow Components. They are added to the Common tab of the Toolbox.


Ordering and Registration

Devart SSIS Data Flow Components are licensed, not sold. Please read carefully our end-user license agreement (EULA) before using the product. You can find the EULA in the installation folder, which is by default

\Program Files\Devart\SSIS Data Flow\License.rtf,

or in the Start menu (Start\Programs\Devart SSIS Data Flow Components\License).

ExpandedToggleIcon Ordering

After the installation, Devart SSIS Data Flow Components can be evaluated for free during the trial period. After the trial period is over you need to purchase a license for add-ins you want to continue using and register them.

Devart SSIS Data Flow Components can be ordered on the corresponding Ordering page of our website.

One license allows you to install Devart SSIS Data Flow Components on one or more development workstations, provided that Devart SSIS Data Flow Components are used by the licensed developer only,

ExpandedToggleIcon Registration

After you purchase a license for one or more Devart SSIS Data Flow Components, you will get an email with activation keys for the purchased components.

Note that by default installer installs Devart SSIS Data Flow Components for all the supported data sources on a trial basis with the trial keys. However, Devart SSIS Data Flow Components are ordered and registered for each data source separately or in bundles.

You can register Devart SSIS Data Flow Components in two ways.

1. You can copy a key and paste it in the Devart Connection Manager Editor dialog box for the corresponding data source. For this:
a. Copy the activation key.
b. Right-click in the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed:

Add-SSIS-Connection-Manager
c. Select the connection manager for the data source, for which you purchased a license. If you purchased one of SSIS Integration Bundles, select connection manager for any data source that is included to the bundle.
d. In the Connection manager type list click the corresponding connection type ("Devart" followed by the data source name) and click Add. The corresponding Connection Manager Editor dialog box is displayed.
e. Click License Info. The License Information dialog box is displayed.
f. Click Activate. The License Key dialog box is displayed.
g. Paste your activation key to the License Key box. If necessary, enter the user name to the User name box.
h. Click OK and close the dialog boxes.
i. Repeat this procedure for all the purchased Devart SSIS Data Flow Components.
2. You can create the files with the keys manually and place them to the License folder in the Devart SSIS Data Flow Components ProgramData folder. By default it is ProgramData\Devart\SSIS Data Flow\License\ folder.


Getting Started

Devart SSIS Data Flow Components are used in SSIS data flow tasks. To use them you need to create an Integration Services project first. See the instructions in MSDN:

· for SQL Server 2012
· for SQL Server 2014
· for SQL Server 2016

After this you need to add a Data Flow task. See the instructions in MSDN:

· for SQL Server 2012
· for SQL Server 2014
· for SQL Server 2016

After adding a Data Flow task you can configure your data source connections with Devart connection managers, add Devart Source and Destination components to your data flow task and configure data flows between them.

See how to:

· Configure Connections
· Use Devart Source Components
· Use Devart Destination Components
· Use Devart Lookup Components

You can also read our Tutorials to see the examples of using Devart SSIS Data Flow Components.


Configuring Connections

Devart SSIS Data Flow Components provide a number of connection manager components for SSIS data flows. To add a Devart connection manager to an Integration Services project, perform the following steps:

1. Right-click anywhere in the Connection Managers area, and then click New Connection.
2. Select the Devart connection manager for the necessary data source in the Add SSIS Connection Manager dialog box.
3. After this, in the opened connection manager editor dialog box, specify the necessary connection settings on the General tab.
4. Optionally switch to other tabs and set advanced parameters.
5. Click OK to save the connection settings.

You can the information about the connection parameters in the corresponding topics of the Data Source Connection Settings section.


Devart Source Components

Devart Source components are data flow components that make data from the corresponding data sources available to other data flow components. Each Devart Source component provides a convenient editor dialog box, which allows you to easily define which data to load.

Note that Devart Source components support only the corresponding Devart connection managers.

ExpandedToggleIcon How to use Devart Source Components

To add a Devart Source component to a Data Flow task, perform the following steps:

1. Drag the corresponding Devart Source component from the SSIS Toolbox window to the Data Flow diagram.
2. Double-click the added component to open its editor.
3. In the <Data Source> Connection list (where <Data Source> is the corresponding data source) select the corresponding Devart Connection Manager or create a new one.
4. In the Query box enter an SQL query that selects the required data. You can also drag the corresponding table from the <Data Source> Objects tree to the Query box.
5. Click OK.

After this you may add other Data Flow components and connect Devart Source data flow path to them.

ExpandedToggleIcon Devart Source Component Editor

This dialog box has the following components:

· The <Data Source> Connection list allows selecting a connection manager for the Source component.
o Batch Size - Available for Devart Salesforce Source. Specifies the number of records to retrieve from Salesforce as one batch.
o Include Deleted Records - Available for Devart Salesforce Source. Specifies whether to include records with IsDeleted = true to the query results.
· The <Data Source> Objects (for cloud sources) or Tables (for databases) area allows you to view the existing tables, expand their nodes to view columns, and drag them from this area to the Query area.
o Show all schemas - Available for databases. Enables displaying all the objects of the selected connection; otherwise, only the objects of the default schema for the connection are displayed.
o Refresh Objects - Updates the list of data source tables.
· The Variables area allows you to view the system and user variables grouped in the corresponding nodes, and if necessary, drag them to the Query area.
· The Query area is intended for creating a SQL query to be executed against the data source. You can drag the tables and variables to this area, or type the query yourself.
o Preview Data - Opens the Preview Data window that displays the query execution result.
· The Properties area displays properties of the currently selected table, column or variable.

ExpandedToggleIcon SQL Support

Devart Source components provide advanced SQL support for cloud sources even when a source does not support SQL natively. Note that you should use SQLite compliant SQL, not the Transact-SQL.


Devart Destination Components

Devart Destination components are designed for loading data into the corresponding data sources.

Note that Devart Destination components support only the corresponding Devart connection managers.

ExpandedToggleIcon How to use Devart Destination Components

To add a Devart Destination component to a Data Flow task, perform the following steps:

1. Drag the corresponding Devart Destination component from the SSIS Toolbox window to the Data Flow diagram.
2. Connect the necessary data flow path to it.
3. Double-click the added component to open its editor.
4. On the Connection Managers tab select the corresponding Devart Connection Manager or create a new one.
5. Click the Component Properties tab.
6. In the <Data Source> Object (for cloud sources) or <Data Source> Table (for databases) property type or select the target object or table (where <Data Source> is the corresponding data source).
7. In the Action property select the required DML operation to apply to the data source. Supported DML operations are described below.
8. Optionally configure other custom properties of the component. Custom properties are source-specific. They are described in the Custom Properties Reference section. For example,
9. Click the Column Mappings tab.
10. Map the input columns to the corresponding destination columns. The columns with the same names in input and destination are mapped automatically.
11. Click OK.

ExpandedToggleIcon Supported DML Operations

Devart Destination components support all the three basic DML operations: insert, update, and delete. Destination components for some sources support additional operations.

· Insert - This is the most common operation. See a tutorial on using it.
· Update - Devart Destination component in the Update mode enables updating data in a data source. See a tutorial on using it. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - This operation is supported for Salesforce. When executing an upsert, records are inserted if they do not exist in the Salesforce object; in case records do exist, then data update is performed. This action is based on the External ID field.
In order to use the Upsert option, the External ID field must be added to the Salesforce object, and the local storage must contain the corresponding unique column (usually this is a primary key in the database table). When configuring the component, you must map this unique column to the External ID field in the target Salesforce object.
· BulkInsert - This operation is supported for relational databases except DB2 and cloud data warehouses. It uses database-specific optimization techniques to speed up data loading. You can see the description of these techniques below.
· HardDelete - This operation is supported for Salesforce. It completely deletes data rows without the possibility to restore them. In Salesforce, the Delete operation actually keeps the records and just sets their IsDeleted property to True, the HardDelete operation actually deletes the data.

ExpandedToggleIcon Source-specific Optimization Techniques

Devart Destination components for relational database can use certain database-specific techniques to speed up data loading:

· Devart Oracle Destination uses direct path load interface to speed up data loading in the OCI connection mode. In the Direct connection mode it uses Array Binding - a feature that allows executing several INSERT SQL statements with the different parameters at once. Oracle server is accessed only once per a batch of inserted records, and that increases the speed a lot. See details on the Direct and OCI modes here.
· Devart MySQL Destination uses INSERT statements that insert data by several rows at the same time.
· Devart PostgreSQL Destination uses the COPY command.
· Devart AzureDWH Destination uses PolyBase technique to speed up data loading. It uploads data as CSV files to Azure Blob Storage and tells Azure SQL Data Warehouse to import data. After the data is imported, the CSV files are deleted.
· Devart Redshift Destination uploads data as CSV files to an Amazon S3 bucket and then tells Redshift to import data using the COPY command. After the data is imported, the CSV files are deleted.
· Devart BigQuery Destination also uploads data as CSV files to a Google Cloud Storage bucket and tells BigQuery to import data. After the data is imported, the CSV files are deleted.

ExpandedToggleIcon Outputs

Devart Destination components support error outputs, which allow you to manage how the component treats row-level errors in both incoming and outgoing data.

The error output includes the column set taking part in the data flow and additional columns, they are ErrorCode that identifies the error, ErrorColumn that contains the lineage identifier of the error column and ErrorDescription that provides a detailed description of the error.

The ErrorCode column can accept the following values:

ErrorCode | Description
-1 | Means that the record is valid.
>0 | Means that the record causes an error.

Additionally, when the primary key values are automatically generated for the target table, Devart Destination component retrieves the generated values and adds them to its output columns.


Devart Lookup Components

Devart Lookup components are designed for joining the input data with the data from objects in the corresponding data source.

Note that Devart Lookup components support only the corresponding Devart connection managers.

ExpandedToggleIcon How to use Devart Lookup Components

To add a Devart Lookup component to a Data Flow task, perform the following steps:

1. Drag the corresponding Devart Lookup component from the SSIS Toolbox window to the Data Flow diagram.
2. Connect the necessary data flow path to it.
3. Double-click the added component to open its editor.
4. On the Connection Managers tab select the corresponding Devart Connection Manager or create a new one.
5. In the Lookup Object (for cloud sources) or Lookup Table (for databases) list select the lookup object.
6. In the Lookup Columns list select check boxes for the columns to add to the Lookup match output.
7. In the Referential Constraint grid select the corresponding input columns and lookup key columns to match.
8. Click OK.
9. Connect the necessary lookup component outputs to other components.

Devart Lookup components provide the following outputs:

· Lookup Match Output - processes rows that have at least one matched row in the lookup object/table.
· Lookup No Match Output - processes rows that do not match to any of lookup object/table rows.
· Lookup Error Output - by default this output is not used. You can set the NoMatchAsError property to true in order to send rows that do not match to any of lookup object/table rows to this output.

ExpandedToggleIcon Performance Optimizations

Devart Lookup components use the following techniques to provide best performance for matching rows:

Batch Check

Devart Lookup components can check several input rows for matches at once with a single query to reduce the number of queries. You can set the maximal number of rows to check in a batch in the BatchSize custom property of the Lookup component.

Please note that assigning bigger values to this property increases the query size, and for some sources too large queries may cause errors. In case of errors reduce the value of this property.

This property and this functionality are not supported for Zoho CRM.

Advanced Caching

Devart Lookup components cache the queried rows from the lookup object/table, so if multiple input rows have the same lookup key values, Devart Lookup components uses cached rows instead of querying the source again. You can set the number of rows to cache in the CacheSize custom property of the Lookup component.


Tutorials

This section provides tutorials for the Devart SSIS Data Flow Components.

Devart Source Tutorial - Loading Data from Salesforce
This tutorial demonstrates how to use Devart Salesforce Source component to extract data from Salesforce.

Devart Destination Tutorial - Loading Data to Salesforce
This tutorial demonstrates how to use Devart Salesforce Destination component to load data to Salesforce.

Devart Lookup Tutorial - Inserting and Updating Contact Data
This tutorial demonstrates how to use Devart Salesforce Lookup component to perform an UPSERT to the Contacts, when an existing contact to update is determined not by a provided ID, but by another field, for example, Email.


Devart Source Tutorial - Loading Data from Salesforce

This tutorial requires SQL Server Integration Services 2012 or higher with SQL Server Data Tools installed. You need to add them yourself as described in Installation and Requirements.

This tutorial demonstrates how to use Devart Salesforce Source component to extract data from Salesforce.

ExpandedToggleIcon Adding Data Flow Components

For the purpose of this tutorial, we create a Devart Salesforce Source and an OLE DB Destination.

1. After you have created a new Integration Services project, in the Control Flow tab, drag Data Flow Task from the SSIS Toolbox window and to the design area. The Data Flow Task is created.
2. Switch to the Data Flow tab, go to the Toolbox docking window, in the Data Flow Sources list select Devart Salesforce Source and drag it to the design area.
3. From the SSIS Toolbox window drag OLE DB Destination to the design area.

ExpandedToggleIcon Creating Connections

For this example we need two connections - one for the Devart Salesforce Source, the other one for the OLE DB Destination. To create a connection for Salesforce, do the following:

1. Right-click in the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed:

Add-SSIS-Connection-Manager
2. In the Connection manager type list click DevartSalesforce and click Add. The Devart Salesforce Connection Manager Editor dialog box is displayed.
3. Specify the User name (email address) and Password, and Security Token you have received when registering at the Salesforce website. If necessary, click Advanced and specify other connection parameters.

Connection-Manager-Editor-Salesforce

The connection for the Salesforce database is now created.

To create a connection for the OLE DB Destination we do the following:

1. Right-click the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed.
2. In the Connection manager type list select OLEDB and click Add. The Configure OLE DB Connection Manager dialog box is displayed.
3. Click New. The Connection Manager dialog box is displayed; fill in the connection settings as required, e.g. as follows:

OLE-DB-Connection
4. Click OK.
5. The Configure OLE DB Connection Manager dialog box is displayed again, click OK.

The connection for the OLE DB Source is now created.

ExpandedToggleIcon Configuring DataFlow Components

After the two required connections are created, we can proceed to customizing the data flow components.

In this example, we will also show how to create and use a variable. To create a variable perform the following steps:

1. Right-click the design surface and then click Variables.
2. The Variables window is displayed. On the Variables window toolbar click the Add Variable button.
3. A new variable is created and displayed in the window. Rename it as required, then in the Properties window set the variable properties.

In this example we call the variable 'AccountName' and set the following properties:

ValueType = String
Value = GenePoint

other properties are left set to their default values.

Now we proceed to customizing the Devart Salesforce Source.

1. Double-click the Devart Salesforce Source data flow object.
2. The Devart Salesforce Source Editor dialog box is displayed. Select the created Devart Salesforce Connection Manager in the Salesforce Connection list.
3. Drag the required table (in our example we use the Account table) from the Salesforce Objects area to the Query area:

Source-Editor-Salesforce-1
4. Then we add a WHERE clause and drag the variable we have previously created from the User node of the Variables area to the Query area.
5. We may also edit the query in such a way that only the necessary columns are selected. Thus, we get the following query:

Source-Editor-Salesforce-2
6. Click the Preview Data button to view the result of the query execution. The Preview Data window is displayed:

Salesforce-Source-Preview-Data
7. Click Close to return to the Devart Salesforce Source Editor dialog box.
8. In the editor dialog box click OK.

If all options are set correctly, the red marker on the data flow object disappears. Connect the two data flow objects with a Data Flow path and proceed to customizing the OLE DB Destination:

1. Right-click the OLE DB Destination data flow object. The Connection Manager tab of the OLE DB Destination Editor dialog box is displayed.

OLE-DB-Destination-1
2. Specify the following settings in this tab:
3. Select the required connection in the OLE DB connection manager drop-down list;
4. Select Table or View in the Data access mode list;
5. Select the required database object name in the Name of the table or the view drop-down list.
6. Switch to the Mappings tab.
7. SSIS automatically maps columns having the same names. If necessary, specify mapping between the source and destination columns either using the Input Columns drop-down lists, or just by drawing lines between the corresponding columns:
8. Switch to the Error Output tab.

OLE-DB-Destination-3

This tab allows you to specify in what way component failure should be treated; the following options are available: Fail component, Redirect row, Ignore failure.
9. After all options are set, click OK.

Make sure that the red marker is absent on the OLE DB Destination data flow object.

Right-click the design area and then click Execute Task. The green marks on the data flow objects indicate that the data flow process is completed successfully:


Devart Destination Tutorial - Loading Data to Salesforce

This tutorial requires SQL Server Integration Services 2012 or higher with SQL Server Data Tools installed.

This tutorial demonstrates how to use Devart Salesforce Destination component to load data to Salesforce.

ExpandedToggleIcon Adding DataFlow Components

For the purpose of this tutorial, we create a Devart Salesforce Source and an OLE DB Destination.

1. After you have created a new Integration Services project, in the Control Flow tab, drag Data Flow Task from the SSIS Toolbox window and to the design area. The Data Flow Task is created.
2. Switch to the Data Flow tab, go to the Toolbox docking window, in the Data Flow Sources list select Devart Salesforce Source and drag it to the design area.
3. From the SSIS Toolbox window drag OLE DB Source and Devart Salesforce Destination.
4. Connect the two data flow objects with a Data Flow path.

ExpandedToggleIcon Creating Connections

For this example we need two connections - one for the Devart Salesforce Source, the other one for the OLE DB Destination. To create a connection for Salesforce, do the following:

1. Right-click in the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed:

Add-SSIS-Connection-Manager
2. In the Connection manager type list click DevartSalesforce and click Add. The Devart Salesforce Connection Manager Editor dialog box is displayed.
3. Specify the User name (email address) and Password, and Security Token you have received when registering at the Salesforce website. If necessary, click Advanced and specify other connection parameters.

Connection-Manager-Editor-Salesforce

The connection for the Salesforce database is now created.

To create a connection for the OLE DB Destination we do the following:

1. Right-click the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed.
2. In the Connection manager type list select OLEDB and click Add. The Configure OLE DB Connection Manager dialog box is displayed.
3. Click New. The Connection Manager dialog box is displayed; fill in the connection settings as required, e.g. as follows:

OLE-DB-Connection
4. Click OK.
5. The Configure OLE DB Connection Manager dialog box is displayed again, click OK.

The connection for the OLE DB Source is now created.

ExpandedToggleIcon Configuring DataFlow Components

After the two required connections are created, we can proceed to customizing the data flow components.

1. In the Data Flow design area, double-click the OLE DB Source data flow item.
2. The Connection Manager tab of the OLE DB Source Editor dialog box is displayed.

OLE-DB-Source-Editor-1

Specify the following settings in this tab:
o Select the required connection in the OLE DB connection manager drop-down list;
o Select the Data access mode, e.g. SQL command;
o Enter the required query in the SQL command text box.
3. Switch to the Columns tab.

OLE-DB-Source-Editor-2
4. Configure the properties used by the data flow to obtain data from the OLE DB typer:
5. Select the required columns in the Available External Columns lists;
6. Configure Output Column names, if necessary.
7. Switch to the Error Output tab.

OLE-DB-Source-Editor-3

This tab allows you to specify in what way component failure should be treated; the following options are available: Fail component, Redirect row, Ignore failure. After all options are set, click OK.

If all options are set correctly, the red marker on the data flow object disappears.

Proceed to customizing the Devart Salesforce Destination data flow object.

1. Double-click the Devart Salesforce Destination in the design area. The Connection Managers tab of the Advanced Editor for Devart Salesforce Destination dialog box is displayed.
2. Select the Devart Salesforce Connection Manager in the Connection Manager drop-down list and switch to the Component Properties tab.

Destination-Editor-Salesforce-1
3. Here you can customize the data flow component properties. Pay attention to Custom properties. In addition to the Insert operation, Devart Salesforce Destination supports Update, Delete, and Upsert operation. Select the necessary object to insert data to in the ObjectName property, for example, Product2.

Destination-Editor-Salesforce-2
4. Switch to the Column Mappings tab.
5. SSIS automatically maps columns having the same names. If necessary, specify mapping between the source and destination columns either using the Input Columns drop-down lists, or just by drawing lines between the corresponding columns:

Destination-Editor-Salesforce-3
6. After mapping is defined, switch to the Input and Output Properties tab.

Destination-Editor-Salesforce-4
7. In this tab you can review input and output properties of the data flow component. Click OK.

Make sure that the red marker is absent on the Devart Salesforce Destination data flow object.

Right-click the design area and then click Execute Task. The green marks on the data flow objects indicate that the data flow process is completed successfully:


Devart Lookup Tutorial - Inserting and Updating Contact Data

This tutorial requires SQL Server Integration Services 2012 or higher with SQL Server Data Tools installed.

This tutorial demonstrates how to use Devart Salesforce Lookup component to perform an UPSERT to the Contacts, when an existing contact to update is determined not by a provided ID, but by another field, for example, Email. The Contacts data to upsert is stored in a CSV file. This file must have at least two columns - LastName and Email.

ExpandedToggleIcon Adding DataFlow Components

We will import Salesforce Contacts from a CSV file using a Flat File Source, determine whether there is a Contact in Salesforce with the same email using Devart Salesforce Lookup, and, depending on this, perform either insert or update to Salesforce with two Devart Salesforce Destinations.

1. After you have created a new Integration Services project, in the Control Flow tab, drag Data Flow Task from the SSIS Toolbox window and to the design area. The Data Flow Task is created.
2. Switch to the Data Flow tab, go to the Toolbox docking window, in the Data Flow Sources list select Devart Salesforce Source and drag it to the design area.
3. From the SSIS Toolbox window drag Flat File Source, Devart Salesforce Lookup, and two Devart Salesforce Destinations.

ExpandedToggleIcon Creating Connections

For this example we need two connections - one for the Devart Salesforce components, the other one for the Flat File Source. To create a connection for Salesforce, do the following:

1. Right-click in the Connection Managers pane and select New Connection from the shortcut menu. The Add SSIS Connection Manager dialog box is displayed:

Add-SSIS-Connection-Manager
2. In the Connection manager type list click DevartSalesforce and click Add. The Devart Salesforce Connection Manager Editor dialog box is displayed.
3. Specify the User name (email address) and Password, and Security Token you have received when registering at the Salesforce website. If necessary, click Advanced and specify other connection parameters.

Connection-Manager-Editor-Salesforce

The connection for the Salesforce database is now created.

To create a connection for the Flat File Source we do the following:

1. Right-click the Connection Managers pane and then click New Flat File Connection. The Flat File Connection Editor dialog box is displayed.
2. Click Browse and open the CSV file with the Contacts to import.
3. Specify the file Locale, Code page, and Format settings.

Flat-File-Connection-Manager-Editor-1
4. Click Columns.

Flat-File-Connection-Manager-Editor-2
5. In the Preview rows make sure that the data is displayed correctly. If necessary, specify the Row delimiter and Column delimiter characters or switch back to the General tab and reconfigure the Format settings. After the data is displayed correctly, click Advanced.
6. Click the Email column and make sure its DataType property is Unicode string [DT_WSTR]. If not, change it to Unicode string [DT_WSTR].

Flat-File-Connection-Manager-Editor-3
7. You may optionally switch to the Preview tab and preview the file data. Here you can set a number of initial Data rows to skip.

Flat-File-Connection-Manager-Editor-4
8. Click OK.

The connection for the Flat File Source is now created.

ExpandedToggleIcon Configuring DataFlow Components

After the two required connections are created, we can proceed to customizing the data flow components.

Configuring Flat File Source

1. In the Data Flow design area, double-click the Flat File Source data flow item.
2. The Connection Manager tab of the Flat File Source Editor dialog box is displayed.
3. Select the required connection in the Flat file connection manager drop-down list.
4. Select the Retain null values from the source as null values in the data flow check box in case there are empty values in the source CSV file.

Flat-File-Source-Editor-1
5. Switch to the Columns tab.
6. Select the columns to import in the Available External Columns lists.
7. Configure Output Column names, if necessary.

Flat-File-Source-Editor-2
8. Switch to the Error Output tab.

Flat-File-Source-Editor-3

This tab allows you to specify in what way component failure should be treated; the following options are available: Fail component, Redirect row, Ignore failure. After all options are set, click OK.

If all options are set correctly, the red marker on the data flow object disappears.

Configuring Devart Salesforce Lookup

Proceed to customizing the Devart Salesforce Lookup data flow object.

1. Connect the Flat File Source output to Devart Salesforce lookup.
2. Double-click the Devart Salesforce Lookup in the design area. Devart Salesforce Lookup Editor dialog box is displayed.
3. In the Connection list select the required Devart Salesforce Connection Manager.
4. In the Lookup Object list select Contact.
5. To perform an update operation, Devart Salesforce Destination requires an ID field to be mapped, so we need to get the ID values of the contacts to update. Select the ID check box in the Lookup Columns list.
6. In the first row of the Referential Constraint grid select the Email columns in both Input Columns and Key Columns columns.

Lookup-Editor-Salesforce
7. Click OK.
8. Connect Devart Salesforce Lookup output to one of the Devart Salesforce Destinations. Input Output Selection dialog box is displayed.

Input-Output-Selection
9. In the Output list select Lookup Match Output.
10. Connect Devart Salesforce Lookup output to another Devart Salesforce Destination. Lookup No Match Output is selected automatically.

If all options are set correctly, the red marker on the data flow object disappears.

Configuring Devart Salesforce Destinations

Proceed to customizing the Devart Salesforce Destination data flow objects.

1. Double-click the Devart Salesforce Destination to which the Lookup Match Output is connected in the design area. The Connection Managers tab of the Advanced Editor for Devart Salesforce Destination dialog box is displayed.
2. Select the Devart Salesforce Connection Manager in the Connection Manager drop-down list and switch to the Component Properties tab.

Destination-Editor-Salesforce-1
3. Here you can customize the data flow component properties. Select the Contact in the ObjectName property. In the Operation property select Update.

4. Switch to the Column Mappings tab.
5. SSIS automatically maps columns having the same names. If necessary, specify mapping between the source and destination columns either using the Input Columns drop-down lists, or just by drawing lines between the corresponding columns:

6. Double-click the Devart Salesforce Destination to which the Lookup No Match Output is connected in the design area. The Connection Managers tab of the Advanced Editor for Devart Salesforce Destination dialog box is displayed.
7. Select the Devart Salesforce Connection Manager in the Connection Manager drop-down list and switch to the Component Properties tab.
8. Here you can customize the data flow component properties. Select the Contact in the ObjectName property.

9. Switch to the Column Mappings tab.
10. SSIS automatically maps columns having the same names. If necessary, specify mapping between the source and destination columns either using the Input Columns drop-down lists, or just by drawing lines between the corresponding columns.

Make sure that the red marker is absent on both Devart Salesforce Destination data flow objects.

Right-click the design area and then click Execute Task. The green marks on the data flow objects indicate that the data flow process is completed successfully:


Data Source Connection Settings

The common basics of connecting to data sources are described in the Configuring Connections article. This section describes specific connection options for each data source supported by Devart SSIS Data Flow Components. Choose your data source to learn its specific connection parameters supported by Devart SSIS Data Flow Components.

ExpandedToggleIcon Database Servers

Oracle | MySQL
PostgreSQL | DB2

CollapsedToggleIcon Cloud Data Warehouses

Amazon Redshift | Azure SQL Data Warehouse Azure SQL Data Warehouse
Google BigQuery |

ExpandedToggleIcon Cloud CRMs

Salesforce | Dynamics CRM
Zoho CRM | SugarCRM

ExpandedToggleIcon Cloud Accounting Applications

QuickBooks

FreshBooks

ExpandedToggleIcon Cloud Marketing Applications

MailChimp | Salesforce Marketing Cloud (ExactTarget) Salesforce Marketing Cloud (ExactTarget)
Marketo |

ExpandedToggleIcon Ecommerce Applications

Bigcommerce

Magento


Oracle

ExpandedToggleIcon Connection Dialog

To connect to an Oracle database, required connection parameters must be set. There are two connection modes for Oracle: Direct and using Oracle Client.

Direct Mode

The Direct mode allows you to connect to Oracle database without Oracle Client software. It means thet you may have no Oracle Client software installed on your PC and connect to Oracle directly via TCP\IP. To enable this connection mode, select the Direct check box.

In the Direct mode, the following connection options are required:

1. Host - The name of TNS alias of Oracle database to which to connect.
2. SID - Unique name for an Oracle database instance;
3. Port - Number of a port to communicate with listener on the server. The default value is 1521;
4. User Id - The Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication);
5. Password - The password for the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication);
6. Connect mode - Allows to open a session with administrative privileges.

Oracle Client Mode

To use Oracle Client for connection to an Oracle database, you should have Oracle Client software installed on your PC. Clear the Direct check box to work with Oracle Client.

In this mode the SID and Port settings are not used, and you need to set the Oracle Home to use instead. Specify the Oracle Client you want to be used in the Home connection option.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

ClientId

Specifies the client identifier for the connection.

Connect Mode

Allows to open a session with administrative privileges SYSDBA or SYSOPER.

Connection Class

Connection class for Oracle's Database Resident Connection Pooling (DRCP). You need to set also OCI Session Pooling = true for using DRCP. DRCP is supported starting with Oracle 11g.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connection Timeout

Time (in seconds) to wait while trying to establish a connection before terminating the attempt and generating an error. A value of 0 indicates no limit. The default value is 15 seconds.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Describe Stored Procedures

This parameter is ignored. Do not use this parameter.

Direct

If true, Devart SSIS Data Flow Components can operate without an Oracle Client installed. The default value is false.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

Home

The Oracle Client that will be used.

Lob Block Size

Specifies the size of a block (in megabytes) to read/write CLOB or NCLOB data from/to database. This option is supported in OCI mode only. Applicable only for Unicode Oracle servers. Default value is 0, which means that the whole value is sent in one block. If you get errors when reading/writing huge LOB values, try setting this property to 8, it is the safest value.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Number Mappings

Sets the number mappings used for this connection. Each mapping is set as (OracleNumberType, FromPrecision, ToPrecision, SystemType), where:

- OracleNumberType is one of the values from OracleNumberType enumeration;

- FromPrecision and ToPrecision set the minimal and maximal precisions of the Oracle number type to map with this mapping;

- SystemType is a .NET type to which Oracle number type should be mapped.

Several mappings should be separated by commas and covered by a common parentheses. For example, this property may be set as:

Number Mappings = (Integer, 1, 1, System.Boolean)

or

Number Mappings = ((Integer, 1, 5, System.Int16), (FLOAT,12,15,System.Decimal), (NUMBER,7,9,System.Single))

Oci Session Pooling

If true, enables the OCI Session Pooling feature.

Oci Session Pool Allow Waiting

If true, new connections wait for an existing one to close if Max Size is reached; otherwise an exception is thrown.

Oci Session Pool Connection Lifetime

This attribute specifies the lifetime of the connection in seconds. Before the connection is placed back into the pool, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, the connection is closed and disposed. If this property value is 0, the connection lifetime is never checked.

Oci Session Pool Increment

Allows applications to set the next increment for sessions to be started if the current number of sessions is less than Max Size. The valid values are 1 and above.

Oci Session Pool Max Size

Specifies the maximum number of sessions that can be opened in the session pool. Once this value is reached, no more sessions are opened. The valid values are 1 and above.

Oci Session Pool Min Size

Specifies the minimum number of sessions in the session pool. This number of sessions are started initially. After this, sessions are opened only when necessary.

Oci Session Pool Password

If set, defines password for proxy user.

Oci Session Pool User Id

If set, defines user name for proxy user. Available only when OCI Session Pooling is enabled.

OraMts

If set to true, Oracle Services for Microsoft Transaction Server (OraMTS) is used for distributed transactions; otherwise OraMTS is not used.

Pass Parameters By Name

Pass parameters by name to the stored procedure calls. Default value is false.

Password

The password for the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication)

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

Number of a port to communicate with listener on the server to use in the Direct mode. The default value is 1521.

Proxy Password

The password for Oracle proxy authentication.

Proxy User Id

The proxy user id for Oracle proxy authentication

Server

The name of TNS alias of Oracle database to which to connect.

Service Name

Alias to an Oracle database instance (or many instances) to use in the Direct mode. The default value is empty string. When Service Name is specified and implies several database instances, add-in connects to the first instance provided by Oracle listener. Note that RAC is not supported in the Direct mode, and provider works only with this one database instance.

SID

Unique name for an Oracle database instance to use in the Direct mode. The default value is empty string.

Statement Cache Purge

Determines whether to clear the OCI statement cache when closing the connection or putting it to the pool. The default value is false.

Statement Cache Size

This attribute enables or disables statement caching. Its value specifies the maximum number of statements that can be cached for a connection. By default this attribute is set to 0 (disabled). Statement caching starts if this parameter is set to a value more than 0. It should not be more than MAX_OPEN_CURSORS parameter in an Oracle database.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. The default value is false.

Trim Fixed Char

Specifies whether to trim trailing spaces when reading data from fixed-length string data types (CHAR, NCHAR). It affects all command objects of this connection. Behavior of data reader depends on Trim Fixed Char value at the moment of opening data reader. The default value is true.

Unicode

Specifies whether the add-in uses UTF16 mode API calls. The default value is false.

User ID

The Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication).

Validate Connection

Specifies whether to validate connections that are being got from the pool.


MySQL

ExpandedToggleIcon Connection Dialog

The following connection options are required for connecting to MySQL:

1. Host - The IP address or DNS name of a MySQL server to which to connect.
2. Port - Number of a port to communicate with listener on the server. The default value is 3306;
3. User Id - The MySQL login account.
4. Password - The password for the MySQL login account.
5. Database - The name of the database to connect to.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Binary As Guid

Determines whether to treat BINARY(16) columns as guid. Default value is false.

Character Set

A character set used by the client. Actually, if this property is enabled, then the "SET NAMES <Charset>" query is executed on establishing a connection. Default value is an empty string. That means there is no action performed with charset on the client and on the server. You can set the Charset property value to 'auto'. In this case, the server asks for the charset and sets the appropriate charset on the client.

Client Interactive

Determines the inactivity timeout before the server breaks the connection. If true, the server breaks the connection after number of seconds specified in interactive_timeout sever variable, otherwise wait_timeout is used.

Compress

If true, enables transferred data compression. The default value is false.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Database

The name of the database.

Default Auth Plugin

The name of the authentication plugin. Applicable only when connecting through the MySQL client library version 5.5 or later.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Default Fetch All

If the FetchAll mode is enabled, all the querried data is retrieved from the server on execution.

Direct

If true, by default, allows not to use MySQL client library (libmysql.dll) to access MySQL server.

Disable Charset Sending

Enables or disables explicit charset resetting when a connection is taken from a pool.

Embedded

If true, allows to use Embedded MySQL server. The default value is false

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

FoundRows

Specifies whether the provider will return the number of rows matched by the WHERE condition of the UPDATE statement instead of the rows actually changed as the number of changed rows.

Host

The IP address or DNS name of a MySQL server to which to connect.

HTTP Base64

Specifies whether to encode data to MIME base64 format before sending them with the POST Request.

HTTP Content Length

Specifies the value of the Content-Length HTTP header parameter. This is the size of message body in bytes for both client and server.

HTTP Host

The host name or IP address of HTTP tunnel server.

HTTP Keep Alive

The timeout in seconds to send Keep-Alive HTTP packets.

HTTP Max Connection Age

The maximum connection age in seconds after which the tunnel will be closed. The default value is 300 seconds.

HTTP Password

The password for HTTP authorization.

HTTP Port

The port number of HTTP tunnel server.

HTTP Strict Content Length

Determines whether data padding is enabled when message size is smaller than ContentLength.

HTTP Url

The URL of the tunneling PHP script. For example, if the script is in the server root, the url can be the following: http://localhost/tunnel.php.

HTTP User

The user name for HTTP authorization.

Ignore Fractional Seconds

Specifies whether fractional parts of seconds in TIME, DATETIME, and TIMESTAMP values should be ignored.

Ignore Prepare

This parameter is ignored. Do not use this parameter.

Keep Alive

Specifies whether to send TCP keep-alive packets and the interval at which they are sent in seconds. Default value is 0, which means that the packets are not sent.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password for the account.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Ping Interval

The interval (in seconds) between pinging MySQL server and reopening the connection if required.

Pipe Name

The name of the pipe to use when connecting via named pipe (Protocol is set to Pipe).

Plugin Dir

The directory where the plugin is located. Applicable only when connecting through the MySQL client library version 5.5 or later.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

The port of MySQL database to which to connect. The default value is 3306.

Protocol

The type of the network protocol which will be used to access to MySQL server. The default value is Tcp. The following values can be used:

· Pipe - Named pipe protocol. Works only on Windows systems.
· Memory - Shared memory protocol. This type of the network protocol can be used to access MySQL server running on the same computer as SQL Server. Not supported in Direct mode.
· UnixSocket - Unix socket file connection to local server. In the connection string this value may be specified as "unixsocket", "unix socket", or "unix". When using this protocol, you should specify the unix socket file as the Host parameter.
· Ssh - SSH protocol.
· Ssl - SSL protocol.
· Http - HTTP tunneling protocol.
· HttpSsl - Secure SSL connection through the HTTP tunneling protocol.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Server Parameters

Specifies command-line argument for launching MySQL embedded server. Usually used to provide path to files used by server and path to folder where data files reside. For instance:

Server Parameters=\"--basedir=d:/servers/embedded/;--datadir=d:/servers/embedded/data/;\"

Sql Modes

SSH Authentication Type

Client authentication methods. Can include the following values:

· PublicKey - Public-key authentication will be used.
· Password - Password will be used for authentication.
· KeyboardInteractive - Keyboard-interactive authentication will be used.

SSH Cipher List

List of ciphers that client agrees to use. The following ciphers are available for SSH connections:

· 3DES
· Blowfish
· AES(128)

You can use ALL keyword to indicate whole set of ciphers. To exclude certain cipher from the set use "-" sign. For instance, "ALL-Blowfish" means that any cipher but Blowfish can be used.

SSH Host

The name or ip address of SSH server.

SSH Host Key

The location of the public key on the client side to verify the server host key during establishing connection.

SSH Passphrase

The client key passphrase.

SSH Password

User password on SSH server.

SSH Port

The number of the port on the SSH server to connect.

SSH Private Key

The location of the client private key.

SSH Strict Host Key Checking

Determines whether the host key is verified during establishing connection. The default value is false.

When this option is set to true, the server public key is verified. The verification is successfull if the server key is identical to the key, supplied by user. This key should be specified in the SSH Host Key parameter. If this property is not set or the keys are not equal, the connection is not established. You can get the public host key from system administrator.

SSH User

User id on SSH server.

SSL CA Cert

Location of authority certificate.

SSL Cert

Location of client certificate.

SSL Cipher List

List of ciphers that client agrees to use.

SSL Key

Location of user's private key.

Tiny As Boolean

Specifies whether to treat TINYINY(1) columns as boolean. Default value is false.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. The default value is false.

Unicode

If true, sets client charset to UTF8 and converts client data according to this charset. The default value is false.

User ID -or- User

The MySQL login account.

Validate Connection

Specifies whether to validate connections that are being got from the pool.


PostgreSQL
ExpandedToggleIcon Connection Dialog

The following connection options are required for connecting to PostgreSQL:

1. Host - The name of the database host to connect to.
2. Port - Number of a port to communicate with listener on the server. The default value is 5432;
3. User Id - The PostgreSQL login account.
4. Password - The password for the PostgreSQL login account.
5. Database - The name of the database to connect to.
6. Schema - The name of the schema to be used once a connection is opened. You can change it later if you need. By default it is schema 'Public'.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

AllowDateTimeOffset -or- Allow DateTimeOffset

Determines whether to use the DateTimeOffset type instead of DateTime when PgSqlDataReader returns a timestamptz value.

Character Set

A character set used by the client. Actually, if this property is enabled, then the "SET NAMES <Charset>" query is executed on establishing a connection. Default value is an empty string. That means there is no action performed with charset on the client and on the server.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connect Timeout -or- Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Database

The name of the database. If this parameter is not specified, it is assumed that name of the database is same as user name.

Default Command Timeout

The default time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Default Fetch All

This parameter is ignored. Do not use this parameter.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

Force IPv4

Host

The name or IP address of host of PostgreSQL database to which to connect.

Ignore Unnamed Parameters

Determines whether to treat the '?' character in the command text as an unnamed parameter.

Initial Schema

The name of the schema to be used once a connection is opened. You can change it later if you need. By default it is schema 'Public'.

Integrated Security

Determines whether to use a secure authentication with single sign-on (GSSAPI or SSPI, depending on the server).

Join Statement Notices

Determines whether all the notices, raised during the statement execution, will be returned together in one InfoMessage event after the statement execution, or each notice will be returned as a separate InfoMessage event.

Keep Alive

Specifies whether to send TCP keep-alive packets and the interval at which they are sent in seconds. Default value is 0, which means that the packets are not sent.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password for the PostgreSQL login account.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

The port of PostgreSQL database to which to connect. The default value is 5432.

Protocol

The Frontend/Backend Protocol version. Available values are 2 and 3. Set the parameter to 2 for the protocol version 2.0 or to 3 for protocol version 3.0. Can be applied only for PostgreSQL server versions 7.4 or higher, for earlier versions of PostgreSQL this parameter is ignored. To execute several statements in the same query set procol to 2 version. The default value is 3.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

SSL CA Cert

The location of the authority SSL certificate.

SSL Cert

The location of the client SSL certificate.

SSL Cipher List

The list of ciphers that client agrees to use.

SSL Key

The location of the user's private key.

SSLMode

SSL connection priority. May be Disable, Allow, Prefer, and Require. The default value is Disable, which means that only an unencrypted SSL connection will be attempted.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, Devart SSIS Data Flow Components will use only one connection internally. The default value is false.

Unicode

If true, by default, sets client charset to UTF8 and converts client data according to this charset.

UnpreparedExecute

If true, unprepared execute mode is used by default.

User ID -or- User

The PostgreSQL login account.

Validate Connection

Specifies whether to validate connections that are being got from the pool.


DB2

ExpandedToggleIcon Connection Dialog

To connect to a DB2 database, required connection parameters must be set.

The following connection options are required:

1. Host - The name of TNS alias of DB2 database to which to connect.
2. Port - Number of a port to communicate with listener on the server. The default value is 5432;
3. User Id - The DB2 login account.
4. Password - The password for the DB2 login account.
5. Database - The name of the database to connect to.
6. Schema - Specifies the schema name used for all unqualified SQL objects used in the connection.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Authentication

A string representing the type of authentication to be used. The default value is "SERVER". Acceptable values are: SERVER, SERVER_ENCRYPT, DATA_ENCRYPT, KERBEROS, GSSPLUGIN, CERTIFICATE.

CaptureMode

Determines if dynamically executed statements are captured for client optimization using pureQuery. Can be "on" or "off". The default value is "off".

ClientAccountingString

The client accounting string. The default value is an empty string ("").

ClientApplicationName

The client application name. The default value is an empty string ("").

ClientEncAlg

Determines whether the AES encryption is used. Set this parameter to "AES" in order to use it. In such case the Authentication parameter will be considered equal to "SERVER_ENCRYPT".

ClientUserID

The string containing the client user ID.

ClientWorkstationName

The name of the client workstation. The default value is an empty string ("").

CLISchema

The name of the schema to be used with the db2ocat tables. This value is not set by default.

CodePage

The connection Encoding code page identifier.

Collection

The collection name, which is the qualifier of the package name (collection.rootPkgName).

ConcurrentAccessResolution

The concurrent access resolution to use.

ConnectNodeNumber

The database partition server to connect to.

Connect Timeout -or- Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Connection Lifetime

Number of seconds for connection to live.

ConvertToLong

Determines whether varchar inputs should be reported as long varchar.

CurrentPackageSet

The schema name for selecting the package to use for subsequent SQL statements. If this parameter is set, the SET CURRENT PACKAGESET statement is executed each time when connecting. This statement specifies the schema name for selecting the package to use for subsequent SQL statements.

CurrentSchema

The default user ID to use as the owner for unqualified SQL objects.

Data Source -or- Host -or- Server

The name or IP address of host of DB2 database to which to connect.

Database

The name of the database. If this parameter is not specified, it is assumed that name of the database is same as user name.

DB2Explain

Determines whether the server will generate Explain snapshot and/or Explain table information.

DBName

The database name. This parameter is only used when connecting to DB2 for z/OS and OS/390, and only if (base) table catalog information is requested by the application.

Default Command Timeout

The default time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

DelimIdent

Specifies whether any string within double quotes (") is treated as an identifier, and any string within single quotes (') is treated as a string literal.

DisableCursorHold

Determines the effect of a transaction completion on open cursors. Default value is false - the cursors are not destroyed after completing the transaction.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

EnsureDefaultDFRM

Specifies whether to explicitly set the rounding mode to ROUND_HALF_EVEN.

ExecutionMode

Determines whether previously captured statements should be executed statically for client optimization using pureQuery. Can be either "static" or "dynamic". "static" means that statements that are captured and packaged in the database are executed statically, statements that have not been captured will be executed dynamically. "dynamic" means that all statements will be processed dynamically.

FitHighPrecisionType

The type of precision to use.

Values:

· UnInitialized - The value is not specified.
· ReturnException - Throws a truncation exception if the value does not fit in the .NET system type.
· WithTruncate - Returns .NET system type after silently truncating the column value if needed.
· AsString - Converts the column to a .NET string type.

Graphic

Specifies if GRAPHIC (double-byte character) is returned as a supported SQL data type and what unit is used to report GRAPHIC column length. Can be an integer value from 0 to 3. Default value is 0. This means that GRAPHIC is not returned as a supported SQL data type, and the reported length of GRAPHIC columns equals the maximum number of DBCS characters in the column. 1 means that GRAPHIC is returned as a supported SQL data type, and the reported length of GRAPHIC columns equals the maximum number of DBCS characters in the column. 2 means that GRAPHIC is not returned as a supported SQL data type, and the reported length of GRAPHIC columns equals the maximum number of bytes in the column. 3 means GRAPHIC is returned as a supported SQL data type, and the reported length of GRAPHIC columns equals the maximum number of bytes in the column.

HostVarParameters

Determines whether the support for host variables is enabled.

Instance

The instance name for a local IPC connection.

Interrupt

An integer value (0, 1, or 2) representing the interrupt processing mode. 0 disables interrupt processing. 1 means that interrupts supported are sent, otherwise the connection is closed. 3 means that interrupts drop the connection. Default value is 1.

Isolation Level

The isolation level for the connection. The default value is "SQL_TXN_READ_COMMITTED". The supported values are: SQL_TXN_READ_UNCOMMITTED - Read Uncommitted (Uncommitted read), SQL_TXN_READ_UNCOMMITTED - Read Uncommitted (Uncommitted read), SQL_TXN_REPEATABLE_READ - Repeatable Read (Read Stability), and SQL_TXN_SERIALIZABLE - Serializable (Repeatable read).

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password for the DB2 login account.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

ProgramId

User-defined character string, that associates an application with a connection. The string can be of up to 80 bytes.

ProgramName

The user-defined or default application name. By default, the first 20 bytes of the executable file name are used.

PureQueryXML

The file name and path of the capture file.

RetrieveXmlInBinaryFormat

Determines whether the XML data should be retrieved in a binary format.

RetryParameterBindingOnError

If parameters are defined with an incorrect datatype, this will result in an error. When this property is set to true, the describe information is used to attempt to determine the correct datatype for the parameter and the statement is re-executed. Setting RetryParameterBindingOnError to true will preserve this functionality. However, this may decrease performance, so it is recommended to set it to false and define parameters correctly.

RootPkgName

The package identifier for the package name (collection.rootPkgName) to be used by pureQuery when ExecutionMode is set to static.

SchemaList

The list of schemas to use. The schema names in the list are case-sensitive, must be delimited with single quotes, and separated by commas. The maximum length of the string is 256 characters.

Security

If you want to enable SSL, set this parameter to SSL.

Server

The host and port to connect to, separated with a colon. For example, "db2server:5000". Port may be omitted.

ServerType

The data server type to connect to. Can be "UNIDATA" or "UNIVERSE". The default value is an empty string ("").

SSLClientKeystash

The name of the SSL stash file used for an SSL connection. Must be specified together with the SSLClientKeystoredb connection string parameter in case the Security parameter is set to "SSL". The SSL stash file stores an encrypted password to the key database file.

SSLClientKeystoredb

The name of the SSL key database file used for an SSL connection. Must be specified together with the SSLClientKeystash connection string parameter in case the Security parameter is set to "SSL".

StatementConcentrator

Determines whether statement concentrator literals are enabled. Can be "Off" or "Literals". If this parameter is not specified, the behavior is determined by the server configuration.

StaticLatch

Determines whether to enable static blocking.

SysSchema

An alternative schema to be searched.

TargetPrincipal

The fully qualified Kerberos principal name of the DB2 instance owner for a target server. Used when the Authentication connection string parameter is set to "KERBEROS".

TraceFile

The name of a file to which all the connection trace information is written. If the file specified does not exist, then it will be created; otherwise, the new trace information will be appended to the end of the file. However, the path to the file must exist. If the filename given is invalid or if the file cannot be created or written to, no trace will occur and no error message will be returned. It is not recommended to use tracing in a multithreaded application, because all the threads will write to the same output file, and the output will be hard to decipher.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. The default value is false. Not available in Mobile Edition.

TrustedContextSystemPassword

The password for the SYSTEM AUTHID of the trusted context.

TrustedContextSystemUserID

The trusted context SYSTEM AUTHID for the connection.

User

The DB2 login account.

Validate Connection

Specifies whether to validate connections that are being got from the pool.

WalletID

The encryption key wallet ID for U2 Data Server.

WalletPwd

The encryption key wallet password for U2 Data Server.


Azure SQL Data Warehouse

ExpandedToggleIcon Connection Dialog

The following connection options are required for connecting to Azure SQL Data Warehouse:

1. Server - The name or IP address of the Azure SQL Data Warehouse server to which to connect.
2. User - The user name to login with.
3. Password - The password to login with.
4. Database - The name of the database to connect to.

Entering these options is enough for reading data from Azure SQL Data Warehouse and loading data to Azure SQL Data Warehouse using normal Insert, Update, and Delete operations. If you want to use a highly efficient BulkInsert operation to quickly load large volumes of data to Azure SQL Data Warehouse via the PolyBase technique, you also need to switch to the Storage tab of the connection editor and provide parameters to connect to Azure Storage:

1. Storage Account - The Azure Storage account name.
2. Storage Account Key - Your 512-bit storage access key.
3. Storage Endpoints Protocol - determines the protocol to use - HTTPS or HTTP.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor tab:

Name

Description

Application Name

The name of the application, or '.NET SQLClient Data Provider' if no application name is provided.

ApplicationIntent

The application workload type when connecting to a database.

Asynchronous Processing

When true, enables asynchronous operation support.

AttachDBFilename

The name of the primary database file, including the full path name of an attachable database. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string.

Authentication

The authentication method to use for connecting to SQL Azure Data Warehouse.

Connection Lifetime

The time span in seconds for connection to live.

Connection Reset

Determines whether the connection is reset when taken from the connection pool.

Context Connection

true if an in-process connection to SQL Azure Data Warehouse should be made.

Current Language

The SQL Azure Data Warehouse Language record name.

Data Source

The name or network address of the instance of SQL Azure Data Warehouse to which to connect.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Encrypt

When true, SQL Azure Data Warehouse uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.

Enlist

true indicates that the SQL Azure Data Warehouse connection pooler automatically enlists the connection in the creation thread's current transaction context.

Failover Partner

The name of the failover partner server where database mirroring is configured.

Initial Catalog

The name of the database.

Integrated Security

When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.

Load Balance Timeout

The minimum time, in seconds, for the connection to live in the connection pool before being destroyed.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

MultipleActiveResultSets

When true, an application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection. Recognized values are true and false.

MultiSubnetFailover

When true, an application a connection must attempt connections in parallel for multi-subnet failover or aggressively retry the TCP connection for a subnet failover.

Network Library

The network library used to establish a connection to an instance of SQL Azure Data Warehouse. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory) and dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP). The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.

Packet Size

Size in bytes of the network packets used to communicate with an instance of SQL Azure Data Warehouse. The default value is 8192.

Password

The password for the SQL Azure Data Warehouse account logging on.

Persist Security Info

When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Replication

true if replication is supported using the connection.

Storage Account Key

Your 512-bit storage access key.

Storage Account Name

The Azure Storage account name. Devart SSIS Data Flow Components use PolyBase to ensure fastest data loading to Azure SQL Data Warehouse. The loads data to Azure Blob Storage as CSV files and uses PolyBase to import data from these files to Azure SQL Data Warehouse, and then deletes the CSV files after the import.

Storage Endpoints Protocol

Determines the protocol to use - HTTPS or HTTP.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. The default value is false.

TrustServerCertificate

When set to true, SSL is used to encrypt the channel but to bypass walking the certificate chain to validate trust. If the connection string has TrustServerCertificate set to true but Encrypt is not set to true, the channel is not encrypted. Recognized values are true, false, yes, and no. For more information, see "Encryption Hierarchy" and "Using Encryption Without Validation" in SQL Server 2005 Books Online.

Type System Version

A string value that indicates the type system the application expects. Possible values are:

Type System Version=SQL Server 2000;

Type System Version=SQL Server 2005;

Type System Version=Latest;

User ID

The SQL Azure Data Warehouse login account.

User Instance

A value that indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.

Validate Connection

Specifies whether to validate connections that are being got from the pool.

Workstation ID

The name of the workstation connecting to SQL Azure Data Warehouse. The default value is the local computer name.


Google BigQuery

ExpandedToggleIcon Connection Dialog

To connect to Google BigQuery, you need to perform the following steps:

1. Click the Web Login button
2. In the opened Web Login dialog box sign in to your Google account.

BigQuery-Connect-1
3. Click Allow.

BigQuery-Connect-3

Additionally you may configure Advanced connection options for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Client Id

The client ID obtained from the Google Developers Console.

Client Secret

The client secret obtained from the Google Developers Console.

Cloud Storage Bucket Name

The name of a bucket in Google's Cloud Storage. The bucket will be used for temporary upload of CSV files when loading data to BigQuery.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Data Set Id

The unique Id of a Dataset you want to connect to.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Primary Key Column

The name of a column that is considered as a primary column of a table.

Google BigQuery does have primary keys. You should specify the name of a column that Devart SSIS Data Flow Components will consider a primary key. Note that it is the same for all the tables accessed via this connection, and thus, all the BigQuery tables you are going to access via this connection must have a column with the same name, for example, ID, that is considered a primary key.

Project ID

The unique ID of a Google Cloud Platform Project associated with the connection.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

If set to True, the connection allows only reading data (only SELECT statements).

Refresh Token

The refresh token returned from the authorization code exchange.

Use Legacy SQL

Determines whether to use BigQuery's legacy SQL dialect. The default value is false.

UTC Dates

Determines whether to treat date values as UTC dates. The default value is false.

Validate Connection

Specifies whether to validate connections that are being got from the pool.


Amazon Redshift

ExpandedToggleIcon Connection Dialog

The following connection options are required for connecting to Amazon Redshift:

1. Server - The name or IP address of the Redshift server to which to connect.
2. Port - Number of a port to communicate with listener on the server. The default value is 5432;
3. User Id - The Redshift login account.
4. Password - The password for the Redshift login account.
5. Database - The name of the database to connect to.
6. Schema - The name of the schema to be used once a connection is opened. You can change it later if you need. By default it is schema 'Public'.

Entering these options is enough for reading data from Amazon Redshift and loading data to Amazon Redshift using normal Insert, Update, and Delete operations. If you want to use a highly efficient BulkInsert operation to quickly load large volumes of data to Amazon Redshift via the Redshift COPY command, you also need to switch to the Amazon Web Services tab of the connection editor and provide parameters to connect to Amazon S3:

1. Access Key ID - the first part of your Amazon Web Services access key.
2. Secret Key - the second part of your Amazon Web Services access key. Read more about AWS access keys.
3. Security Token - an alternative to Access Key ID and Secret Key pair, Amazon Web Services Security token is a temporary, limited-privilege credential.
4. Region - the AWS region, where your S3 storage is hosted.
5. Bucket Name - here you may optionally specify the name of your S3 bucket to temporarily load CSV files with imported data to. If you leave it empty, Devart Redshift Destination will create a new bucket when importing or replicating data to Redshift and delete it after the operation is finished.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced tab of Connection Editor dialog box:

Option

Description

AWS Access Key ID

The first part of your Amazon Web Services access key.

AWS Secret Key

The second part of your Amazon Web Services access key. Read more about AWS access keys...

AWS Security Token

An alternative to AWS Access Key ID and AWS Secret Key pair, Amazon Web Services Security token is a temporary, limited-privilege credential.

AllowDateTimeOffset -or- Allow DateTimeOffset

Determines whether to use the DateTimeOffset type instead of DateTime when PgSqlDataReader returns a timestamptz value.

Character Set

A character set used by the client. Actually, if this property is enabled, then the "SET NAMES <Charset>" query is executed on establishing a connection. Default value is an empty string. That means there is no action performed with charset on the client and on the server.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connect Timeout -or- Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Database

The name of the Redshift database you want to connect to.

Default Command Timeout

The default time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Default Fetch All

This parameter is ignored. Do not use this parameter.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

Force IPv4

Determines whether authentication by an IPv6 address can be used, or an IPv4 address must always be used for authentication.

Host

The name or IP address of host of PostgreSQL database to which to connect.

Ignore Unnamed Parameters

Determines whether to treat the '?' character in the command text as an unnamed parameter.

Initial Schema

The name of the schema to be used once a connection is opened. You can change it later if you need. By default it is schema 'Public'.

Integrated Security

Determines whether to use a secure authentication with single sign-on (GSSAPI or SSPI, depending on the server).

Join Statement Notices

Determines whether all the notices, raised during the statement execution, will be returned together in one InfoMessage event after the statement execution, or each notice will be returned as a separate InfoMessage event.

Keep Alive

Specifies whether to send TCP keep-alive packets and the interval at which they are sent in seconds. Default value is 0, which means that the packets are not sent.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password to login with.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Ping Interval

The interval (in seconds) between pinging Azure SQL Data Warehouse server and reopening the connection if required.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

Redshift connection port; default value is 5432.

Protocol

The Frontend/Backend Protocol version. Available values are 2 and 3. Set the parameter to 2 for the protocol version 2.0 or to 3 for protocol version 3.0. The default value is 3.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

S3 Bucket

The name of your S3 bucket to temporarily load the file with imported or replicated data to. If you leave it empty, Skyvia will create a new bucket when importing or replicating data to Redshift and delete it after the operation is finished.

To load data to Redshift with max performance, Devart Redshift Destination loads data as CSV files to Amazon S3, tells Redshift to import data from it, and deletes the CSV file after the import.

S3 Bucket Region

The AWS region, where your S3 storage is hosted.

SSL CA Cert

The location of the authority SSL certificate.

SSL Cert

The location of the client SSL certificate.

SSL Cipher List

The list of ciphers that client agrees to use.

SSL Key

The location of the user's private key.

SSLMode

SSL connection priority. May be Disable, Allow, Prefer, and Require. The default value is Disable, which means that only an unencrypted SSL connection will be attempted.

Transaction Error Behavior

Determines how the driver handles errors that occur within a transaction.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, Devart SSIS Data Flow Components will use only one connection internally. The default value is false.

Unicode

If true, by default, sets client charset to UTF8 and converts client data according to this charset.

UnpreparedExecute

If true, unprepared execute mode is used by default.

User ID -or- User

The user name to login with.

Validate Connection

Specifies whether to validate connections that are being got from the pool.

Option

Description

AWS Access Key ID

The first part of your Amazon Web Services access key.

AWS Secret Key

The second part of your Amazon Web Services access key. Read more about AWS access keys...

AWS Security Token

An alternative to AWS Access Key ID and AWS Secret Key pair, Amazon Web Services Security token is a temporary, limited-privilege credential.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Database

The name of the Redshift database you want to connect to.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Default Fetch All

If the FetchAll mode is enabled, all the querried data is retrieved from the server on execution.

Direct

If true, by default, allows not to use MySQL client library (libmysql.dll) to access MySQL server.

Disable Charset Sending

Enables or disables explicit charset resetting when a connection is taken from a pool.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

FoundRows

Specifies whether the provider will return the number of rows matched by the WHERE condition of the UPDATE statement instead of the rows actually changed as the number of changed rows.

Host

The name or IP address of the Redshift server to which to connect.

Ignore Fractional Seconds

Specifies whether fractional parts of seconds in TIME, DATETIME, and TIMESTAMP values should be ignored.

Ignore Prepare

This parameter is ignored. Do not use this parameter.

Keep Alive

Specifies whether to send TCP keep-alive packets and the interval at which they are sent in seconds. Default value is 0, which means that the packets are not sent.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password to login with.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Ping Interval

The interval (in seconds) between pinging Azure SQL Data Warehouse server and reopening the connection if required.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

Redshift connection port; default value is 5432.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

S3 Bucket

The name of your S3 bucket to temporarily load the file with imported or replicated data to. If you leave it empty, Skyvia will create a new bucket when importing or replicating data to Redshift and delete it after the operation is finished.

To load data to Redshift with max performance, Devart Redshift Destination loads data as CSV files to Amazon S3, tells Redshift to import data from it, and deletes the CSV file after the import.

S3 Bucket Region

The AWS region, where your S3 storage is hosted.

Schema

Tthe name of the Redshift schema you want to connect to.

Transaction Scope Local

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. The default value is false.

Unicode

If true, sets client charset to UTF8 and converts client data according to this charset. The default value is false.

User ID -or- User

The user name to login with.

Validate Connection

Specifies whether to validate connections that are being got from the pool.


Salesforce

ExpandedToggleIcon Connection Dialog

The following connection options are required to connect to Salesforce:

1. Host - The Salesforce.com or Database.com login URL;
2. User Id - The Salesforce login account;
3. Password - The password for the Salesforce login account;
4. Security token - The automatically generated key that is used for logging in to Salesforce from an untrusted network. See the Salesforce documentation to learn how to get or reset your security token.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Authentication Type

Determines the authentication method to use when connecting to Salesforce.

Batch Size

The the number of records returned by a service at once. The default value is 500; the minimum is 200, and the maximum is 2,000.

Client ID

The Consumer Key from the connected app definition. Used for the OAuth Refresh Token authentication.

Client Secret

The Consumer Secret from the connected app definition. Used for the OAuth Refresh Token authentication.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connect Timeout -or- Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Host

The Salesforce.com or Database.com login URL.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Metadata Cache

The path to the metadata cache database file.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Organization Id

A unique identifier for the Salesforce organization to which the account you connect to belongs.

Password

The password for the account.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Refresh Token

The Salesforce OAuth 2.0 refresh token. Used for the OAuth Refresh Token authentication.

Security Token

The automatically generated key that is used for logging in to Salesforce from an untrusted network..

Trigger Auto Response Email

Specifies whether to trigger auto-response rules or not. Default value is false.

Trigger Other Email

Specifies whether to trigger email outside the organization or not. The default value is false.

Trigger User Email

Specifies whether to trigger email that is sent to users in the organization or not. The default value is false.

Update Mru

Specifies whether to update the list of most recently used items or not.

Use Assignment Rules

Determines whether to use assignment rules when inserting or updating data. The default value is false. Note that setting this property to true causes an additional roundtrip to the server when connecting to Salesforce. As an alternative, you can assign the Id of the necessary assignment rule to the Assignment Rule Id property for Devart Salesforce Destination, and this destination will use this assignment rule even when Use Assignment Rules is set to false.

User Id

The Salesforce login account.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Dynamics CRM

ExpandedToggleIcon Connection Dialog

To connect to Dynamics CRM, required connection parameters must be set.

The following connection options are required:

1. Host - The login URL to Dynamics CRM service;
2. User Id - The Dynamics CRM login account;
3. Password - The password for the Dynamics CRM login account;

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Cache All

Allows you to create cache for all the tables when connecting. Data is actually cached only when it is queried.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password for the account.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Server

Server name

User ID

The Dynamics login account.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Zoho CRM

ExpandedToggleIcon Connection Dialog

You need to specify the following options when connecting to Zoho CRM:

1. Domain - select whether you connect to crm.zoho.com or to crm.zoho.eu.
2. Authentication Token - The automatically generated key that is used for logging in to Zoho CRM from an untrusted network. For generating authentication token, you must have the permission to access the API service. If you do not have permission, please contact your CRM administrator.

In order to generate authentication token, open the following URL in your web browser (depending on whether you use Zoho on crm.zoho.com or crm.zoho.eu domain):

https://accounts.zoho. com /apiauthtoken/create?SCOPE=ZohoCRM/crmapi
-or-
https://accounts.zoho. eu /apiauthtoken/create?SCOPE=ZohoCRM/crmapi

The browser will show something similar to the following:

#
#Wed Feb 29 03:07:33 PST 2012
AUTHTOKEN=bad18eba1ff45jk7858b8ae88a77fa30
RESULT=TRUE

Copy your authentication token (the part of the line after "AUTHTOKEN=" - in our example, its "bad18eba1ff45jk7858b8ae88a77fa30") from this page and paste it to Connection Editor.

For more information about Zoho CRM authentication tokens see Zoho CRM documentation.
3. Zoho time zone - the timezone that is specified in your Zoho CRM account settings.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Domain

Zoho CRM domain base URL. Can be crm.zoho.com or crm.zoho.eu.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Security Token

Zoho CRM Authentication Token.

Trigger Workflow

Determines whether to trigger the workflow rule when inserting or updating data.

Upgrade Cache

Determines whether to upgrade the local cache database or to throw an exception in case when Devart SSIS Data Flow Components version that uses a newer cache structure or newer version of Zoho CRM API discovers an old incompatible cache for the connection that is opening.

Use Display Name For Custom Tables

Determines whether display names are used to access custom Zoho CRM modules instead of the module names.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered in Excel are considered UTC dates when saving them to the data source.

Zoho Time Zone

The timezone that is specified in your Zoho CRM account settings.


SugarCRM

Devart SSIS Data Flow Components for SugarCRM support SugarCRM 7.0 or higher. Devart SSIS Data Flow Components for SugarCRM do not support SugarCRM Community edition.

ExpandedToggleIcon Connection Dialog

To connect to SugarCRM, required connection parameters must be set.

The following connection options are required:

1. Host - The login URL to SugarCRM service;
2. User Id - The SugarCRM login account;
3. Password - The password for the SugarCRM login account;

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Ignore Certificate Name Mismatch

Determines whether the certificate name mismatch error should be ignored.

Ignore Certificate Untrusted

Determines whether the untrusted root certificate error should be ignored.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Metadata Cache

The path to the metadata cache database file.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The user password.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Server

The login URL to the SugarCRM service.

User Id

The SugarCRM account login.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered in Excel are considered UTC dates when saving them to the data source.


QuickBooks

ExpandedToggleIcon Connection Dialog

To connect to QuickBooks, you need to perform the following steps:

1. Click the Web Login button
2. In the opened Web Login dialog box sign in to your QuickBooks account.
3. Select a company to query data from (click the link with the corresponding company name).
4. Click Authorize.

Additionally you may configure Advanced connection options for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Access Token

An OAuth access token. Filled automatically when you perform web login and authorization as described above.

Access Token Secret

A secret used by the application to establish ownership of the access token. Filled automatically when you perform web login and authorization as described above.

Company Id

A string that uniquely identifies a QuickBooks company. Filled automatically when you perform web login and authorization as described above.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Consumer Key

An OAuth value, identifying the application to the Intuit OAuth provider service. Filled automatically when you perform web login and authorization as described above.

Consumer Key Secret

An OAuth secret used by the application to establish ownership of the consumer key. Filled automatically when you perform web login and authorization as described above.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


FreshBooks

ExpandedToggleIcon Connection Dialog

To connect to FreshBooks, required connection parameters must be set.

The following connection options are required:

1. Host - FreshBooks service URL, derived from the account URL, for example: https://sample.freshbooks.com/api/2.1/xml-in ;
2. Authentication Token - An automatically generated key that is used for connecting to FreshBooks;

To get the URL and Authentication Token, sign in to FreshBooks, click the My Account link in the top of the page, and then click the FreshBooks API link.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Authentication Token

An automatically generated key that is used for connecting to FreshBooks.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Freshbooks Time Zone

Determines whether to treat date values as dates in the timezone, specified in the FreshBooks company profile. This parameter is deprecated, and it is not used for new FreshBooks API.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Server

FreshBooks service URL, derived from the account URL, for example: https://sample.freshbooks.com/api/2.1/xml-in ;

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


MailChimp

ExpandedToggleIcon Connection Dialog

To connect to MailChimp, you need to click the Web Login button and sign in to your MailChimp account in the opened Web Login dialog box.

Additionally you may configure Advanced connection options for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

API Key

A key that grant access to your MailChimp account. Filled automatically when you perform web login as described above.

API Version

Determines the MailChimp API version to use to interact with MailChimp. Note that different API versions provide access to somewhat different sets of objects and fields.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Merge Fields Detection Behavior

Determines how to present MailChimp merge tags when working with the ListMembers table. Merge tags are custom fields of the ListMembers table in MailChimp. It's own set of merge tags can be added for list members of each list; however, Skyvia presents all tables, including MailChimp ListMembers, so that all its rows have the same columns. Merge Fields Behavior parameter

The following values are available for this setting:

· None - all merge tags are not read from MailChimp and not added as ListMembers table fields.
· JoinCommon - only merge tags that are defined for all the Lists are added as ListMembers table columns. Other tags are ignored. This is the default value.
· JoinAll - all the merge tags are joined to other ListMembers table columns (except for the case described below). If a merge tag is not defined for the list, a list member belongs to, NULL value is returned for the corresponding column of this list member.

If different lists have merge tags with the same name but different type, default value, required setting, etc., these merge tags are ignored regardless of the Merge Fields Behavior parameter. Merge tags that have the same name for different lists must be the same in order to be available in Skyvia.

note Note

Reading merge tag information takes some time. If you don't to use merge tags, set this parameter to None for best performance.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Use Merge Tag As Field Name

Determines whether to use merge tag values as custom field names or use label values instead.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Marketo

ExpandedToggleIcon Connection Dialog

The following connection options are required:

1. Client Id - a GUID value, representing Marketo API client id;
2. Client Secret - Marketo API client secret;
3. Domain - Your Marketo subdomain;

You may also select the Use Bulk Extract check box to use Marketo Bulk Extract API for reading Marketo leads. In this case reading Marketo leads will use less API calls, but may take more time. Select this check box only to read very large number of Marketo leads. Reading Marketo leads, created within a known period of time will improve reading performance, it's better to use "Created At" >= some value or/and "Created At" <= some value conditions in your query when using Marketo Bulk Extract API.

To configure access to Marketo and get client id and client secret for the connection, see this instruction in Marketo documentation.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Client Id

A GUID value, representing Marketo API client id.

Client Secret

Marketo API client secret.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Domain

Your Marketo subdomain.

LimitCount

Request limit count. The maximum number of Marketo API requests, performed in a LimitWindow interval. Default value is 100, which corresponds to the default Marketo API call limit.

LimitWindow

Request limit time window. The API call window (time interval), in seconds, within which the number of Marketo API requests is limited by the LimitCount parameter. Default value is 20 seconds, which corresponds to the default Marketo API call limit window.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

UseBulkExtract

Determines whether to use Marketo Bulk Extract API for reading Marketo leads. In this case reading Marketo leads will use less API calls, but may take more time. Set to True only to read very large number of Marketo leads. Reading Marketo leads, created within a known period of time will improve reading performance, it's better to use "Created At" >= some value or/and "Created At" <= some value conditions in your query when using Marketo Bulk Extract API.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Salesforce Marketing Cloud

ExpandedToggleIcon Connection Dialog

To connect to Salesforce Marketing Cloud, you need to select Authentication Type and enter the corresponding connection parameters.

The following connection options are required for the AppCenterClient authentication type:

1. Client Id - your client ID from the App Center;
2. Client Secret - your client Secret from the App Center.

To find out how get these connection parameters, please read this topic in Salesforce Marketing Cloud documentation.

The following connection options are required for the UserNamePassword authentication type:

1. Host - URL of an Salesforce Marketing Cloud instance for API UserNamePassword authentication. By default the S1 Instance address is used: https://webservice.Salesforce Marketing Cloud.com/Service.asmx ;
2. User Id - API User name to login using UserNamePassword authentication;
3. Password - The password for UserNamePassword authentication.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

ExpandedToggleIcon Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

AppClient Id

Application center сlient ID for AppCenterClient authentication.

AppClient Secret

Application center client secret for AppCenterClient authentication.

AppSandbox

Determines whether to use a sandbox account for AppCenterClient authentication.

AuthenticationType

Determines whether to use App Center client credentials or API user credentials for authentication.

Client IDs

The list of specific partner accounts or business units for retrieve requests.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password for UserNamePassword authentication.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Support Extensions Objects

Determines whether Salesforce Marketing Cloud Data Extensions are available via this connection, and whether their data can be viewed/edited.

Url

URL of an Salesforce Marketing Cloud instance for API UserNamePassword authentication. By default the S1 Instance address is used: https://webservice.exacttarget.com/Service.asmx

User

API User name to login using UserNamePassword authentication.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


BigCommerce

Connection Dialog

To connect to BigCommerce, required connection parameters must be set.

The following connection options are required:

1. Host - The API Path - the path where all XML requests to BigCommerce should be sent;
2. User Id - The user name to login with;
3. Authentication Token - An automatically generated key that is used for connecting to BigCommerce;

To get the Host, User Id, and Authentication Token parameters, sign in to BigCommerce in the browser, point to the Settings link in the bottom left corner of the page, and then in the Advanced Settings column click the Legacy API Settings link. After this, you can find all the necessary parameters in the opened Legacy API Account Details. Copy the Username parameter to the User Id box, API Path parameter to the Host box, and API Token parameter to the Authentication Token box.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Access Token

An automatically generated key that is used for connecting to BigCommerce using OAuth authentication. See BigCommerce documentation for more information on how to get it.

Authentication Token

An automatically generated key that is used for connecting to BigCommerce using basic authentication. See BigCommerce documentation for more information on how to get it.

Authentication Type

Determines whether to use legacy Basic-Auth credentials or OAuth tokens for authentication.

Client Id

A unique ID of a BigCommerce client application. Used only for OAuth authentication. See BigCommerce documentation for more information on how to get it.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Server

The API Path - the path where all XML requests to BigCommerce should be sent.

Store Dates

Determines whether to treat date values as dates in the BigCommerce store timezone. Please note that the UTC Dates parameter has a higher priority, and if it is set to True, date values are treated as UTC dates regardless of the Store Dates value.

Store Id

A unique identifier of the store (the first part of the store URL after the protocol). Used only for OAuth authentication.

User ID

The BigCommerce user name to login with.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Magento

Connection Dialog

To connect to Magento, required connection parameters must be set.

The following connection options are required:

1. Domain - The address of your Magento store;
2. User Id - The user name to login with;
3. API Key - An automatically generated key that is used for connecting to Magento;

Additionally, if you want to connect to Magento 2, you need to click Advanced and set the Service Version parameter to Ver2.

You can find the API Key in the User information section of your Magento dashboard.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

Authentication Token

An automatically generated key that is used that is used for connecting to Magento.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Default Command Timeout

The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.

Domain

The address of your Magento store.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Service Version

Magento version to connect to. Default value is Ver1, which means Magento 1.x. If you want to connect to Magento 2, set this property to Ver2.

User ID

The Magento user name to login with.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.


Custom Properties Reference

This section describes custom properties of Devart SSIS Data Flow Components.

Data Source

Source

Destination

Lookup

BigCommerce

Devart BigCommerce Source

Devart BigCommerce Destination

Devart BigCommerce Lookup

DB2

Devart DB2 Source

Devart DB2 Destination

Devart DB2 Lookup

Dynamics CRM

Devart Dynamics Source

Devart Dynamics Destination

Devart Dynamics Lookup

Salesforce Marketing Cloud

Devart Salesforce Marketing Cloud Source

Devart Salesforce Marketing Cloud Destination

Devart Salesforce Marketing Cloud Lookup

FreshBooks

Devart FreshBooks Source

Devart FreshBooks Destination

Devart FreshBooks Lookup

Magento

Devart Magento Source

Devart Magento Destination

Devart Magento Lookup

MailChimp

Devart MailChimp Source

Devart MailChimp Destination

Devart MailChimp Lookup

Marketo

Devart Marketo Source

Devart Marketo Destination

Devart Marketo Lookup

MySQL

Devart MySql Source

Devart MySql Destination

Devart MySql Lookup

Oracle

Devart Oracle Source

Devart Oracle Destination

Devart Oracle Lookup

PostgreSQL

Devart PostgreSQL Source

Devart PostgreSQL Destination

Devart PostgreSQL Lookup

QuickBooks

Devart QuickBooks Source

Devart QuickBooks Destination

Devart QuickBooks Lookup

Salesforce

Devart Salesforce Source

Devart Salesforce Destination

Devart Salesforce Lookup

SugarCRM

Devart Sugar Source

Devart Sugar Destination

Devart Sugar Lookup

Zoho CRM

Devart Zoho Source

Devart Zoho Destination

Devart Zoho Lookup


Devart AzureDWH Destination

Devart AzureDWH Destination component loads data into Azure SQL Data Warehouse tables.

It has the following custom properties:

Property

Description

BatchSize

The size of data uploaded to Azure Blob Storage before actually starting import. Default value is 314572800 (300 Mb), which is 10 times the default max size of an uploaded CSV file.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart AzureDWH Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart AzureDWH Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart AzureDWH Destination component uses PolyBase to quickly load large volumes of data to Azure SQL Data Warehouse. It loads data in the following way:
o First, it writes data to a CSV file, till the CSV file reaches the size, specified in the UploadBatchSize property. After this, it uploads the CSV file to Azure Blob Storage and writes data to a new CSV file.
o After the total size of uploaded files reaches the limit, specified in the BatchSize property, Devart AzureDWH Destination tells Azure SQL Data Warehouse to import data from these CSV files. After import, uploaded files are deleted.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of an Azure SQL Data Warehouse table to load data into.

UploadBatchSize

The max size of data, placed to one CSV file before uploading this file to Azure Blob Storage and starting to write data to a new CSV file.


Devart AzureDWH Lookup

Devart AzureDWH Lookup component joins the input data with the data from Azure SQL Data Warehouse tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 500. Please note that assigning bigger values to this property reduces the number of queries to Azure SQL Data Warehouse, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

Lookup Object

The name of an Azure SQL Data Warehouse database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart AzureDWH Source

Devart AzureDWH Source extracts data from Azure SQL Data Warehouse database objects. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 10.

SelectStatement

An SQL SELECT statement that queries Azure SQL Data Warehouse database objects.


Devart BigCommerce Destination

Devart BigCommerce Destination component loads data into BigCommerce objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart BigCommerce Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart BigCommerce Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for BigCommerce.
· HardDelete - Not supported for BigCommerce.

BatchSize

Not supported for BigCommerce.

ObjectName

The name of a BigCommerce object to load data into.


Devart BigCommerce Lookup

Devart BigCommerce Lookup component joins the input data with the data from BigCommerce objects. It has the following custom properties:

Property

Description

BatchSize

Not supported for BigCommerce

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart BigCommerce Lookup behavior when the lookup condition cannot be converted to BigCommerce API calls. If this property is set to True, Devart BigCommerce Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart BigCommerce Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a BigCommerce object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart BigCommerce Source

Devart BigCommerce Source extracts data from BigCommerce objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 1000.

SelectStatement

An SQL SELECT statement that queries BigCommerce objects.


Devart BigQuery Destination

Devart BigQuery Destination component loads data into Google BigQuery tables.

It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart BigQuery Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart BigQuery Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - not supported for BigQuery.
· HardDelete - not supported for BigQuery.

BatchSize

The max size of data uploaded to Google Cloud Storage before actually starting import. Default value is 314572800 (300 Mb), which is 10 times the default max size of an uploaded CSV file.

BulkInsert

This property is taken into account only when the Action property is set to Insert. When set to True, Devart BigQuery Destination component loads data in the following way to achieve the highest performance:

· First, it writes data to a CSV file, till the CSV file reaches the size, specified in the UploadBatchSize property. After this, it uploads the CSV file to Google Cloud Storage and writes data to a new CSV file.
· After the total size of uploaded files reaches the limit, specified in the BatchSize property, Devart BigQuery Destination tells Google BigQuery to import data from these CSV files. After import, uploaded files are deleted.

When this property is set to False, Devart BigQuery Destination uses usual INSERT statement to insert data.

TableName

The name of a Google BigQuery table to load data into.

UploadBatchSize

The max size of data, placed to one CSV file before uploading this file to Google Cloud Storage and starting to write data to a new CSV file.


Devart BigQuery Lookup

Devart BigQuery Lookup component joins the input data with the data from Google BigQuery objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 500. Please note that assigning bigger values to this property reduces the number of queries to Google BigQuery, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Not used in Devart BigQuery Lookup. This property is ignored.

Lookup Object

The name of a Google BigQuery object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart BigQuery Source

Devart BigQuery Source extracts data from Google BigQuery objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 1000.

SelectStatement

An SQL SELECT statement that queries Google BigQuery objects.


Devart DB2 Destination

Devart DB2 Destination component loads data into DB2 tables. It has the following custom properties:

Property

Description

BatchSize

The maximal number of records to send as one batch to the server. 0 means that one commit will be issued at the end of the operation. Default value is 0.

BulkInsertKeepIdentity

Determines whether to send values supplied for identity columns to the target table. If false, the values for identity columns will be generated at the database side. Default value is False.

BulkInsertTableLock

Determines whether to acquire a table-level lock for the duration of the data loading operation. Default value is False.

BulkInsertTruncate

Determines whether to clear data of the target table before the operation. Default value is False.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart DB2 Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart DB2 Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart DB2 Destination component uses DB2-specific technique to quickly load data to DB2.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of a DB2 database object to load data into.

Timeout

The number of seconds for the operation to complete before it times out. Default value is 0, which means that the operation never times out.


Devart DB2 Lookup

Devart DB2 Lookup component joins the input data with the data from DB2 tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to DB2, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

Lookup Object

The name of a DB2 database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart DB2 Source

Devart DB2 Source extracts data from DB2 tables. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

SelectStatement

An SQL SELECT statement that queries DB2 database objects.


Devart Dynamics Destination

Devart Dynamics Destination component loads data into Dynamics CRM objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Dynamics Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Dynamics Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for Dynamics CRM.
· HardDelete - Not supported for Dynamics CRM.

BatchSize

Not supported for Dynamics CRM.

ObjectName

The name of a Dynamics CRM object to load data into.


Devart Dynamics Lookup

Devart Dynamics Lookup component joins the input data with the data from Dynamics CRM objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 1000. Please note that assigning bigger values to this property reduces the number of queries to Dynamics CRM, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Dynamics Lookup behavior when the lookup condition cannot be converted to Dynamics CRM API calls. If this property is set to True, Devart Dynamics Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Dynamics Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Dynamics CRM object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Dynamics Source

Devart Dynamics Source extracts data from Dynamics CRM objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries Dynamics CRM objects.


Devart ExactTarget Destination

Devart ExactTarget Destination component loads data into Salesforce Marketing Cloud objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart ExactTarget Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart ExactTarget Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for Salesforce Marketing Cloud.
· HardDelete - Not supported for Salesforce Marketing Cloud.

BatchSize

The maximal number of records to send as one batch to the server when performing an Insert operation. Not supported for some Salesforce Marketing Cloud objects. Default value is 200.

ObjectName

The name of an Salesforce Marketing Cloud object to load data into.


Devart ExactTarget Lookup

Devart ExactTarget Lookup component joins the input data with the data from Salesforce Marketing Cloud objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to Salesforce Marketing Cloud, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart ExactTarget Lookup behavior when the lookup condition cannot be converted to Salesforce Marketing Cloud API calls. If this property is set to True, Devart ExactTarget Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart ExactTarget Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Salesforce Marketing Cloud object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart ExactTarget Source

Devart ExactTarget Source extracts data from Salesforce Marketing Cloud objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries Salesforce Marketing Cloud objects.


Devart FreshBooks Destination

Devart FreshBooks Destination component loads data into FreshBooks objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart FreshBooks Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart FreshBooks Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for FreshBooks.
· HardDelete - Not supported for FreshBooks.

BatchSize

Not supported for FreshBooks.

ObjectName

The name of a FreshBooks object to load data into.


Devart FreshBooks Lookup

Devart FreshBooks Lookup component joins the input data with the data from FreshBooks objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 500. Please note that assigning bigger values to this property reduces the number of queries to FreshBooks, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart FreshBooks Lookup behavior when the lookup condition cannot be converted to FreshBooks API calls. If this property is set to True, Devart FreshBooks Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart FreshBooks Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a FreshBooks object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart FreshBooks Source

Devart FreshBooks Source extracts data from FreshBooks objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries FreshBooks objects.


Devart Magento Destination

Devart Magento Destination component loads data into Magento objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Magento Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Magento Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for Magento.
· HardDelete - Not supported for Magento.

BatchSize

Not supported for Magento.

ObjectName

The name of a Magento object to load data into.


Devart Magento Lookup

Devart Magento Lookup component joins the input data with the data from Magento objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 100. Please note that assigning bigger values to this property reduces the number of queries to Magento, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Magento Lookup behavior when the lookup condition cannot be converted to Magento API calls. If this property is set to True, Devart Magento Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Magento Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Magento object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Magento Source

Devart Magento Source extracts data from Magento objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries Magento objects.


Devart MailChimp Destination

Devart MailChimp Destination component loads data into MailChimp objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart MailChimp Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart MailChimp Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for MailChimp.
· HardDelete - Not supported for MailChimp.

BatchSize

Not supported for MailChimp.

ObjectName

The name of a MailChimp object to load data into.


Devart MailChimp Lookup

Devart MailChimp Lookup component joins the input data with the data from MailChimp objects. It has the following custom properties:

Property

Description

BatchSize

Not supported for MailChimp

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart MailChimp Lookup behavior when the lookup condition cannot be converted to MailChimp API calls. If this property is set to True, Devart MailChimp Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart MailChimp Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a MailChimp object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart MailChimp Source

Devart Magento Source extracts data from MailChimp objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 100.

SelectStatement

An SQL SELECT statement that queries MailChimp objects.


Devart Marketo Destination

Devart Marketo Destination component loads data into Marketo objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Marketo Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Marketo Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for Marketo.
· HardDelete - Not supported for Marketo.

BatchSize

The maximal number of records to send as one batch to the server when performing an Insert operation. Default value is 100.

ObjectName

The name of a Marketo object to load data into.


Devart Marketo Lookup

Devart Marketo Lookup component joins the input data with the data from Marketo objects. It has the following custom properties:

Property

Description

BatchSize

Not supported for Marketo

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Marketo Lookup behavior when the lookup condition cannot be converted to Marketo API calls. If this property is set to True, Devart Marketo Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Marketo Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Marketo object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Marketo Source

Devart Magento Source extracts data from Marketo objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 100.

SelectStatement

An SQL SELECT statement that queries Marketo objects.


Devart MySql Destination

Devart MySql Destination component loads data into MySQL tables. It has the following custom properties:

Property

Description

BufferSize

The maximal size of a data packet sent to the server. Default value is 262144.

BulkInsertDelayed

Determines whether to use INSERT DELAYED statement syntax. Default value is False.

BulkInsertLock

Determines whether to execute the LOCK TABLE statement before loading the data. Default value is False.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart MySql Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart MySql Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart MySql Destination component uses INSERT statements that insert data by several rows at the same time.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of a MySQL database object to load data into.


Devart MySql Lookup

Devart MySql Lookup component joins the input data with the data from MySQL tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to MySQL, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

Lookup Object

The name of a MySQL database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart MySql Source

Devart MySql Source extracts data from MySQL tables. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

SelectStatement

An SQL SELECT statement that queries MySQL database objects.


Devart Oracle Destination

Devart Oracle Destination component loads data into Oracle tables. It has the following custom properties:

Property

Description

BufferSize

The maximal size of a data packet sent to the server. Default value is 262144.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Oracle Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Oracle Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart Oracle Destination component uses Oracle-specific technique to quickly load data to Oracle. With OCI connections, Devart Oracle Destination uses direct path load interface to speed up loading. With Direct mode it uses array binding feature.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of an Oracle database object to load data into.


Devart Oracle Lookup

Devart Oracle Lookup component joins the input data with the data from Oracle tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to Oracle, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

Lookup Object

The name of a Oracle database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Oracle Source

Devart Oracle Source extracts data from Oracle tables. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

SelectStatement

An SQL SELECT statement that queries Oracle database objects.


Devart PostgreSql Destination

Devart PostgreSql Destination component loads data into PostgreSQL tables. It has the following custom properties:

Property

Description

BufferSize

The maximal size of a data packet sent to the server. Default value is 262144.

BulkInsertMode

Determines which mode is used to load external data to a PostgreSQL database. Default value is Text. The following values are supported:

· Text - the text mode is used, which is the only option available for PostgreSQL 7.3 and older.
· Binary - the binary mode is used, which is faster, but supported only within protocol 3.0.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart PostgreSql Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart PostgreSql Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart PostgreSql Destination component uses the COPY command to speed up data loading.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of a PostgreSQL database object to load data into.


Devart PostgreSQL Lookup

Devart PostgreSql Lookup component joins the input data with the data from PostgreSQL tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to PostgreSQL, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

Lookup Object

The name of a PostgreSQL database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart PostgreSQL Source

Devart PostgreSql Source extracts data from PostgreSQL tables. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

SelectStatement

An SQL SELECT statement that queries PostgreSQL database objects.


Devart QuickBooks Destination

Devart QuickBooks Destination component loads data into QuickBooks objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart QuickBooks Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart QuickBooks Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for QuickBooks.
· HardDelete - Not supported for QuickBooks.

BatchSize

The maximal number of records to send as one batch to the server when performing an Insert operation. Default value is 25.

ObjectName

The name of an QuickBooks object to load data into.


Devart QuickBooks Lookup

Devart QuickBooks Lookup component joins the input data with the data from QuickBooks objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 5000. Please note that assigning bigger values to this property reduces the number of queries to QuickBooks, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart QuickBooks Lookup behavior when the lookup condition cannot be converted to QuickBooks API calls. If this property is set to True, Devart QuickBooks Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart QuickBooks Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a QuickBooks object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart QuickBooks Source

Devart QuickBooks Source extracts data from QuickBooks objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 1000.

SelectStatement

An SQL SELECT statement that queries QuickBooks objects.


Devart Redshift Destination

Devart Redshift Destination component loads data into Azure SQL Data Warehouse tables.

It has the following custom properties:

Property

Description

BatchSize

The size of data uploaded to Amazon S3 before actually starting import. Default value is 314572800 (300 Mb), which is 10 times the default max size of an uploaded CSV file.

Operation

A DML operation to apply to the target object. Default value is BulkInsert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Redshift Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Redshift Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· BulkInsert - Devart Redshift Destination component loads data in the following way to achieve the highest performance:
o First, it writes data to a CSV file, till the CSV file reaches the size, specified in the UploadBatchSize property. After this, it uploads the CSV file to Amazon S3 and writes data to a new CSV file.
o After the total size of uploaded files reaches the limit, specified in the BatchSize property, Devart Redshift Destination tells Redshift to import data from these CSV files. After import, uploaded files are deleted.

RetryCount

The number of times to retry loading the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to load data. Default value is 1.

TableName

The name of an Amazon Redshift table to load data into.

UploadBatchSize

The max size of data, placed to one CSV file before uploading this file to Amazon S3 and starting to write data to a new CSV file.


Devart Redshift Lookup

Devart Redshift Lookup component joins the input data with the data from Azure SQL Data Warehouse tables. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 500. Please note that assigning bigger values to this property reduces the number of queries to Amazon Redshift, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

Lookup Object

The name of an Amazon Redshift database object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Redshift Source

Devart Redshift Source extracts data from Amazon Redshift database objects. It has the following custom properties:

Property

Description

CommandTimeout

The wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. Default value is 30.

RetryCount

The number of times to retry querying the data in case if it fails. Default value is 5.

RetryInterval

The number of seconds before repeating an attempt to query data. Default value is 1.

SelectStatement

An SQL SELECT statement that queries Amazon Redshift database objects.


Devart Salesforce Destination

Devart Salesforce Destination component loads data into Salesforce objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Salesforce Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Salesforce Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - When executing an upsert, records are inserted if they do not exist in the Salesforce object; in case records do exist, then data update is performed. This action is based on an External ID field.

In order to use the Upsert option, the Salesforce object must have an External ID field, and the local storage must contain the corresponding unique column (usually this is a primary key in the database table). When configuring the component, you must map this unique column to the External ID field in the target Salesforce object.
· HardDelete - Completely deletes data rows without the possibility to restore them.

While the Delete operation only sets the IsDeleted property to True for deleted records, HardDelete actually deletes them.

AssignmentRuleId

The id of a Salesforce assignment rule that will be used when inserting data to Salesforce via this destination. If set, this rule is applied regardless of the connection's Use Assignment Rules parameter.

BatchSize

The maximal number of records to send as one batch to the server when performing an Insert operation. Not supported for some Salesforce objects. Default value is 5000. Actually the maximal volume of data to load in one batch is limited by two properties - BatchSize and BufferSize. Of these two limitations that one takes effect, the value of which is reached first.

BufferSize

The maximal size of a data packet sent to the server. Default value is 10485760. Actually the maximal volume of data to load in one batch is limited by two properties - BatchSize and BufferSize. Of these two limitations that one takes effect, the value of which is reached first.

ObjectName

The name of an Salesforce object to load data into.


Devart Salesforce Lookup

Devart Salesforce Lookup component joins the input data with the data from Salesforce objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 50. Please note that assigning bigger values to this property reduces the number of queries to Salesforce, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Salesforce Lookup behavior when the lookup condition cannot be converted to Salesforce API calls. If this property is set to True, Devart Salesforce Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Salesforce Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Salesforce object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Salesforce Source

Devart Salesforce Source extracts data from Salesforce objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 1000.

BulkQuery

Determines whether to use optimizations for loading large volumes of data. Default value is False.

IncludeDeleted

Determines whether to query records with IsDeleted = True. Default value is False.

SelectStatement

An SQL SELECT statement that queries Salesforce objects.


Devart Sugar Destination

Devart Sugar Destination component loads data into SugarCRM objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Sugar Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Sugar Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for SugarCRM.
· HardDelete - Not supported for SugarCRM.

BatchSize

Not supported for SugarCRM

ObjectName

The name of an SugarCRM object to load data into.


Devart Sugar Lookup

Devart Sugar Lookup component joins the input data with the data from SugarCRM objects. It has the following custom properties:

Property

Description

BatchSize

The number of input rows to check for matching rows at once in one query. Default value is 50. Please note that assigning bigger values to this property reduces the number of queries to SugarCRM, but increases the query size, and too large queries may cause errors. In case of errors reduce the value of this property.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Sugar Lookup behavior when the lookup condition cannot be converted to SugarCRM API calls. If this property is set to True, Devart Sugar Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Sugar Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a SugarCRM object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Sugar Source

Devart Sugar Source extracts data from SugarCRM objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries SugarCRM objects.


Devart Zoho Destination

Devart Zoho Destination component loads data into Zoho CRM objects. It has the following custom properties:

Property

Description

Action

A DML operation to apply to the target object. Default value is Insert. The following values are supported:

· Insert - This is the most common operation.
· Update - Devart Zoho Destination component in the Update mode enables updating data in a data source. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record.
· Delete - Devart Zoho Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record.
· Upsert - Not supported for Zoho CRM.
· HardDelete - Not supported for Zoho CRM.

BatchSize

The maximal number of records to send as one batch to the server when performing an Insert operation. Default value is 50.

ObjectName

The name of a Zoho CRM object to load data into.


Devart Zoho Lookup

Devart Zoho Lookup component joins the input data with the data from Zoho CRM objects. It has the following custom properties:

Property

Description

BatchSize

Not supported for Zoho CRM.

CacheSize

The number of the queried rows from the lookup object to cache. Default value is 2000.

CaseInsensitiveLookup

Determines whether matching is case insensitive. Default value is False.

LocalSQLEngine

Determines the Devart Zoho Lookup behavior when the lookup condition cannot be converted to Zoho CRM API calls. If this property is set to True, Devart Zoho Lookup selects all the data from the Lookup Object and performs the lookup locally over the cached data. If this property is set to False, Devart Zoho Lookup throws the corresponding error. Default value is True.

Lookup Object

The name of a Zoho CRM object to find matching rows from.

NoMatchAsError

Determines whether to redirect the input rows that do not match at least one row in the lookup object to the Error output. By default, False.

UseFirstMatchInMultipleResults

Determines whether to pick the first row from the lookup object if more than one matching row is found. Default value is True. If set to false, the input rows, for which multiple matches are found, are redirected to the Error Output with the corresponding error message.

UseNullsIfNoMatchFound

Determines whether to redirect rows with matches not found to the match output and fill the corresponding lookup columns with NULLs for such rows. By default, False.


Devart Zoho Source

Devart Zoho Source extracts data from Zoho CRM objects. It has the following custom properties:

Property

Description

BatchSize

The number of records to fetch in one batch. Default value is 500.

SelectStatement

An SQL SELECT statement that queries Zoho CRM objects.