Introduction

Overview

One of the challenges that business executives face today is the need to integrate data from disparate sources, in different locations, and in disparate formats into a single data store to assist them in making business decisions. The developer of the data movement application charged with the task of integrating this dispar ate data needs a single tool that he or she can use to extract the data from each disparate data source, transform the data in many different ways, and then load the data into the integrated data structure. This tool must be flexible and exten sible enough to meet the needs of experienced developers yet be simple enough for beginning developers to use. In addition, the tool must be capable of devel oping data movement applications that are easy for database administrators to use and manage.

Microsoft SQL Server 2000 Data Transformation Services (DTS) is this tool. Developers can use DTS to create simple or complex data movement applica tions. These applications can be created using the DTS Import/Export Wizard, DTS Designer, and Microsoft Visual Basic, depending on the complexity required for the data movement application and the skill set of the developer. The data movement application can consist of a single DTS package or can con sist of a number of interrelated DTS packages. Each DTS package contains con nection objects, tasks , and workflow constraints.

This book teaches you how to use DTS by providing you with step-by-step pro cedures that let you build an extensible data movement application that period ically moves data from delimited text files, a SQL Server database, and Microsoft Excel spreadsheets into a multidimensional cube in Microsoft Analy sis Services. While some knowledge of SQL Server, Analysis Services, and ActiveX is assumed in the design of the data movement application, this knowl edge is not required to understand the DTS techniques and best practices encap sulated in the data movement application that you will build.

The first part of this book is written for those readers who are new to DTS, and the remaining chapters are geared to those readers who have some previous exposure to DTS. The packages created in each chapter build upon themselves ; it is intended that you complete the procedures in this book in chapter order. However, the files for each chapter also include completed packages from previ ous chapters and database backup files to allow you to perform the procedures in this book in any order you want.