Configuring the Destination
When you've looked at the Data Preview window and you're satisfied that all the settings are right, you're ready to set up the destination file. For this example, I created a database called Addresses and set the wizard to load the data into that database. The wizard lets you create a new database by clicking New in the Choose a Destination dialog box that Figure 5 shows. I typed in the name Addresses in the resulting Create New Database dialog box, accepted the rest of the default settings, and closed the dialog box by clicking OK. The Addresses database is now selected in the Database field of the Choose a Destination dialog box. So in this step, I created a database and the destination connection.
After you set up the destination connection, you need to specify which table to load into and how to map the columns from the source flat file to the destination table. Clicking Next in the Choose a Destination dialog box brings up the Select Source Tables and Views dialog box that Figure 6 shows. This figure shows only one table because I'm importing from a flat file. If I were importing from a database, I'd likely have more than one table from which to choose. I selected the AWCustomers.txt file because that's my source data file. I also need to define the column mappings to tell the wizard where in the destination table to put each flat-file column. I do that by clicking Edit..., which brings up the Column Mappings dialog box that Figure 7 shows.
The Column Mappings dialog box is where you map the source columns to the destination columns. You can remove columns by clicking the destination for a column and selecting <ignore> from the drop-down box that pops up. Note that I selected the Nullable box for the EMail Address and Address Line 2 columns. Selecting Nullable tells the wizard to let these columns be NULL when it's creating the destination table.
Because I'm loading data to the destination table for the first time, I need to create the table. Clicking Edit SQL in the Column Mappings dialog box opens the Create Table SQL Statement dialog box, which shows the script DTS has automatically generated to create the destination table. You'll notice that in the generated script, there's no NOT NULL constraint for EMail Address and Address Line 2 because I selected Nullable in Figure 7. If I need to modify the script in any way, I can do so here. Clicking OK in the Create Table SQL Statement dialog box and again in the Column Mappings dialog box takes you back to the wizard's Select Source Tables and Views screen, which Figure 6 shows.
Saving and Running the Package
To save the new DTS package you've created, click Next to open the Save or Schedule Package dialog box. This is where you save the package either to SQL Server or to an XML file by using the DTSX filename extension. Saving to XML is new in DTS 2005. XML files are easier to read and modify than packages saved in structured storage. Also, more tools are available to support processing XML files. So Microsoft decided to save packages to XML and phase out support for structured storage files.
You also have the option to schedule a package to be executed. In SQL Server 2005 Beta 2, this option is disabled, and the DTS development team still hasn't decided how this feature will work, so this dialog box will likely change. Note that two save options that were available in the DTS 2000 Import/Export Wizard are missing. DTS no longer supports saving to Metadata Services because Microsoft has phased it out. And saving to script isn't currently supported in Beta 2 but might be supported by the time DTS 2005 ships.
Depending on which save option you chose—SQL Server or XML file—the next dialog box lets you enter a SQL Server or XML file name to save to. SQL Server will save the sample package, for example, in a new table in the sysdtspackages90 MSDB database. If you choose to save to XML, you get a different dialog box than you get if you choose to save to SQL Server.
After you give the wizard all the information it needs to save the package, you'll see a summary in the Complete the Wizard window, which Figure 8 shows, that reports all the operations the wizard has been configured to perform. Showing everything together in one report like this is another way the new wizard makes double-checking your work easier. If you're happy with the setup and the summary looks right, you can execute the package. If you selected the Run Immediately option in the Save or Schedule Package dialog box, clicking Finish in the Complete the Wizard window will run the resulting package. If you click Finish and you get errors, you can go back into the wizard and modify settings. This feature is great because you can test the package and correct any errors while retaining all the settings.
After you tell the wizard to execute, it first must build the package. Even if you choose not to run the package immediately, the status window that Figure 9 shows will appear when you click Finish on the summary screen. All the rows in the Action column up to the Saving entry are reporting the success or failure of package creation. Because I opted to run the package immediately, the Action column also shows additional rows such as the Executing, Post-execute, and Cleanup steps that report the results of executing the package. If errors occur when the package runs, an Error link will appear in the Message column. Clicking the link opens another window that shows you the errors.
In this example, the status window shows that the package execution was successful, so clicking the 18508 rows transferred link in the Message column will show the package output. DTS 2005 output is quite verbose compared to DTS 2000 output, and the extra information should help you understand what's happening inside successful packages as well as troubleshoot problem packages. Notice also in Figure 9 the Report button, which lets you save the package output to a report in a file, save it to the Clipboard, or send it as an email message. Clicking Filter lets you filter the entries. For example, you can filter to show only failed or only successful operations.
As you can see, the new DTS Import/Export Wizard still looks a lot like the old wizard, but it has some substantial changes. The new wizard focuses on moving data, with the transform and transfer features gone, and it lets you more easily see and correct problems as you build packages.