Sunday, 19 July 2015

Difference between 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