SQL Server 2000

Using DTS Packages for transfering data

MS SQL Server 2000's DTS package is easy to use and very powerful tool for tansfering data between different database servers and softwares. An official definition states -
"Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations."

Actually, DTS is used in such database environments where data is spread over multiple formats. Like, if any company is using MS SQL Server to hold some data and MS Access t hold other, DTS can be handy to manage or consolidate data to single destination.

In DTS, we have to work with a set of operations, objects and tools. These can be summarized by following -

• DTS Packages - "A DTS package is an organized collection of connections, DTS tasks, DTS transformations, and workflow constraints assembled either with a DTS tool or programmatically and saved to Microsoft® SQL Server™, SQL Server 2000 Meta Data Services, a structured storage file, or a Microsoft Visual Basic® file." - As by Microsoft online help.


• 
  DTS Task - "A DTS task is a discrete set of functionality, executed as a single step in a package. Each task defines a work item to be performed as part of the data movement and data transformation process."  - As by Microsoft online help.
  
  
  
  1. Importing and exporting data.
  
  
  2. Transforming data.
  
  
  3. Copying database objects.
  
  
  4. Sending and receiving messages to and from other users and packages.
  
  
  5.   Executing a set of Transact-SQL statements or Microsoft ActiveX® script against a data source.
  
  
  
  


• DTS Transformations
  A DTS transformation is one or more functions or operations applied against a piece of data before the data arrives at the destination. The source data is not changed.


• Connectivity
  DTS is based on an OLE DB architecture that allows you to copy and transform data from a variety of data sources.

We can create DTS packages from many different sources. We will discuss DTS package in next blog Entry. You can check for some online magazines (RSS feed reader) for this by clicking on following -

Powered by Qumana

posted by Boolean Z @ 11:22 PM