Using DTS Packages for transfering data

2006-04-26T23:22:00.000-07:00

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

Chapter 1 : Introduction to SQL Server 2000

2005-08-18T01:48:00.000-07:00

This is major database server package of Micro Soft flagship. The last version of SQL Server before SQL Server 2000, was SQL Server 7.0. So it can be said to be version 8.0 of SQL Server. Initially Sybase and Microsoft together created SQL Server but they had changed their way long before and now both have their own database packages.

SQL Server 2000 now has a new version named SQL Server 2005. Although, still it has to find its way in market. SQL Server 2000, on other hand, is leader in the market.

With release of Visual Studio .Net, SQL Server has became the most favored database server. This will certainly hurt Oracle, which was the hottest choice of developers before.

SQL Server can run in NT 4.0 (service pack 5 or later), Windows 2000 and Windows 98 or ME. But, to it is recommended to run it in Windows 2000 Server Edition.

SQL Server is based on client Server model. The server provides - security, high availability and fault tolerance while client is used for - use interface, execution of queries and ease of use. Client requests the server and server responds to client's request.

All databases are based on some database concepts like, relational model, OO model, network model etc. SQL Server (both Sybase and MS ) is based on Relational Model with Object Oriented approach. In relational model, data is stored in tables and database follows codd's rule.