When I create a OLE DB source, I can use the following expression:
STR(1234.545, 8, 2)
But when I use a Derived-Column flow task, that expression is illegal. The design-time error states:
The function "SET" was not recognized. Either the function name is incorrect or does not exist.
I'd like to know why the function is not available to the Derived-Column task. And in general, I'd like to know the rule governing which functions are in fact available, or perhaps a list of the functions that are available to the Derived-Column task.
To answer what functions available in a Derived Column Transformation, look at Integration Services Expression Reference. In particular, you are probably interested in String Functions and Other Functions (SSIS Expression)
Why SET works in OLE DB and not in derived column is rather straight forward, they're different technologies. The set statement runs against a database and is a declarative style language (SQL) while the derived column expression is an imperative style language (vb/c#/etc).
BTW, I have no idea what the reference SET statement would do or what OLE DB provider that would work for. Perhaps it was just an example.