Adding query options to the SSIS OData Source

 

In my previous article about SSIS OData source I created an SSIS 2014 package with an OData source connected to the Odata.org sample webservice.

OData query options

The OData Source has query options that can be used to alter the collection that is being used. I have made some tests on these options.

Option	Description
$expand	Expands related entities inline
$filter	Filters the results, based on a Boolean condition
$inlinecount	Tells the server to include the total count of matching entities in the response
$orderby	Sorts the results
$select	Selects which properties to include in the response
$skip	Skips the first n results
$top	Returns only the first n the results

See also Odata uri conventions paragraph 4

Set query options in OData Source editor

In the Data Flow select and edit the OData Source. The ‘Query options:’ textbox can be used to set options

$orderby

e.g. to order by the Product name: $orderby=Name. To Verify the result, click on the ‘Preview…’ button.

$filter

e.g. select all products with Rating 3: $filter= Rating eq 3

e.g. select products released in the year 2006: $filter= year(ReleaseDate) eq 2006

e.g. select discontinued products: $filter= DiscontinuedDate ne null

$top

e.g. select top 5 products ordered by Name descending: $top=5&$orderby=Name desc

$select

e.g. select only 2 columns on the products table: $select=Price, Name

$inlinecount, $format

Not usefull for SSIS purposes.

$expand

I will investigate this further once I have better samples than the odata.org samples..