import and convert table data

Hi everyone,
I have some data in a CSV file, and I have to import it into a table. For some reason, I am supposed to import this data into a temp table and then move it to the original table and I have to convert it to the right data types while I do this. Is there a better way to do this and how can I give custom error messages saying, for e.g., the data type cannot be converted, the right number of records are not present etc.

Thanks for the help.

I'm guessing the "for some reason" is something like... While your little import routine is busy farting around trying to import some data, that may or may not actually load into the original table, some other processes may actually want to use the original table without it being locked for long periods of time. Some people get a wee bit upset when the company websites go down because some newbie decided it'd be a good idea to try and do bulk imports from external sources because it's easy.

And sounds like you are looking for a DTS package to import from a CSV file into a temp table, then launching a stored procedure to merge changes in after it's complete.