Microsoft has completely rewritten Data Transformation Services (DTS) in SQL Server 2005 for many reasons. In addition to streamlining DTS's functionality and making it easier to use, the rewrite improves scalability and performance. In "What's New In DTS?" (May 2004, InstantDoc ID 42141), I gave an overview of DTS's highlights, including the design goals of the rewrite and the new features of the DTS Designer. With the July release of a broad private beta and with the SQL Server 2005 public beta drawing close, it's time to take a closer look at some specific DTS enhancements. Many SQL Server 2000 DTS users work primarily or only with the Import/Export Wizard, so let's start the closer inspection of DTS 2005 with this wizard. Although the Microsoft DTS development team I work on has improved and expanded the entire DTS 2005 toolset, the Import/Export Wizard is still a great way to quickly build packages for moving data. The rewritten Import/Export Wizard includes two notable changes. First, the Import/Export Wizard no longer supports copying objects between SQL Server databases. To copy objects, you use the Transfer Database Wizard, which you launch from SQL Server Management Studio, the new SQL Server 2005 management suite that replaces Enterprise Manager, Query Analyzer, and other utilities. Second, the rewritten wizard no longer supports building script transformations.
Why did the team make these changes? The simple answer is that we needed to refocus and simplify the wizard so that it's only a data-movement utility—not an extraction, transformation, and loading (ETL) tool. We learned that having the copy-objects functionality in two places—in the DTS Import/Export Wizard and the Copy Database Wizard—sometimes confused users. To eliminate this duplicate functionality, we consolidated the copy functions into the Copy Database Wizard. The change should clarify which tools to use for specific tasks. For quickly building packages to move data from one place to another, use the Import/Export Wizard. For moving database objects—from whole databases to bits and pieces of a database—use the Transfer Database Wizard. And to create packages that perform simple to complex transformations on data at high throughput rates, use the DTS Designer.
Although we simplified the Import/Export Wizard, it's still a fairly complex and flexible tool. Depending on the settings you choose and the options you select, you can take many different paths through the wizard, so I can't cover every wizard setting here. Therefore, as I walk you through the creation of a simple data-import package in the enhanced Import/Export Wizard, I highlight features that are different from DTS 2000. In a future article, I'll explain what the wizard is doing behind the UI by showing and editing the resulting package in the DTS Designer.
Getting Started
If you're an Enterprise, Universal, or Professional Microsoft Developer Network (MSDN) subscriber, you can download the DTS 2005 Import/Export Wizard at http://msdn.microsoft.com/subscriptions/downloads. To simulate a typical data-import scenario for this article, I generated a flat file containing customer-address data from the SQL Server 2005 AdventureWorks sample database. I exported customer addresses by using the T-SQL query that Web Listing 1 shows to combine some of the AdventureWorks tables into a flat file called AWCustomers.txt. (You can download the listing at InstantDoc ID 43805.) I've purposely kept the data simple so that we can focus on the tools. This example simulates a typical data load from a flat file into a SQL Server table.
You can launch the Import/Export Wizard from four different locations. At the command prompt, you can type DTSWizard.exe to launch the wizard. In the Business Intelligence Workbench—the new DTS development environment that you can launch from the SQL Server Start menu—you can launch the wizard from the DTS Packages project node. As in SQL Server 2000, SQL Server 2005 setup adds a DTS Wizard menu item to the SQL Server menu tree in the Start menu, so you can also launch from there. And in SQL Server Management Studio, you can launch the wizard from any database node. The first thing you'll see when you launch the wizard is the welcome screen. The first time you run the wizard, select the Do not show this starting page again check box and rid yourself of this extra step on subsequent runs.
Configuring the Data Source
Figure 1 shows the next window, the Data Source dialog box, where I specify the source data. The drop-down box at the top of the window is for choosing the data-source provider type. This drop-down box also hides a subtle difference in how DTS now handles flat files. In SQL Server 2000, DTS uses a text OLE DB driver to access flat files. DTS 2005 introduces a new pipeline component called an adapter. (To learn about the pipeline—aka the Data Flow Task—see "What's New in DTS?") Source adapters are the pipeline components that interact with source systems and files. So, this drop-down box actually shows available providers and adapters together. For this example, I want to import a flat file to SQL Server, so I selected the Flat File Data Source. In the File name box, I specified the AWCustomers.txt file, which you can download at InstantDoc ID 43805.
Prev. page
[1]
2
3
next page 
