Jeff Moden (7/24/2014)
opc.three (7/24/2014)
To anyone reading this article and subsequent comments, know that BULK INSERT introduces problems and carried with it many more hidden limitations than it offers benefits. BCP (the command-line counterpart to T-SQL's BULK INSERT) and SSIS, even the SqlBulkCopy class in .NET (the .NET counterpart to T-SQL's BULK INSERT), are components that offer you more flexibility than does BULK INSERT when designing a data-solution. The perspective of the BULK INSERT command with respect to the file, i.e. that the path you provide to the BULK INSERT command must be visible and permissions must be granted from the server hosting SQL Server's perspective, are the haranguing downfall of BULK INSERT when working in a distributed computing environment where your application is likely not running on the same server where your database instance is being hosted.As with SSIS or any other tool, there does have to be a proper connection to wherever you're importing the data from. Ostensibly, ETL doesn't happen by accident and there should be a plan as to both where the data will live and who or what can import it in the form of privs. I have a large distributed environment where different data lives on different machines and have not had the problems that you speak of.
That is because your SQL Server service account has been granted and extensive set of permissions within your distributed environment. Not everyone is OK with the approach to let SQL Server reach out to the file system for much else than writing backups.
Every tool has its requirements for use, seemingly annoying nuances, and workarounds. For well formed and consistent data, BULK INSERT is a great tool for me.
For one-off tasks BULK INSERT can come in handy but I avoid incorporating it into permanent solutions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato