Importing Data Using the DTS ImportExport Wizard


Importing Data Using the DTS Import/Export Wizard

The DTS Import/Export Wizard is primarily used by beginning developers to perform simple tasks or to create basic packages that developers will later extend using DTS Designer. The procedures in this chapter demonstrate how simple it is to copy some or all of the data from one SQL Server database to another database, to use a query to filter the data being copied from a SQL Server data source, to import data from a Microsoft Access database into a SQL Server database, and to import data from a text file into SQL Server. These procedures simply expose some of the capabilities of DTS that you can use in your environment. Because data is frequently stored in a variety of formats, there is always a need to copy or move data from one data format into another data format.

Copy data between SQL Server databases

In the following procedure, you will learn how to copy data from one SQL Server database into another SQL Server database. This procedure demonstrates one of several methods that you can use to launch the DTS Import/ Export Wizard. When you finish this procedure, you will have created your first DTS package. The package will create a new table in the destination database and populate it with data from a data source as filtered through a view. This is a very simple task, and it illustrates well just how easily you can begin performing tasks with DTS using the DTS Import/Export Wizard.

Important  

Before you begin the exercises in this book, you must execute the BeforeYouBegin.cmd batch file from this book s CD-ROM to copy all the files required into the proper file structure, and then you must attach the SBS_OLTP database. Please see the book s Introduction for details.

  1. To launch the DTS Import/Export Wizard, click the Start button, point to All Programs, point to Microsoft SQL Server, and then click Import And Export Data.

  2. Click Next to select a data source from which you will copy data.

    The default data source is the default database on your local server. DTS will use the Microsoft OLE DB Provider for SQL Server to connect to this data source and will use Windows Authentication. You will connect to the SBS_OLTP database on your local server.

    Tip  

    Selecting (local) in the Server list rather than the name of your server will enable the package to connect to the local server regardless of where the package is executed. If, however, you specify a particular server name in the package and execute the package on a remote server, the package will attempt to connect to the original server rather than the local server. Understanding the effect of the server name in a package will enable you to ensure the package executes the way it was intended.

    click to expand
  3. Ensure that Use Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLTP, and then click Next to select the destination for the data. You will create as the data destination a new SQL Server database on your local server.

    click to expand
  4. In the Database list, select New so that you can specify the database name, the initial data file size, and the initial log file size for a new database.

    DTS uses the default settings for all other database properties when creating a new database. In this case, you will specify a name for the new database and use the default file sizes for the new database.

    Tip  

    If you want to specify other database properties for the new database, such as the locations for the data and log files, you must create the database using one of the standard SQL Server methods and then select it as the data destination in the wizard.

  5. In the Name box, type SBS_OLAP and then click OK. The new database is created and selected as the data destination.

    Note  

    The SBS_OLAP database is actually created when you click OK in the wizard. If you cancel the wizard after this point, the database is not dropped.

  6. Click Next to specify what DTS will copy from the data source to the data destination.

    In this procedure, you will copy a view from the source database. In subsequent procedures, you will learn how to use a query to copy database objects.

    click to expand
  7. Click Next to specify the tables and views that DTS will copy for you and to modify the schema or the data as it is copied to the data destination.

    In this procedure, you will select the Sales By Category view in the SBS_OLTP database. Since you will not be adding any Transform options in this procedure, DTS will copy the data, as filtered by the view definition, to a new table at the data destination without transforming the data in any way.

    Important  

    When a view is copied, a destination table is created by using the columns contained in the view definition and then populating them from the data source by using the view definition to filter the data. Materializing views to tables in this way can be useful for periodically copying selected database information, such as summary information about sales or orders, to an instance of SQL Server that is running on a mobile user s laptop computer. This data can then be viewed on the laptop computer when it is disconnected from the network and periodically updated by the user when he or she reconnects to the network.

    click to expand
  8. Select the check box for the Sales By Category view.

  9. In the Destination column, change the name of the new table to Sales by Category SBS , and then click the ellipsis in the Transform column to view the column mappings and transformations that DTS will perform. (You will need to scroll to the right to view the entire Destination column and locate the Transform column.)

    In the Mappings box on the Column Mappings tab, notice the mapping between the columns defined in the view and the columns that will be created in the destination table. Notice also that you can choose to have DTS drop and recreate the destination table each time the package is executed.

    In this procedure, you will accept the default column mappings and choose to drop and recreate the destination table each time the package is executed. This enables the package to completely update the data in the destination table each time the package is executed.

    click to expand
  10. Select the Drop And Recreate Destination Table check box and then click OK.

    Tip  

    You can click Preview to see a preview of the data generated by the view and its schema.

  11. Click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.

    The default behavior of the wizard is to execute the package immediately. In this procedure, you will run the package immediately as well as save the package to a structured storage file. This will enable you to verify the functionality of the package, re-execute the package manually or according to a schedule, and view and extend the package using DTS Designer. You will learn about the differences between each of these save options in Chapter 3.

    click to expand
  12. Select the Save DTS Package check box, click Structured Storage File, and then click Next to provide the details for the save options.

    You will save the package to the local file system and use an owner password to secure the package. You will learn about package security in Chapter 3.

    click to expand
  13. Type SQL_DTS_SBS_1.1 in the Name box, type DTS Step By Step Chapter 1 Package 1 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:

    C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.1.dts

    Click Next to review the selections you made in the wizard.

  14. Click Finish to save and execute the package. The progress of the package execution is displayed.

  15. Click OK to acknowledge that the package copied one table successfully from Microsoft SQL Server to Microsoft SQL Server.

    Notice that the wizard saved the package and then executed three tasks (also called steps). The first task, Drop Table [SBS_OLAP].[dbo].[Sales By Category SBS] Step (the Drop Table step), failed. This task failure is expected because this destination table does not yet exist. The next time the task is executed, the existing table will be dropped. Notice that the failure of the drop table task did not cause the entire package to fail. (You will learn more about workflow execution rules between tasks in Chapter 2.) The next task, Create Table [SBS_OLAP].[dbo].[Sales By Category SBS] Step (the Create Table step), created the Sales By Category SBS table. The final task, Copy Data From Sales By Category to [SBS_OLAP].[dbo].[Sales By Category SBS] Step (the Copy Data step), populated the Sales By Category SBS table in the SBS_OLAP database.

    Notice also that 77 rows were copied to the Sales By Category SBS table. Finally, if you scroll to the right, you can observe the start, finish, and execution times of each step in the package.

    click to expand
    Tip  

    If a problem is encountered during the execution of any step in the package, you will receive an error. To obtain more details about the error, double-click the task with the error. If the error causes the package to fail, you can click Back and correct the error. However, you will need to rename the package before clicking Finish to save and execute the package again. The DTS Import/Export Wizard created the package containing the error on the first execution attempt, and the wizard will not overwrite an existing package.

  16. Click Done.

