- Need for Data Transfer
- Advantages of DTS Over Its Predecessors
- DTS Tasks
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Advantages of DTS Over Its Predecessors
DTS has a number of advantages over its predecessors. With DTS, you can import data from any data source for which you have an OLEDB or ODBC provider. Whether your company stores its data in relational databases such as Oracle, or in a non-relational format such as email stores or Lotus Notes, DTS can get around importing such data. While moving data, you can also manipulate it and store it in the desired format.
DTS capabilities are not limited to data transfer. DTS also provides an excellent way to automate some of the administrative tasks. For instance, you can import the data from an external data source to a staging table, call a stored procedure to give the imported data the particular shape you need, and then kick off an Analysis Services cube processing task—all from the same DTS package. If necessary, you can also send an email notification to the responsible employee in case of a package failure.
DTS probably wouldn't be as popular if it did not have a very nice intuitive user interface. You can create a package from the DTS Designer, using the DTS wizard, or through code. The DTS Designer can be accessed by expanding the Data Transformation Services folder in Enterprise Manager, right-clicking on Local Packages, and selecting New Package. The DTS wizard can be accessed in several different ways—the easiest is selecting Import and Export Data from the SQL Server menu. The wizard lets you answer a few simple questions, and gets you well on the way of developing your packages. The DTS Designer lets you pick from the list of tasks and then customize each task for your needs.
Perhaps one of the best things about DTS is that it is extensible. SQL Server 7.0 only provided eight built-in tasks. SQL Server 2000 provides 19 built-in tasks—which, in most cases, will be more than sufficient. However, each of these tasks can be customized through the DTS Object Model. In addition, you can develop your own custom tasks, and register them with SQL Server.
SQL Server also provides a way to secure your DTS packages. You can set a user password and an owner password to each package. The users can only execute the package, whereas the owner can make changes to the package.
Last but not least, DTS comes free when you purchase any edition of SQL Server 2000 (developer, desktop, standard, or enterprise editions are available). In fact, you don't even have to have SQL Server installed on your computer to run DTS—you can use DTS to transfer data among non-SQL Server data sources just as well.