I’m trying to use Query BSC and external content type to get all employees from SQL db view and process returned items one by one in my worfklow (update SharePoint list). Because there are thousands of items in SQL, I need to break the query into smaller batches. Without that I’m getting timeouts.
My first attempt was to use Page Number filter on Read List operation. But to my understanding Query BCS action can’t handle that.
My second attempt was to use Wildcard filter and filter alphabetically by last name. But I haven't found a way to filter items that begins to “a”. It returns all items containing letter “a”.
Third attempt was to use Comparison on employee ID number – I tried to select range of items within min and max empl. ID number.
The problem is that employee ID is a number/integer column in SQL, but when I run the Query BCS action, it passes the parameter as a string.
I get an error:
The execution returned an unexpected error.
This Equals filter expects a value of Type 'System.Nullable`1[[System.Int32, mscorlib, Version=18.104.22.168, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' and not the supplied type 'System.String'.
My last attempt was to use Comparison filter on different SQL column, where I have employee ID as a text string. This filter works, but with one major problem – it obviously compares numbers as a text. It’s satisfied when subset (the begining?) of the number (text) passes the condition. For example, number 1500 fits the filter for numbers greater than 10 and less than 20.
Am I doing something wrong? Or is there any other way how to effectively query a subset of items in external content type? For example, some out-of-the box web service?
Thank you for any advice.