I was horrified to discover that Microsoft SQL Server's text import/export tools don't even support UTF-8. Like, at all. You can either use their bastardized wrongendian pseudo-UTF-16, or just pick a code-page and go pure 8-bit.
And obviously it supports arbitrary text-encodings, although sometimes you will need to be explicit about it.
If you used the simplified wizards, all the options may not have been there, but you should have been given the option to export/save the job as a package, and then you can open, modify, test and debug that before running the job for real.
Seriously. SQL Server has some immensely kick-ass and über-capable tooling compared to pretty much every other database out there.
To even suggest it doesn't support UTF8 is ludicrous.
SSIS is insanely powerful and performant, but it's also insanely cumbersome and script-unfriendly. Microsoft has finally started embracing the power of plain text and scriptable tools in their web-stack and .NET, but SSIS represents a holdover from their heavyweight GUI-and-wizard days.
That said, if you are doing repeatable jobs (and not just one-off imports) you can still create a SSIS package, and then run the package from your script using the package-runner and appropriate config-data.
So why would someone even use bcp instead of SSIS? SSIS might be nice for performing repeated imports of data that has a fixed format, but for quick and dirty exports/imports it's really frustrating to use. It's not even smart enough to scan an entire data file and suggest appropriate field lengths and formats. Every single time I try to import a .csv file it craps out and doesn't even show where the error occured - that's after clicking through a bunch of steps in a GUI. At least with BCP you can easily rerun the import/export from the command line.
SQL and SQL Management studio are generally great but I would not include SSIS when lauding them.
char [ ( n ) ] Fixed-length, non-Unicode string data.
http://technet.microsoft.com/en-us/library/ms186939.aspx
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
So, (var)char is "non-Unicode", and n(var)char is UCS-2 only.
That is in agreement with http://blogs.msdn.com/b/qingsongyao/archive/2009/04/10/sql-s..., which claims the glass is half full ("In summary, SQL Server DOES support storing all Unicode characters; although it has its own limitation.")
On the other hand, we have http://msdn.microsoft.com/en-us/library/ms143726.aspx that seems to state that SQL Server 2012 has proper unicode collations. UTF8 still is nowhere to be found, though.
If you want to treat data as a stream of bytes hardcore UTF8 & PHPesque style (this function is "binary safe" woo) with no regard to the actual text involved, feel free to store it a bytes. SQL Server supports that.
If you want to store it as unicode text feel free to use the ntext and nvarchar types. I'm pretty sure that's what you intend to do anyway, even though you insist on calling it UTF8.