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
Bulk Insert task Execute SQL task
Copy SQL Server Objects task Transfer Database task
Transfer Error Messages task Transfer Logins task
Transfer Jobs task Transfer Master Stored Procedures task
9
DTS Tasks That Transform Data

• Transform Data task
• Data Driven Query task

10
DTS Tasks That Function as Jobs
ActiveX Script task Dynamic Properties task
Execute Package task Execute Process task
File Transfer Protocol task Send Mail task
11
DTS Transformations
Copy Column ActiveX Script Date Time String
Lowercase String Uppercase String Middle of String
Trim String Read File Write File
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.