Monday, 21 April 2014

Difference between DTS and SQL Server Integration Services (SSIS)

1. DTS stands for 'Data Transformation Services', while SSIS stands for 'Sql Server Integration Services'.

2. DTS is a set of objects using an ETS tool(tool to extracts, transforms, and loads information into a database for warehousing)in order to extract, transform, and load this information to and/or from a database, while SSIS is an ETL tool provided by Microsoft to extra data from different data sources.

3. DTS was originally part of the Microsoft SQL Server 2000, while SSIS is a component of the Microsoft SQL Server 2005.

4. In order solve complex calculations, DTS uses Activex Script which is similar to vb script, while SSIS uses programming languages like as c# or VB.NET.

5. There is no deployment wizard available in DTS, while SSIS contains Deployment wizard.

6. Very limited Set of Transformation available under DTS, while in SSIS almost all possible transformations are available.


7. In DTS there is no parallel processing available(Only one task at a time), while in SSIS multiple tasks can execute parallel. 


8. DTS doesn' support BI in all aspects, while SSIS supports end to end process of BI.


9. In DTS script is Unmanaged(Not running under CLR), while  in SSIS script is managed and runs under CLR.


10. DTS can develop using Enterprise manager, while SSIS can develop using BIDS(Business Intelligence Development Studio).



11. DTS can be deploy in local server only, while SSIS   package can be deploy in all available servers(local/remote) using BIDS.

12. In DTS designer there is single pane available, while SSIS designer contains four pane - 
  • Control Flow
  • Data Flow
  • Event Handlers
  • Package Explorer
13. There is no event handler available in DTS, while in SSIS event handlers are available like as 'OnError', 'OnProgress'.

14. There is no solution explorer available in DTS, while in SSIS solution  explorer is available including packages, connectionsData Source and Data Source Views (DSV).

15. There is no variables in DTS so connections and other values are always static, while in SSIS these values can be dynamic as can be achieved using variables.

No comments:

Post a Comment