Get Schooled at New Horizons

Info and insights from New Horizons Computer Learning Centers!
Start exploring

Finding the most hassle-free data import approach in SQL Server

When importing or exporting data to/from Microsoft SQL Server, you have many options: bcp, .NET’s SqlBulkCopy class, SSIS, linked servers, OPENROWSET, and BULK INSERT. Each of these solutions tends to perform faster than other approaches that are further removed from SQL Server. Nevertheless, choosing the right solution from this list depends on your situation. In particular, these approaches differ markedly in terms of the setup requirements.
Generally, OPENROWSET and BULK INSERT are used for one-off or on-the-fly queries that access an external data source. However, they can be a problem in custom applications if there’s a reason why the application shouldn’t have the elevated privileges required to execute them. To make these work, the ad hoc distributed queries option must be set:
sp_configure ‘show advanced options’, 1;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
Furthermore, OPENROWSET and BULK INSERT syntax both involve specifying a “provider” that will actually read the external data. For this to work, certain dlls have to be found on the machine. Many of the examples posted online involve outdated 32-bit drivers, and it’s not guaranteed that any given syntax will work in your environment.
Linked servers use the very same “provider” syntax but set up a permanent linked to an external data source. The ad hoc distributed queries option isn’t required; so, to a degree, the requirements for linked servers are a little lower. Also, in addition to using sp_addlinkedserver to create a linked server, you can also create one in GUI fashion right from the SQL Server Management Studio. A possible disadvantage of linked servers, however, is that applications that rely on them may fail if the database is transferred to another server. The problem is that details such as linked servers often get missed when it comes to knowledge management, so future staff may not know that a linked server needs to be set up for a certain application to work properly.
SSIS is a very popular tool for data import, because of its power and drag-and-drop GUI interface. Another advantage is that many people have been trained on SSIS, facilitating future support for any solution that uses it. However, a number of components need to be installed to develop SSIS packages and have them work properly in development mode.
Bcp, which stands for Bulk Copy Program, is a single executable to which you pass parameters. While it’s much older than SSIS and has some limitations, in many ways it’s the simplest option for performing basic imports and exports. Another simple option is .NET’sSqlBulkCopy class, which is designed for fast-performing import/export operations integrated directly in one’s application code. If your code reads in data from an external source, this class facilitates fast import into SQL Server, with just a connection string and a DataTable.

Check our all of our SQL Server courses!

Leave a Reply