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.
|$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
e.g. to order by the Product name: $orderby=Name. To Verify the result, click on the ‘Preview…’ button.
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
e.g. select top 5 products ordered by Name descending: $top=5&$orderby=Name desc
e.g. select only 2 columns on the products table: $select=Price, Name
Not usefull for SSIS purposes.
I will investigate this further once I have better samples than the odata.org samples..