CHAPTER 22 • DATA TRANSFORMATION SERVICES 820 Launching the Wizards There are many ways to launch the DTS Wizards. To launch the DTS Wizards from within SQL Server Enterprise Manager, follow these steps: 1. Select any node in the treeview at or below the level of a SQL Server. 2. Choose Tools ➢ Data Transformation Services ➢ Import Data to launch the DTS Import Wizard or Tools ➢ Data Transformation Services ➢ Export Data to launch the DTS Export Wizard. Alternatively, you can launch the DTS Wizards from the Select Wizard dialog box by following these steps: 1. Select any node in the treeview at or below the level of a SQL Server. 2. Choose Tools ➢ Wizards or click the Run a Wizard button on the toolbar. Either of these actions will open the Select Wizard dialog box. 3. Expand the Data Transformation Services node in the dialog box. 4. Choose DTS Export Wizard or DTS Import Wizard. 5. Click OK to launch the Wizard you’ve chosen. You can also launch the DTS Wizards from the Data Transformation Services node in SQL Server Enterprise Manager: 1. Select the Data Transformation Services node under any server name in the treeview. 2. Select Action ➢ All Tasks ➢ Import Data to launch the DTS Import Wizard or Action ➢ All Tasks ➢ Export Data to launch the DTS Export Wizard. Alterna- tively, you can right-click the Data Transformation Services node and choose All Tasks ➢ Import Data to launch the DTS Import Wizard or All Tasks ➢ Export Data to launch the DTS Export Wizard. To launch the DTS Wizard from the Windows Start menu, choose Start ➢ Programs ➢ Microsoft SQL Server ➢ Import and Export Data. Finally, you can launch the DTS Wizards directly from the Windows command prompt. The simplest way to do so is just to type (or run from a batch file) the name of the launcher program: dtswiz If entered with no options, this will have the same effect as choosing the Import and Export Data menu item from the Start menu. Optionally, you can supply a num- ber of arguments to the dtswiz program. Table 22.1 lists the command line switches that you can use with dtswiz. 2627ch22.qxd 8/22/00 11:16 AM Page 820 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 821 TABLE 22.1: DTSWIZ ARGUMENTS Argument Meaning /? Display help for dtswiz /n Use Windows NT integrated security (overrides the /u and /p arguments) /f filename Save the package to the specified file /i Run the DTS Import Wizard /x Run the DTS Export Wizard /s servername Import to or export from the specified SQL Server /u username Username for SQL Server security /p password Password for SQL Server security /d database Import to or export from the specified database /y Do not display the system databases in the Wizard interface /m Force all DTS tasks to use one thread of execution Running the Wizards As you might guess from the options shown in Table 22.1, there’s really only one DTS Wizard rather than three. The Import and Export Wizards are just the main DTS Wiz- ard, launched by SQL Server Enterprise Manager with the /i or /x switch to set some defaults. You’ll find that the panels and options in all three variants of the Wizard are very similar. In this section, you’ll see the general format of the Wizard. Just remem- ber that if you’ve launched it as an Import or Export Wizard, some of the settings will be prepopulated for you. The initial panel of the DTS Wizard explains the purpose of the Wizard. There’s nothing to do on this panel except click the Next button. When you do that, you’ll see the Choose a Data Source panel, shown in Figure 22.1. DTS IN THE USER INTERFACE Development with SQL Server PART V 2627ch22.qxd 8/22/00 11:16 AM Page 821 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 22 • DATA TRANSFORMATION SERVICES 822 FIGURE 22.1 Choose a Data Source panel in the DTS Wizard The Choose a Data Source panel consists of three parts. At the top, you can select the source for the data. This combo box allows you to choose any OLE DB provider that’s installed on your system as the data source. For example, you can choose to copy data from a SQL Server database (the default), a Microsoft Access database, or an Oracle database. Below this combo box, there is a section that prompts for driver-specific information. At the bottom of the panel, there are the Wizard navigation buttons. When you select a data source from the combo box, the middle frame changes to prompt for the information required by that type of data source. For a SQL Server data source, for example, you need to choose a server, supply authentication information, and choose a database. There’s also an Advanced button in this case that lets you set the more obscure OLE DB options for the SQL Server OLE DB driver. On the other hand, if you choose to use a Microsoft Access data source, the frame’s controls change to prompt you for only the filename, username, and password to use when opening the Access database. After you choose a data source and click Next, the Wizard will show the Choose a Destination panel. This panel is an exact copy (except for the caption) of the Choose a Data Source panel. Once again, you can choose any OLE DB database as the target for your DTS operation. Because both the Data Source and Destination can be arbi- trary data sources, there are three basic modes of operation of the DTS packages that the Wizard can create: • Import data from any data source to SQL Server 2627ch22.qxd 8/22/00 11:16 AM Page 822 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 823 • Export data from SQL Server to any data source • Transfer data between two data sources, neither of which is SQL Server No matter which mode you choose, when you click Next from the Choose a Desti- nation panel, SQL Server will present the Specify Table Copy or Query panel, shown in Figure 22.2. FIGURE 22.2 Specify Table Copy or Query panel in the DTS Wizard There are three choices on this panel. If you want to move entire tables from the source to the destination, choose Copy Table(s) and View(s) from the Source Database. Note that you can still use this option to copy a partial table if you’ve defined a view in the source that includes only the data of interest. Alternatively, to define the data to be copied using a SQL statement, choose Use a Query to Specify the Data to Transfer. Finally, if the source and destination are both SQL Server databases, you can choose to transfer SQL Server objects with all of their properties and data. This last choice will be disabled if either the source or the destination is not a SQL Server database. The next panel in the Wizard depends on your choice in the Specify Table Copy or Query panel. If you choose to copy tables and views, you’ll see the Select Source Tables and Views panel, shown in Figure 22.3. DTS IN THE USER INTERFACE Development with SQL Server PART V 2627ch22.qxd 8/22/00 11:16 AM Page 823 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 22 • DATA TRANSFORMATION SERVICES 824 FIGURE 22.3 Select Source Tables and Views panel in the DTS Wizard This panel lets you perform a number of operations related to selecting the data: • To include a table or view in the data to be transferred, check the checkbox to the left of the table or view’s name. • To specify a destination table for the data, select it from the drop-down list in the Destination column. This list includes all of the tables in the destination database. It will default to a table of the same name as any selected source table, if possible. • To see the data in a source table, select the table and click the Preview button. SQL Server will display a dialog box containing up to 100 rows from this table. • To customize the way that the data is transferred from the source to the destina- tion, select the source and destination tables, and click the Browse button in the Transform column. This will open the Column Mappings, Transformations, and Constraints dialog box, shown in Figure 22.4. 2627ch22.qxd 8/22/00 11:16 AM Page 824 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 825 FIGURE 22.4 Column Mappings, Transformations, and Constraints dialog box The Column Mappings, Transformations, and Constraints dialog box allows you to customize the way that data is moved from the source database to the destination database. For any table, you can use this dialog box to perform these actions: • To decide whether to create the destination column from scratch, delete rows from the destination table, or append rows to the destination table, choose the appropriate option on the Column Mappings tab. • To change which source column is mapped to which destination column, choose a source column from the drop-down list in the Mappings section of the Column Mappings tab. • To perform calculations on the data as it’s moved from the source to the desti- nation, choose the Transformations tab and write a VBScript or JScript proce- dure. Figure 22.5 shows a sample of such a procedure. • To create primary or foreign keys on the destination table, select Create Destina- tion Table on the Column Mappings tab and then check the appropriate boxes on the Constraints tab. DTS IN THE USER INTERFACE Development with SQL Server PART V 2627ch22.qxd 8/22/00 11:16 AM Page 825 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 22 • DATA TRANSFORMATION SERVICES 826 • To customize the CREATE TABLE statement used to create the destination table, select Create Destination Table on the Column Mappings tab and then modify the SQL statement on the Constraints tab. FIGURE 22.5 Using VBScript to transform a table When you click Next on the Select Source Tables and Views panel, you’ll be taken to the panel Save, Schedule, and Replicate Package. We’ll discuss this panel in a few pages. First, though, we’ll back up and cover the other choices on the Specify Table Copy or Query panel. If you choose the Use a Query to Specify the Data to Transfer option on this panel and click Next, the Wizard will show the Type SQL Statement panel. You can enter any SELECT statement that returns data from the source data- base in this panel. If you prefer, you can click the Query Builder button. This will dis- play the Select Columns panel, shown in Figure 22.6. 2627ch22.qxd 8/22/00 11:16 AM Page 826 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 827 FIGURE 22.6 Select Columns panel in the DTS Wizard The Select Columns panel is the start of a three-panel sequence that collects all the information necessary to create a SELECT statement: 1. Select Columns 2. Specify Sort Order 3. Specify Query Criteria WARNING Although the Query Builder panels allow you to select columns from more than one table, the constructed SQL statement will not include any joins. You’ll need to put in any JOIN clauses manually by editing the final SQL statement. When you type or build a SQL statement and click Next, the Wizard proceeds to the Select Source Tables and Views panel that we discussed above. From this point, the flow of the Wizard is the same as it is in the case of copying tables. If you choose Copy Objects and Data between SQL Server Databases on the Specify Table Copy or Query panel and click Next, the Wizard will show the Select Objects to Copy panel, shown in Figure 22.7. DTS IN THE USER INTERFACE Development with SQL Server PART V 2627ch22.qxd 8/22/00 11:16 AM Page 827 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 22 • DATA TRANSFORMATION SERVICES 828 FIGURE 22.7 Select Objects to Copy panel in the DTS Wizard The Select Objects to Copy panel allows you to choose SQL Server objects that will be moved from the source server to the destination server by scripting. You can set the following options on this panel: • To create objects in the destination database, check the Create Destination Objects checkbox. You can also choose whether to drop any objects with the same name before creation and whether to automatically create dependent objects. • To copy data from the source database to the destination database, check the Copy Data checkbox. You can also choose whether to replace existing data or append new data to the destination table. • To transfer all objects, check the Copy All Objects checkbox. • To transfer only some objects, uncheck the Copy All Objects checkbox and click the Select Objects button. This will open the Select Objects dialog box, where you can choose tables, views, stored procedures, defaults, rules, and user- defined datatypes to be transferred. • To set advanced options for the transfer, uncheck the Use Default Options check- box and click the Options button. This will open the Advanced Transfer Options dialog box. This dialog box lets you choose whether to transfer users, roles, logins, permissions, indexes, triggers, full-text indexes, and primary and foreign keys when transferring tables. You can also choose whether SQL Server should use Unicode names for objects and whether it should use quoted identifiers. 2627ch22.qxd 8/22/00 11:16 AM Page 828 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 829 • To set the location for the script files that will be generated to perform the trans- fer, type a location into the Script File Directory textbox or use the Browse but- ton beside the textbox to choose a directory. When you click Next in the Select Objects to Copy dialog box, the Wizard will dis- play the Save, Schedule, and Replicate Package panel, shown in Figure 22.8. From this panel forward, the sequence of the Wizard is the same no matter which option you chose on the Specify Table Copy or Query panel. FIGURE 22.8 Save, Schedule, and Replicate Package panel in the DTS Wizard The Save, Schedule, and Replicate Package panel lets you set these options: • To run your DTS package at once, check the Run Immediately checkbox. • To schedule the DTS package for later execution, check the Schedule DTS Pack- age for Later Execution checkbox. The Browse button will let you set the sched- ule time and frequency. You can schedule the job to run as often as once a minute or as infrequently as once a month or less. • To make the results of this DTS package available to replication subscribers, check the Use Replication to Publish Destination Data checkbox. You’ll learn more about replication in Chapter 27. • To save the DTS package so that you can run it again in the future (either from the user interface or according to a schedule), check the Save DTS Package checkbox. We’ll discuss the options for saving DTS packages in the next section. DTS IN THE USER INTERFACE Development with SQL Server PART V 2627ch22.qxd 8/22/00 11:16 AM Page 829 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... which makes it unsuitable if the data isn’t already in the exact correct format Copy SQL Server Objects task: The Transfer SQL Server Objects task uses SQL- DMO to move entire objects from one SQL Server to another This task can move the same types of objects that the DTS Wizard can move when working in native SQL Server mode Data Driven Query task: The Data Driven Query task provides a more complex... location 2 SQL Server will open the Connection Properties dialog box For the SQL Server OLE DB Provider, this dialog box lets you choose the server, database, and authentication method to be used to connect If you’ve previously placed a connection on the design surface, you have the option to reuse that connection Fill in the properties to pick a server and database as the data source 3 Click OK SQL Server. .. The DTS Wizard offers four choices for saving a DTS package: SQL Server: This option saves the package to any SQL Server for which you have permissions The package is stored in the sysdtspackages table in the msdb database (the database that SQLServerAgent uses for its own work area) This is usually the best way to store a DTS package SQL Server Meta Data Services: This option saves the package to a... databases from one SQL Server to another Transfer Error Messages task: The Transfer Error Messages task copies error messages from one SQL Server to another You can use this task to collect all error messages generated in the course of executing a DTS package to a single location Transfer Jobs task: The Transfer Jobs task transfers jobs from the msdb database on one SQL Server to another SQL Server Transfer... maintained by SQL Server that’s designed to help track metadata about objects Third-party tools can use this metadata to write utilities that manage objects SQL Server Meta Data Services also helps track data lineage, so that you can tell where a particular piece of data originated If you’re working with packages on a single server, you usually won’t want to use Meta Data Services for storage (SQL Server. .. code for creating a sample custom task located on your SQL Server 2000 CD-ROM in the devtools\samples\ SQLDTS folder You can also create custom tasks with Visual Basic, but you’re on your own there; the SQL Server team doesn’t provide any samples Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark PA R T V Development with SQL Server 2627ch22.qxd 2627ch22.qxd 840 8/22/00 11:16 AM... run a package saved to SQL Server, follow these steps: 1 Open SQL Server Enterprise Manager 2 Expand the Data Transformation Services folder and click the Local Packages node 3 Click the package and choose Action ➢ Execute Package, or right-click the package and choose Execute Package To run a package saved to the Microsoft Meta Data Services, follow these steps: 1 Open SQL Server Enterprise Manager... To launch the DTS Package Designer, open SQL Server Enterprise Manager and expand the Data Transformation Services node under any SQL Server Then right-click either the Local Packages or the Repository Packages node and choose New Package This will open the DTS Package Designer, shown in Figure 22.11 FIGURE 22.11 DTS Package Designer PA R T Development with SQL Server V The DTS Package Designer’s user... 839 DTS IN THE USER INTERFACE 839 Execute SQL task: The Execute SQL task can send a SQL statement to any connection in the DTS package for execution File Transfer Protocol task: The FTP task allows you to move a file or group of files from one location to another You can move files from either an Internet FTP server or a directory, and post files to an FTP server or directory This task is most useful... Analysis Services Processing task allows DTS to refresh the data in a Microsoft Analysis Server cube See Chapter 28 for more information on Analysis Services Bulk Insert task: The Bulk Insert task uses the BULK INSERT facility of SQL Server to quickly move external data into a table This is the fastest way to load data to SQL Server However, you can’t do any data transformations or validation within a Bulk . Wizard /s servername Import to or export from the specified SQL Server /u username Username for SQL Server security /p password Password for SQL Server security /d. correct format. Copy SQL Server Objects task: The Transfer SQL Server Objects task uses SQL- DMO to move entire objects from one SQL Server to another. This