Transcript DTS

SSIS Over DTS
Sagayaraj Putti
(139460)
What is DTS?
Data Transformation Services (DTS)
 DTS is a set of objects and utilities that allow the automation of extract, transform and load
operations to and from database.
 DTS also allows data to be transformed and loaded from heterogeneous sources using OLE
DB, ODBC or text-only files into any supported database.
 DTS is included with Microsoft SQL Server 2000 and is always used with SQL Server
databases although can be used independently with other databases.
 DTS provides an alternative method of version control and backup for packages when used in
conjunction with a version control system such as Microsoft Visual SourceSafe.
 DTS is a solution created as one or more packages. Each package contains organized set of
tasks that define work to be performed, transformations on data and objects, workflow
constraints that define task execution and connections to data sources and destinations.
11 April 2016
2
What is SSIS?
SQL Server 2005 Integration Services
 SSIS is one of the powerful features in SQL Server 2005 used as a robust way to load data and
perform tasks in a workflow.
 SSIS is a platform for building high performance data integration and workflow solutions.
 SSIS is an ETL (Extraction, Transformation, and Load) tool whose purpose is to do extraction,
transformation and loading of data but can be used for several other purposes like automation
of maintenance of SQL Server databases.
 SSIS is a component of SQL Server 2005 / 2008 and is a successor of DTS.
 DTS and SSIS may appear similar but SSIS has been rewritten completely from the scratch
and the internal architecture of SSIS is completely different from DTS.
11 April 2016
3
DTS & SSIS Comparison
DTS Vs. SSIS
 The internal architecture of SSIS is completely different from DTS. The data flow engine is
completely segregated from the control flow engine or SSIS Runtime Engine and hence the
performance is improved significantly in SSIS.
 SSIS is now no longer a stand-alone designer. SSIS uses the new development environment
called Business Intelligence (BI) Development Studio and the administration environment is
called SQL Server Management Studio.
 The most commonly used wizard in DTS was Import/Export Wizard which allowed DBAs and
developers to move data from source to target of any type very quickly with no code and little
hassle. The Import/Export Wizard in SQL Server 2005 had the following improvements
compared to DTS.
 Creation of new database directly from Wizard
 The import/export wizard of SSIS has the capability to create a new target database
directly whereas the user would have to stop the process, return to the enterprise
manager and create a new database in DTS.
11 April 2016
4
DTS & SSIS Comparison
DTS Vs. SSIS
 Real-time preview of data
 Improved flat file control for both source and destination
 Optimization of processing when large number of tables and views is desired.
 The way the designers are designed are different for DTS and SSIS.
 In DTS, the designer consists of a single pane with two options on the right, Connections
and work flow tasks.
 In SSIS, the designer is split into 4 design panes:
 Control Flow
 Event Flow
 Event Handlers
 Package Explorer
 In DTS, only independent packages could be created and there was no possibility to store the
related packages of the module in a single pace. The Solution Explorer in SSIS allows multiple
packages related to one module in a single solution.
11 April 2016
5
DTS & SSIS Comparison
DTS Vs. SSIS
 In DTS, each package may contain up to 5-10 connections to a particular datasource and it
becomes cumbersome to modify all the connections when the package is shifted from one
environment to other which is resolved using dynamic properties and disconnected edits.
Connection Managers in SSIS makes this process easy. In SSIS, a centralized place is
available to configure the connection to a particular data source or data destination called
connection managers. Modifications are required at only one place for SSIS packages when the
environment is shifted.
 There are two types of variables in SSIS architecture, System Variables and User Variables.
SSIS variables have a default scope of package and can be reduced to container, task or event
handler in the package. The addition of scope to the SSIS variables differentiate them from
DTS global variables.
 All SSIS container types can be configured to use transactions and Integration Services provide
three options for configuring transactions.
 Required
 Supported
 Not Supported
11 April 2016
6
DTS & SSIS Comparison
DTS Vs. SSIS
 SQL Server 2008 Integration Services are entirely different from DTS. In DTS, a typical ETL
scenario loads one or more staging tables and then applies series of updates prior to the final
load of the data warehouse. These constant updates to staging tables were very taxing on
database servers. In SSIS, due to the change in architecture, most of the functions can be
performed in the memory quickly without even writing the data to a staging table.
 In DTS, the average run time over five runs of the package was 33.2 seconds and the
same package, after upgraded to Integration Services took 11.3 seconds on average.
 Meta Data Services of DTS is not supported by SSIS.
11 April 2016
7
SSIS over DTS
Why Upgrade from DTS to SSIS?
 Running DTS in SQL Server 2005 only provides a “wrapper” to the DTS package and the
package will not take advantage of the performance gains that SSIS offer.
 The load time for larger volumes of data to move, clean and transform will increase drastically in
DTS. Using SSIS will cut this time by a fraction of the original load time thus allowing for more
processes to take place that may have originally put on alternate schedules due to resource
constraints.
 The techniques for developing packages in SSIS are more standard and straight forward
compared to the alternate techniques required to perform those same actions in DTS.
 ETL Loads in DTS have higher maintenance costs due to the lack of functionality now present
in SSIS. The lack of functionality in DTS allows developers to use more complicated
techniques and are more error prone.
11 April 2016
8
DTS – to – SSIS Migration
DTS – to – SSIS Migration
 Run the SQL Server 2005 Upgrade Advisor which has an option for DTS specifically.
 During migration, DTS runtime remains in place, DTS packages are unaffected and local server
packages remain stored in msdb.dbo.sysdtspackages table. Structured storage files, or
packages saved to DTS files, are also unchanged.
 Meta Data Services could create problems during migration as SSIS does not support Meta
Data Services.
 The packages will continue to run after the upgrade and scheduled jobs will use DTSRUN and
call the same packages from the same locations as they did prior to the upgrade.
 The DTS runtime will continue to be available and will be updated in the future to enable DTS
packages to connect to SQL Server 2005 data sources. The DTS designer components are
removed along with SQL Server 2000 Enterprise Manager.
11 April 2016
9
DTS – to – SSIS Migration
DTS – to – SSIS Migration
 SQL Server Management Studio which is the SQL Server 2005 replacement for Enterprise
Manager, supports DTS packages.
 In the Object Explorer window, under the Management Legacy nodes, Data Transformation
Services can be seen which is the equivalent of local packages. Packages can be imported and
migrated from there but to manage/edit packages, the Microsoft SQL Server 2005 DTS
Designer components need to be installed.
11 April 2016
10
11 April 2016
11