You have successfully created your first DTS package. In Chapter 2, you will learn how packages function by using DTS Designer to look at the details of each task in this package. You ll also learn how to create more complex packages with additional functionality. In the remainder of this chapter, you will learn how to perform additional tasks using the DTS Import/Export Wizard and how to work with different data sources and destinations.

Copy data between SQL Server databases using a SQL query

In the next procedure, you will learn how to use a SQL query to consolidate and filter data as it is copied between SQL Server databases. DTS is frequently used to assist in preparing a variety of reports by consolidating and filtering data from disparate sources.

  1. To launch the DTS Import/Export Wizard, click the Start button, point to All Programs, point to Microsoft SQL Server, and then click Import And Export Data.

  2. Click Next to select a data source from which you will copy data.

    In this procedure, you will again select the SBS_OLTP database on your local server as your data source.

    Note  

    DTS can extract data from or copy data to any OLE DB or ODBC data source to which it can establish a connection and to which it has sufficient permissions.

  3. Ensure that Use Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLTP, and then click Next to select a data destination.

    In this procedure, you will select the SBS_OLAP database on your local server, the database that you created in the previous procedure, as the destination for the data you are importing.

  4. Ensure that Use Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLAP, and then click Next to specify what DTS will copy from the data source to the data destination.

    In this procedure, you will use a query to consolidate and filter the data as it is being copied from the SBS_OLTP database to the SBS_OLAP database.

  5. Click Use A Query To Specify The Data To Transfer, and then click Next to specify the query that DTS will use.

    You can type a Transact-SQL query directly into the Query Statement text box, or you can click Browse to locate and load a query from a script file stored in the file system. If you need help building basic Transact-SQL queries, a graphical query builder is also available. In this procedure, you will use the Query Builder to build a simple query.

    click to expand
  6. Click Query Builder. This will bring up an interface in which you can select tables and columns from the data source.

    In this procedure, you will select a subset of the columns in the Customers and Orders tables to retrieve the list of customers with orders that have not been shipped.

    click to expand
  7. In the Source Tables list, expand Customers, double-click CompanyName, double-click ContactName, and then double-click Phone. Expand Orders, double-click OrderID, double-click RequiredDate, and then click Next to choose a sort order. You will sort the rows based on the required date of the order.

    click to expand
  8. In the Selected Columns list, double-click the RequiredDate column in the Orders table and then click Next to specify the query criteria.

    By default, all rows will be returned. In this procedure, you will limit the rows returned to those orders that have not shipped and whose required date is greater than or equal to the current date.

    click to expand
  9. Click Only Rows Meeting Criteria. In the Column list, select [Orders].[RequiredDate]; in the Oper. list, select > =; and in the Value/ Column list, type GETDATE() . In the first column in the second row, select And; from the drop-down list in the Columns list, select [Orders].[ShippedDate]; in the Oper. List, select =; in the Value/Column list, type NULL; and then click Next to review the query.

    Review the generated query and its syntax. GETDATE() is an internal Transact-SQL function that returns the current Datetime information at the time that the package s query is executed. Although the Query Builder did a pretty good job of creating this query that joins the Customers and Orders tables, to achieve the desired results, you need to change this query to use the IS NULL clause rather than = NULL .

    click to expand
    Important  

    The Query Builder is very good at generating the Transact- SQL script for basic queries and joins, but you will sometimes need to modify the generated script to achieve the result set you desire . As you can see in this example, the Query Builder does not know to change =NULL to IS NULL when performing NULL comparisons.

  10. In the Transact-SQL statement, replace the = before the NULL clause with IS , and add a space between IS and NULL.

    Tip  

    To verify that the Transact-SQL syntax for your completed script is valid, click Parse.

  11. Click Next to modify the schema or the data as it is copied to the data destination.

    Notice that Query is displayed as the source and Results is displayed as the destination. If you click Next, the results of the query you just created will be copied to a new table called Results in the SBS_OLAP database. However, you will change this default name to UnshippedOrders , which is a more descriptive name for the new table.

    click to expand
  12. In the Destination column, highlight Results, and then type UnshippedOrders . Do not include a space in the table name.

  13. Click the ellipsis in the Transform column to view the details of the new table that will be created.

    In the Mappings box on the Column Mappings tab, notice the mapping between the columns returned by the source query and the columns that will be created in the destination table. Notice also the data types, the nullable property, and the column size for the columns in the destination table. These properties of the destination table are generated automatically by DTS based on the properties of the columns in the result set. You can change these properties in the Mappings box, or you can click the Edit SQL button and modify the Transact-SQL script directly.

    click to expand
  14. Click Edit SQL to view the Transact-SQL script that DTS will use to create the destination table.

    You can change this Transact-SQL statement to change the column names or properties. However, the DTS Import/Export Wizard does not support major changes to the script. In Chapter 2, you will learn how to use the Execute SQL task to write more complex Transact- SQL statements within a package. In this procedure, you will not change the properties of the destination table.

    Tip  

    If you make a mistake when viewing or modifying the Transact-SQL statement, click Auto Generate to regenerate to the original Transact-SQL statement.

  15. Click Cancel to close the Create Table SQL Statement dialog box.

    click to expand
  16. Select the Drop And Recreate Destination Table check box.

  17. Click the Transformations tab, and then click Transform Information As It Is Copied To The Destination to view the transformation options.

    The Visual Basic transformation script copies information from each source column to each destination column without changing any data. You can perform additional transformations by changing this script. In this procedure, you will not perform any script-based transformations.

    You will learn to perform transformations using the default scripting language ”Visual Basic, Scripting Edition (VBScript) ”throughout the course of this book. Microsoft JScript is also available with an installation of SQL Server, and additional programming languages can be installed.

    click to expand
  18. Click OK and then click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.

  19. Click Save DTS Package, click Structured Storage File, and then click Next to provide the details for the save options.

  20. Type SQL_DTS_SBS_1.2 in the Name box, type DTS Step By Step Chapter 1 Package 2 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:

    C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.2.dts

    Click Next to review the selections that you made in the wizard.

  21. Click Finish to save and execute the package.

  22. Click OK to acknowledge that the package copied one table successfully.

    The wizard saved the package and then executed three tasks. The first task, the Drop Table UnshippedOrders Step task, failed. This task failure was expected because this destination table does not yet exist. The next task created the UnshippedOrders table. The final task populated the UnshippedOrders table. The next time this package is executed, the existing table will be dropped before the new table is created and populated. No rows are copied to the UnshippedOrders table because there are no orders with a required date equal to or greater than now in the SBS_OLTP database.

  23. Click Done.

