I currently have a windows application which is automated and runs daily. the purpose is to access a webservice to download a dataset, inserting into sql 2005 database.
Is a windows service application suitable in this situation, would it be more flexible, and would it perform better.
A Windows Service would allow you to run your task even though no user is actively logged on. I would say, for a task like that, a Windows Service is more suitable. I doubt, however, that there would be any performance improvement.
You can definitely have it as a service, but I don't think you are going to get any benefit from it. Since a service is always running, they are normally used for applications which have to run, because they are constantly checking for a condition (waiting for remoting, checking every n minutes for information in a database etc.).
Since yours runs once a day, you aren't going to have any advantage if you change it over. If your automated task is setup correctly, it should run if the machine is on, just like a service. The advantage to having a windows app (console specifically) over a service is that if something fails, you can just start the app again and run it. This won't be so easy with a service, because extra code will have to be in the program to make sure that it only runs so many times (in your case once) a day. You probably won't be able to have it execute your process on start up, because you have to take into account the server being restarted. This means that if your server goes down when the process is supposed to run, you are going to have to know how to "trick" the program into thinking that it should run the process for that time only. Window's apps don't suffer from this, because they terminate after the process has completed, so there's probably no additional code to prevent it from running the process again.
If it just needs to run once daily (or just periodically), then making it a service is kind of a waste in my opinion. Most of the time it would just be doing nothing.
There's nothing about services in particular that would improve performance, so I would recommend just leaving it as a plain-old application and using scheduler to run it once daily.
If you are inserting the data in to SQL Server then you clearly have a dependency on that anyway. I would investigate scheduling using the SQL Agent.
Depending on your processing requirements you may find you can do the whole lot using SQL Server Integration Services (SSIS) which is well documented on how to schedule withing SQL Server.
I know you mention you've already written this. But with SSIS / SQL Agent you get a level of monitoring 'for free' and you may find that it is little effort to replicate you current code. Also ongoing a DBA can maintain your code very easily without needing access to source code etc.