The features supported by SSIS and SQL Server differs depending on what edition of SQL Server 2005 you have. Higher the edition, more is the features available but with a price attached.
Let’s see some of the features of each of the SQL Server 2005 Editions in terms of SSIS starting from the Basic to the more Advance edition.
SQL Server 2005 Developer Edition: Developer Edition allows developers to build and test any type of application with SQL Server on any windows platform. Developer Edition includes all of the functionality of Enterprise Edition, but is licensed only for development, test, and demo use only. Each license of SQL Server 2005 Developer Edition entitles one developer to use the software on as many systems as necessary and additional developers can use the software by purchasing additional licenses. Developer Edition can easily be upgraded to SQL Server 2005 Enterprise Edition for rapid deployment to production.
SQL Server 2005 Compact Edition: SQL Server Compact is a free and easy-to-use embedded database engine that lets developers build Windows Desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone. SQL Server Compact’s embedded Databse engine is under 2MB in size, making it easy to create and deploy database applications over the Internet without compromising great performance or rich features. This Edition includes a subset of SQL Server 2005 data types and shares common elements of the Transact-SQL (T-SQL) language with the data service engines. SQL Server Compact 3.5 introduces a host of new features including LINQ to SQL support, Microsoft Synchronization Services for ADO.NET, plus enhanced support for T SQL statements that lets developers rapidly develop and deploy applications using Visual Studio 2008.
SQL Server 2005 Express Edition: This is the equivalent of Desktop Edition (MSDE) for SQL 2000 but with more features. This edition is still available as a free edition for smaller applications with a databse size limit of 4GB. The concurrent query limitation has been removed from this edition allowing more users to query the server instance at the same time. It also supports objects like Stored Procedures, Triggers, and Views. This edition also comes with a management tool which was not packed with MSDE. It does have a 1 GB memory limitation and can use only 1 CPU. This is a good choice when you want a seamless upgrade to SQL Workgroup, Standard or Enterprise Editions.
SQL Server 2005 Workgroup Edition: This edition is suitable for small and medium-sized business thats needs a database server with limited Reporting Services and Business Intelligence. Workgroup adds additional functionality such as merge replication publishing, unlimited database size, and can utilize dual CPU machines with memory up to 3GB. The memory restriction of 2GB in SQL 200 Workgroup has been increased to 3GB. Workgroup Edition fits in various roles like, serving as a front-end Web server or for departmental or branch office operations. It includes the core database features of the SQL Server product line and is easy to upgrade to Standard or Enterprise Edition.
SQL Server 2005 Standard Edition: Standard Edition is an affordable and more valuable option for small- and medium-sized business. It includes the core functionality needed for noncritical e-commerce, data warehousing, and line-of-business solutions. You can now create high availability systems by using Standard Edition features like clustering, database mirroring and support for win32, x64, and Itanium-based servers. These features were only available in SQL 2000 Enterprise edition before. It does not have a memory limitation and can use 4 CPU’s. It also supports unlimited database size.
SQL Server 2005 Enterprise Edition: This Edition is a best fit for companies that need highest levels of scalability and availability with more advance features in SQL Server and Business Intelligence. It also meets the high demands of enterprise online transaction processing and data warehousing applications. There is no memory limitation, no database size restrictions and no CPU support restrictions. This editions can leverage the maximum memory and CPU’s supported by the version of OS running on that SQL Server.
Following are the transformations and tasks only available in SQL 2005 Enterprise Edition:
- Analysis Services Partition Processing Destination
- Analysis Services Dimension Processing Destination
- Data Mining Training Destination
- Data Mining Query Component
- Data Mining Query Task
- Fuzzy Grouping
- Fuzzy Lookup
- Term Extraction
- Term Lookup
Here are the approx. pricing of various Editions:
| SQL Server Editions | Processor Pricing | Server Plus CAL Pricing |
|---|---|---|
| Workgroup Edition | Retail* $3,899 | Retail* $739 with 5 CALs |
| Standard Edition | Retail* $5,999 | Retail* $1,849 with 5 CALs |
| Enterprise Edition | Retail* $24,999 | Retail* $13, 969 with 25 CALs |
*All prices reflect pricing for purchases within the United States and are in U.S. dollars. The prices listed are estimated retail prices; reseller pricing may vary.