Chapter Overview

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.