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 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



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


$inlinecount, $format

Not usefull for SSIS purposes.


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


Christopher Hall 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.


Anonymous said...

Off - but on topic - how do you get around rounding errors - for example the price is 2.5 in one of your screen shots - in my case we have 7.99 of numeric precision 18 scale 0 - the 6.99 becomes 7.00. target is flat file. can't change the odata data source for any data type except the string types. tried to send to sql db also and change target data type to numeric with 18,2 also tried decimal. still get rounding error. ever encounter this?