A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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
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.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz