OData Source - SQL Server Integration Services Design Patterns - page 338

OData supports multiple query options that can be included with a request. Query op-
tions start with a dollar sign ( $ ) and are added to the query string portion of the URL,
following the resource path. These options are typically used to pass filters to the ser-
vice or to limit the number of rows or columns in the result set. Some OData services
may not support all of the available query options, so sometimes you will need to ex-
periment. Table 12-3 shows some of the more commonly used query options you will
use when working with SSIS. You can use the query options in SSIS by specifying
them in the Query Options box in the OData Source Editor, or by directly setting the
Query property value on the component.

Table 12-3 . OData Query Options

Option Description

$select Comma-separated list used to specify which columns should be returned by the

service. Equivalent to the SELECT statement in T-SQL.

Restricts the result set to rows that match the specified filter expression. Equi-
valent to the WHERE clause in T-SQL.

$filter

Specifies the order in which results are returned from the service. Equivalent to
the ORDER BY clause in T-SQL.

$orderby

Limits the number of rows returned from the service. Equivalent to the TOP ex-
pression in T-SQL.

$top

$skip Indicates that the service should ignore the first N rows of the result set, where

N is specified by the $skip argument.

This option allows you to override the default format of the response. Valid op-
tions are json , atom , and xml . SSIS will try to use the json format by de-
fault, since it is the most concise and tends to offer the best performance (espe-
cially when the service supports JSON with minimal metadata—also known as
JSON light).

$format

Configuring the OData Connection Man-
ager

The OData Connection Manager is where you specify the service document URL (i.e.,
the root URL of the OData service) and authentication information. The connection
manager supports three authentication types: Windows, Basic (username and pass-
word), and Microsoft Online Services Authentication. You can configure Windows and