Wednesday, July 22, 2015

Difference between DTS & SSIS


DTS
SSIS
SQL Server 7.0 introduces available on 2000 onwards
SQL Server 2005 onwards its available
Designed for ETS (Extract Transform Sources)
Designed for ETL (Extract Transform Load)
It Consists of Single Window for all operations. It has data transformations like work flow etc
It Consists of multiple windows for multiple operations. It has ControlFlow,Data Flow, Event Handler, Package Explorer.
Data transformations available.
Dataflow task introduced and all transformations are embedded.
Message boxes displayed in Active-X Script.
Message boxes displayed in Script task.
Less Transformations
More Transformations
Partial BI Support
Full BI Support
No Deployment Wizard
Deployment wizards are introduced.
No DSV(Data source view) , No Connection manger, No Event handlers, No looping through folders & files
Introduced in SSIS
Saved in Enterprise manager(SQL Server) & File system (Structured storage file)
Saved in local file system, deployed in SQL Server.
 DTS Package looks like as follows








 
 
 

 

 
SSIS Package looks like as follows