Wednesday, May 07, 2014

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

image

$orderby

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

image

$filter

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

image

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

image

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

image

$top

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

image

$select

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

image

$inlinecount, $format

Not usefull for SSIS purposes.

$expand

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

1 comment:

Unknown said...

Just wondering if you have had any luck with $expand yet. I am trying to source from a SharePoint list with a collection datatype and was hoping I could make it work with $expand.

Thanks!