Module 9: Transferring Data

1. Module 9: Transferring Data

2. Overview • Introduction to Transferring Data • Tools for Importing and Exporting Data in SQL Server • Introduction to DTS • Transforming Data with DTS

3. Introduction to Transferring Data • Why to Import and Export Data • Why to Transform Data

4. Why to Import and Export Data • Importing and Exporting Data Involves Moving, Copying, Archiving, or Migrating Data • Before You Import and Export Data, You Must Determine: • Data source • Data destination • Data manipulation • Importing and Exporting Can Be Simple or Complex

5. Why to Transform Data • Change the Format of Data • Transform and Map Data • Make Data Consistent • Validate Data • Schedule the Operation • Import and Export Data Between Heterogeneous Environments

6. Tools for Importing and Exporting Data in SQL Server • DTS Import/Export Wizard • DTS Designer • DTS Object Transfer • DTS Bulk Insert • Bulk Copy Program (bcp Utility) • Replication

7. Introduction to DTS • DTS Overview • DTS Process • DTS Tools

8. DTS Overview • With DTS, You Can: • Import and export data between any data source and destination • Transform the data • Transfer database objects between databases in SQL Server • Use DTS to: • Create custom transformation objects • Build data warehouses and data marts • Access applications that use OLE DB providers

9. In Out Transformations Data Sources Data Destinations OLE DB Provider OLE DB Provider DTS DTS Process

10. DTS Tools • DTS Import/Export Wizard • Create simple packages for importing, exporting, and transforming data • Can be launched from a command prompt • DTS Designer • Creates and edits DTS packages graphically • Defines complex transformations • Specifies complex sequence of operations • dtsrun Utility • Retrieves, runs, deletes, or overwrites packages

11. Transforming Data with DTS • Transforming and Mapping Data • Defining Transformation Tasks • Defining Workflows • Creating a DTS Package • Executing and Scheduling a DTS Package

12. Transforming and Mapping Data • Mapping Data Types • You can specify how data is formatted between source and destination • You can specify how data is modified between source and destination • Integrating and Consolidating Data • You can combine data • You can summarize data vertically or horizontally

13. Defining Transformation Tasks • Each Task Defines a Unit of Work • A Task Can: • Execute a Transact-SQL statement • Execute a script • Launch an external application • Copy SQL Server objects • Execute or retrieve results from a DTS package

14. Step A Step D Step E Step B Step F Step C Defining Workflows • Precedence Constraints • Sequence • Parallel • Combination • Step Priority • Idle • Normal • High • Transaction Capabilities

15. Creating a DTS Package • Saving a DTS Package • COM-structured storage file • SQL Server Meta Data Services • msdb database • Visual Basic file • Implementing Package Security • Owner password • User password

16. Executing and Scheduling a DTS Package • Executing a DTS Package • Using SQL Server Enterprise Manager • Using dtsrun command prompt utility • Scheduling a DTS Package • Using DTS Import/Export Wizard when you save to the msdb database • Using SQL Server Enterprise Manager when you use the dtsrun command prompt utility dtsrun /SAccounts /UJose /NOrdersImport

17. Demonstration: Defining a DTS Package

18. Use DTS Wizards for Simple Import and Export Operations Use DTS Transfer Object Task to Transfer Objects Between SQL Server Databases Use DTS Designer to Design Workflows and Complex Transformations Recommended Practices

19. Lab A: Transferring Data

20. Review • Introduction to Transferring Data • Tools for Importing and Exporting Data in SQL Server • Introduction to DTS • Transforming Data with DTS