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.
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. |
After this you may add other Data Flow components and connect Devart Source data flow path to them.
|
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 first records of the query execution result. By default, it displays the first 100 records. If necessary, you may change the Number of Rows to display in this window. |
| • | The Properties area displays properties of the currently selected table, column or variable. |
|
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. Simple SELECT statements are translated into cloud API calls and executed against the cloud source directly. For more complex statements, all the data from cloud objects, mentioned in the query, is queried to the local cache, and the query is executed locally, against the cache.
You can disable local SQL execution by setting the Local SQL Engine parameter in the advanced parameters of your cloud connection manager to False. In this case more complex statements that cannot be converted to API calls will simply fail.
|
Variables can be added to the query text in two ways. By default, they are added quoted with single quotation marks - as SQL string literals.
In this way, the variable value is also enclosed in single quotation marks and any single quotation marks in it are escaped, i. e. the variable value is substituted as an SQL string. However, if you need, you may even pass a set or even an arbitrary SQL fragment as a variable value. For this, just remove these single quotation marks:
The query editor highlights such a SQL statement as invalid, but this SQL can be used if it is valid after substituting a variable value. You can use the following variable value: cities = 'Berlin', 'Hamburg', 'Hannover', and as the result, the following SQL will be run:
SELECT * FROM Account
WHERE BillingCity IN ('Berlin', 'Hamburg', 'Hannover')
|
How to use Devart Source Components