Rise of Data Transformation Services - DTS Packages
Interoperability and communication between applications plays a vital role in today’s emerging business needs.One such component which helps the business to transfer data between different system is the Data Transformation Service Packages famously known as DTS packages.This pasage will cover the differences between SQL Server 2000 and SQL Server 2005 with respect to the DTS Packages.Enterprise Manager of the Microsoft SQL Server 2000 provides an inbuilt menu within its IDE and the dts packages are accessed using the SQL Server 2000 DTS Designer IDE.
The DTS Designer 2000 allows us to perform the following tasks over a package such as 1. Designing 2. Executing and 3.Scheduling.
In SQL Server 2005, the DTS packages cannot be accessed the same way as we use to access it in SQL Server 2000. The reason being the advent of Microsoft SQL Server Integeration Services (SSIS) in the 2005 version. The two main IDE of the SQL Server 2005 are
- SQL Server Management Studio, and
- SQL Server Business Intelligence Development Studio.
Unlike the DTS Designer 2000 that was used to access the DTS Packages, the SQL Server Business Intelligence Development Studio supports the packages with its SSIS. Microsoft also provides a backward compatibility method of accessing the DTS Packages that were created in SQL Server 2000. Even after the migration of the database from SQL Server 2000 to SQL Server 2005, the DTS Packages which were originally created in the SQL Server 2000 environment can also be migrated to the 2005 Server. Since the DTS Packages are Server specific, by 1. opening the SQL Server 2000 Enterprise Manager and by 2. accessing the SQL Server Management Studio, the DTS Packages can be opened by DTS Designer 2000 through the SQL Server 2005 environment.