This gets a bit funky to explain, but I'll give it a good shot...
When you compose a query using a linked server within Microsoft SQL, the query itself is interpreted according to the Transact-SQL syntax rules (it is actually done at the OLE-DB level, but the rules are almost exactly the same). The results of that interpretation form the query plan, and an equivalent query is actually passed to the provider for the linked server.
In order to be able to use the Oracle specific syntax like TO_DATE, you'll need to use Microsoft SQL's OPENQUERY function.
Thank you for your reply. I am not sure if I fully understand.
Just want to be clear attached please see the screenshots I am using to create DTS package by using query. You mean I can use MS SQL language instead of Oracle sytex to transfer data set?
Or I have to create separate SP and call sp from DTS query window?