You have successfully created a package that extracts data from two tables at the data source and creates a new table at the data destination by using a Transact-SQL query containing a multi-table join and a WHERE clause. In Chapter  2, you will use DTS Designer to add additional functionality to this package.

Copy data from a Microsoft Access database to a SQL Server database

Businesses frequently store data in a variety of structured formats, such as in Microsoft Access databases or in Microsoft Excel worksheets, and need to consolidate this data for reporting purposes. In the following procedure, you will learn how to copy data from an Access database into the SBS_OLAP database. You will launch the DTS Import/Export Wizard from within the SQL Server Enterprise Manager management console rather than from the Start menu.

  1. To launch the DTS Import/Export Wizard from within SQL Server Enterprise Manager, click the Start button, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In the SQL Server Enterprise Manager console tree, expand Microsoft SQL Servers, expand SQL Server Group, and then click (local) (Windows NT) to establish a connection to the default instance of SQL Server on your local server.

    Important  

    Most of the SQL Server wizards operate in the context of a connection to a SQL Server instance. If your focus within SQL Server Enterprise Manager is not within a connection to a SQL Server instance (for example, if your focus is at the top of the console tree), most of the SQL Server Enterprise Manager wizards will be grayed out.

  3. On the Tools menu, point to Data Transformation Services, and then click Import Data to launch the DTS Import/Export Wizard.

  4. Click Next to select a data source from which you will copy data.

    In this procedure, you will change the default data source and select an Access database as your data source.

    click to expand
  5. In the Data Source list, select Microsoft Access.

    Notice the extensive list of data sources from which DTS can import data. After you select Microsoft Access as your data source, the page changes to reflect the connection information required to connect to an Access database. You do not need to have Access installed to connect to an Access database, but you must be able to connect to the Access .mdb file using its file name. Based on the security set for the Access file, you might have to provide a user name and password. In this procedure, you will connect to the AccessPubsDB database.

    Important  

    When you save a DTS package that includes user names and passwords, you will have to edit the connection object or objects in the package to reflect any changes to this information that occur in the future. This task can become quite time consuming in large data movement applications because the number of connection objects increases in more complex packages. Chapter 3 discusses the security implications of embedding authentication information in packages.

    click to expand
  6. In the File Name box, type C:\Microsoft Press\SQL DTS SBS\Ch1\ChapterFiles\AccessPubsDB.mdb and then click Next to select a data destination. You will leave the Username and Password boxes empty because this database file does not have any security restrictions.

  7. Verify that Microsoft OLE DB Provider For SQL Server appears as the data destination, ensure that Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLAP and then click Next to specify what DTS will copy from the data source to the data destination.

    Notice that the Copy Objects And Data Between SQL Server Databases option is grayed out because the data source is not a SQL Server database. In this procedure, you will copy entire tables from the AccessPubsDB database into the SBS_OLAP database rather than use a query to filter or consolidate the data being imported.

  8. Click Next. This will bring you to the page in which you can select which tables in the Access database to copy.

    In this procedure, you will select all the tables in AccessPubsDB to copy, with their data, to the SBS_OLAP database. When the package is executed, DTS will create destination tables with the same names as the source tables and populate them with all the data from the source tables.

    click to expand
  9. Click Select All and then click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.

  10. Click Save DTS Package, click Structured Storage File, and then click Next to provide the details for the save options.

  11. Type SQL_DTS_SBS_1.3 in the Name box, type DTS Step By Step Chapter 1 Package 3 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:

    C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.3.dts

    Click Next to review the selections that you made in the wizard.

  12. Click Finish to save and execute the package.

  13. Click OK to acknowledge that the package copied 10 tables successfully from Microsoft Access to Microsoft SQL Server.

    The wizard saved the package and then created and populated a table for each table in the source database, using separate steps for each of these tasks.

  14. Click Done.

You have successfully imported the tables and data from an Access database to a SQL Server database with very little effort.

Note  

For more information on using DTS to convert or transform data between heterogeneous data sources and destinations, see Data Conversion and Transformation Considerations in SQL Server Books Online. There are Important variations in the way that different programs, providers, and drivers support data types and SQL statements, and you must be aware of them when working with heterogeneous data sources.

Copy data from a text file to a SQL Server database

In the next procedure, you will learn how to import data from a structured text file to a SQL Server database. Because the large majority of structured data sources have tools to export data to or import data from structured text files, you will frequently use DTS to either import data from text files or export data to text files. Moving data from a mainframe application into a text file and then from the text file into SQL Server is often the easiest way to interface with mainframe applications. Importing data from a text file is easy with DTS because DTS assists you with determining the file properties for the text file and creating format files (if format files are required).

  1. To launch the DTS Import/Export Wizard from within SQL Server Enterprise Manager, ensure that your focus is within your local SQL Server instance. On the Tools menu, point to Data Transformation Services, and then click Import Data.

  2. Click Next to select a data source from which you will copy data.

    In this procedure, you will use a semicolon-delimited text file as your data source.

  3. In the Data Source list, select Text File.

    The page changes to reflect the only connection information required to connect to a text file: the name and path of the text file.

    click to expand
  4. In the File Name box, type C:\Microsoft Press\SQL DTS SBS\Ch1\ChapterFiles\PotentialCustomers.txt and then click Next to select the file format.

    When DTS imports data from a text file, DTS attempts to determine the properties of the text file. For more information on file formats, see Select File Format in SQL Server Books Online. Notice that the wizard provides you with a preview of the text file to help you specify the appropriate format properties for the source file. You can also choose to skip a specified number of rows at the beginning of a text file, which enables you to import only part of a large text file during development, or to avoid header information that might precede the formatted rows.

    The Customer.txt file contains semicolon-delimited columns, contains ANSI data, uses a vertical bar as a row delimiter , does not use a text qualifier, and has column names in the first row.

    click to expand
  5. In the Row Delimiter box, select Vertical Bar. In the Text Qualifier box, select <none>. Select the First Row Has Column Names check box, and then click Next to specify the column delimiter. The Customer.txt file uses a semicolon as a column delimiter.

  6. Click Semicolon. Notice that the preview pane properly displays a preview of the source file after you specify a semicolon as the delimiter type.

    click to expand
  7. Click Next to select a data destination. In this procedure, you will import the data from the text file into the SBS_OLAP database.

  8. Verify that Microsoft OLE DB Provider For SQL Server appears as the data destination, ensure that Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLAP, and then click Next to modify the schema or the data as it is copied to the data destination.

    The text file appears as the source, and a new table called PotentialCustomers appears as the destination table in the SBS_OLAP database. You can transform the source data as it is being imported using ActiveX scripts or edit the Transact-SQL that will be used to create this new table by clicking the ellipsis in the Transform column. In this procedure, you will modify the Transact-SQL script.

    click to expand
  9. Click the ellipsis in the Transform column to view the column mappings and transformations that DTS will perform.

    In the Mappings box on the Column Mappings tab, notice the mapping of columns between the columns in the text file and the columns in the destination table that will be created. The name for each column in the new table is determined by the names specified in the first row of information in the text file. DTS uses varchar (8000) as the default data type for all data when importing data from a text file.

    In this procedure, you will add a column constraint, change column data types, and change column data lengths so that the formatting is more appropriate for the data being imported from the text file.

    click to expand
  10. In the Mappings box, modify the data types, null constraints, and data sizes using the information in the following table:

    Column name

    Data type

    Nullable

    Size

    Customer Name

    Varchar

    Not nullable

    50

    Street Address

    Varchar

    Nullable

    50

    City

    Varchar

    Nullable

    30

    State

    Char

    Nullable

    2

    Zip Code

    Char

    Nullable

    10

    Telephone Number

    Char

    Nullable

    14

  11. Click OK and then click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.

  12. Click Save DTS Package, click Structured Storage File, and then click Next to provide the details for the save options.

  13. Type SQL_DTS_SBS_1.4 in the Name box, type DTS Step By Step Chapter 1 Package 4 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:

    C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.4.dts

  14. Click Next to review the selections that you made in the wizard.

  15. Click Finish to save and execute the package.

  16. Click OK to acknowledge that the package copied one table successfully from Flat File to Microsoft SQL Server.

    The wizard saved the package, created the PotentialCustomers table, and then copied the data from the text file into the PotentialCustomers table.

  17. Click Done.

You have successfully imported data from a text file into a table in a SQL Server database. This simple example shows how easy it is to use DTS to import data from a structured text file.




Microsoft SQL Server 2000 DTS Step by Step
Microsoft SQL Server 2000 DTS Step by Step (Step by Step (Microsoft))
ISBN: 0735619166
EAN: 2147483647
Year: 2003
Pages: 87
Authors: Carl Rabeler

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net