1 / 25

Chapter Overview

Chapter Overview. Transferring and Transforming Data Introducing Microsoft Data Transformation Services (DTS) Transferring and Transforming Data with DTS Graphical Tools Working with DTS Packages Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement.

violet-yang
Télécharger la présentation

Chapter Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Overview • Transferring and Transforming Data • Introducing Microsoft Data Transformation Services (DTS) • Transferring and Transforming Data with DTS Graphical Tools • Working with DTS Packages • Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement

  2. Data Import Preparation Tasks • Verify internal consistency of data at data source. • Determine whether additional columns are needed. • Determine whether data format of source data requires modification. • Determine whether existing data columns need to be aggregated or separated. • Determine whether data import is a one-time task or a regularly occurring task. • Determine access rights to the source data.

  3. Data Transformations • Can data transformations be made in the data source? • Should temporary tables and Transact-SQL statements be used to scrub and cleanse the data after import into Microsoft SQL Server? • Can Data Transformation Services be used to make changes to the data during the import process itself? • Will transformations be applied to a regularly scheduled import or is the import a one-time task?

  4. Data Transfer Tools • DTS • Bcp • BULK INSERT statement

  5. DTS Process

  6. DTS Connections

  7. DTS Tasks • Discrete unit of work within a DTS package. • Can execute sequentially or in parallel. • Available through DTS Import/Export wizard or DTS Designer. • The DTS Parallel Data Pump task can only be accessed programmatically.

  8. DTS Tasks That Copy Data and Data Objects

  9. DTS Tasks That Transform Data • Transform Data task • Data Driven Query task

  10. DTS Tasks That Function as Jobs

  11. DTS Transformations

  12. DTS Package Workflow

  13. DTS Package Storage • Microsoft SQL Server 2000 • Meta Data Services • Microsoft Visual Basic file • Structured storage file

  14. DTS Tools • DTS Import/Export wizard • DTS Designer • Visual Basic/Microsoft Visual C++ • DTS Run utility and Dtsrun command

  15. DTS Import/Export Wizard • Define data to be imported, using a Transact-SQL query to limit data if necessary. • Select or create the destination tables if necessary. • Change default column mappings if necessary. • Choose objects to transfer if applicable. • Save and/or schedule the DTS package.

  16. DTS Designer

  17. Additional DTS Package Functionality • Transaction support • Message Queue task • Send Mail task • Programming templates

  18. Storage in the msdb Database • DTS packages can be saved in the msdb database on any SQL Server instance. • Each DTS package version is saved. • DTS packages saved to the msdb database can be secured using an owner password and a user password.

  19. Storage Using Meta Data Services • DTS packages can be saved to SQL Server Meta Data Services on any SQL Server instance. • Allows tracking of package version, meta data, and data lineage. • Saved meta data includes data transformations and data sources. • You must enable the tracking of data lineage and create a column in the data destination for the tracking information.

  20. Storage Using a Structured Storage File • DTS packages can be saved to an operating system file. • DTS packages saved to a structured storage file can be secured using an owner password and a user password. • Allows the DTS package to be moved, copied, or mailed across the network. • Open files in SQL Server Enterprise Manager, or use command-prompt utilities to execute them.

  21. DTS Package Execution Utilities • DTS Run utility (Dtsrunui.exe). • Dtsrun command. • Packages run in the security context of the logged-in user. • Scheduled packages run in the security context of the SQL Server Agent.

  22. Additional Options When Working with DTS Packages • Use DTS package logs to record information regarding the success or failure of a DTS package. • Use DTS exception logs to record error information about uncopied rows. • Performing disconnected edits is possible if one or more of the data sources or data destinations is unavailable.

  23. Bulk Copying of Data Using Text Files • Use either the Bcp command-prompt utility or the BULK INSERT Transact-SQL statement. • Used for importing large quantities of data at high speed with minimal transformation. • Format files can be used to specify the format of the data being imported. • DTS can be used with the BULK INSERT task to handle formatting issues.

  24. Optimization of Bulk Copy Operations • Use Bulk-Logged Recovery during importing of a large amount of data. • Use the TABLOCK hint and a large batch size when importing a large amount of data from a single client into an empty table. • It is generally faster to drop nonclustered indexes before importing large amounts of data (and then re-create the indexes). • If a table has a clustered index, it is generally faster to order the data in the text file, if possible, and then specify the ORDER hint.

  25. Chapter Summary • Analyze your data before importing it to determine the type of transformations, if any, that are required. • Use the DTS Import/Export wizard for simple transformations and copying database objects. • Use DTS Designer for complex transformations and workflow constraints. • Choose a DTS package storage format based on usage of DTS packages. • Use the BULK INSERT task rather than creating format files manually.

More Related