The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server data loading

What are the points in favor and against the following methods for loading data from a file into a SQL Server table using a C# service:  1) bulk insert, 2) calling xp_cmdshell with a bcp command, 3) client-based DTS, 4) client-based bcp.
Also, we are using SQL Server 2000, but would the advice be different for SQL Server 2005?
Thursday, July 05, 2007
I'd recommend using the SQLBulkCopy class available in the .NET Framework. We've seen pretty good performance out of it in our application and it doesn't require that the user have any elevated permissions in SQL Server (as compared to the options you've listed).
Tim Lentine Send private email
Thursday, July 05, 2007
Stay away from xp_cmdshell, it can become a huge security hole and it is disabled by default in SQL 2005.

Use bulk copy if you want to bring the data in exactly as it is.

Use DTS if you need to do any data tweaking as you bring it in, and you wnat to use SQL Server tools for management and scheduling of the job.

Write your own transformation process if you want to run it under the control of your application and you need to do data tweaking.  I've found that embedding DTS in services usually turns out to be more extra work than it's worth.
JSmith Send private email
Thursday, July 05, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz