SQL Server has never been short of ways to read from and write to files and it is always better to use the standard techniques provided by SQL Server where possible. However, most of them are really designed for reading and writing tabular data and aren't always trouble-free when used with large strings or relatively unstructured data.
For reading tabular data from a file, whether character-delimited or binary, there is nothing that replaces the hoary old Bulk Copy Program (BCP), which underlies more esoteric methods such as Bulk Insert. It is possible to read text-based delimited files with ODBC, simple files can be read and written-to using xp_cmdshell, and you will find that OSQL is wonderful for writing results to file, but occasionally I've found I need to do more than this.
Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from TSQL. In this article, I provide examples of stored procedures that use this interface to allow you to:
View Complete Post