none
Differences between ssis 2005 and ssis 2008 R2?

Answers

  • Hi,

    Here is a link to the features introduced in 2008

    http://blogs.msdn.com/b/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx

    In R2, there was a SSIS Connection Wizard introduced but nothing really exciting.

    Also, what it does not say here, is that 2008+ is a lot less buggy than 2005 was. Although still not perfect.

     


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, June 30, 2011 1:35 PM
  • SSIS 2005 vs SSIS 2008:

    1.Upgrading from SSIS 2005 to SSIS 2008: Should be as easy as opening each package up and re-saving it in 2008.

    o Scripting engine has changed. (You can now write SSIS scripts in C#.)

    o SSIS 2008 uses a newer build of the SQL Server Native Client

    2. SSIS 2008 uses a newer build of the SQL Server Native Client

    3. SSIS 2008 contains an updated OLEDB Provider for Analysis Services 2008 (10.0)

    4. The SSIS 2008 uses VSTA (Visual Studio 2005 Tools for Applications) instead of the VSA (Visual Studio for Applications) scripting engine. (This means you can now write SSIS scripts in C#.)

    5. SSIS 2005 custom developed components will need to be altered to work in SSIS 2008

    6. New Data Profiling Task & Viewer

    7. Enhanced Performance and Caching for the Lookup Transformation

    8. Enhanced Data Flow Engine Tuned for Parallelism

    9. Various smaller enhancements for supporting new data types and TSQL capabilities

    10. Importing Excel 2007 worksheets is native to SQL 2008. In SQL 2005, you have to build a new connection based on the office 12.0 OLEDB client.

    11. The script environment differences are the biggest change between the two builds of SSIS.

    12. When you open/add a SSIS 2005 package which contains a Script Task in SQL Server 2008 BIDS it will automatically attempt to convert the script for you.(Do this.)

    13. You can also attempt to execute the older SSIS 2005package with the 2008 dtexec utility, this operation will also convert the script; however the script conversion is only temporary.

    14. No performance limitations in SSIS Standard edition vs. Enterprise edition

    o Enterprise does have some advances transformations, like Data Mining and Fuzzy

    15. Look ups, that Standard does not have.

    o These advanced operations are likely to be used in

    16. OLAP environments.

    17. Both 2005 and 2008 Enterprise Edition’s killer feature is horizontal partitioning.

    o Strongly recommended for large datasets in OLAP cubes. Can provide huge performance gains.

    o Can be used on any large SQL database for performance

    18. Guideline is approx more than 20 million rows in a single partition max.

     

    References:

    William D Assaf Development Consultant Sparkhound, LLC

    http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx

    • http://blog.scalabilityexperts.com/2008/02/14/the-key-sql-server-2008-

    bi-migration-dts-to-ssis-2008-part-1-of-2/

    • http://blog.scalabilityexperts.com/2008/02/19/the-key-sql-server-2008-

    bi-migration-dts-to-ssis-2008-part-2-of-2/

    • http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/

    archive/2007/06/08/400.aspx

     


    latha
    Thursday, June 30, 2011 1:56 PM

All replies

  • To start off, a SSIS 2008 package cannot run using the SSIS 2005 engine, but SSIS 2008 BIDS or SSIS 2008 engine is capable of upgrading it.

    C# was added to SSIS 2008 script task

    There are differences in data providers, see this:http://blog-mstechnology.blogspot.com/2009/11/ssis-2008-vs-ssis-2005-data-providers.html

    and finally see what's new in SSIS 2008 R2/SSIS 2008: http://social.technet.microsoft.com/wiki/contents/articles/what-s-new-in-ssis-2008-2008-r2.aspx

     


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, June 30, 2011 1:32 PM
  • Hi,

    Here is a link to the features introduced in 2008

    http://blogs.msdn.com/b/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx

    In R2, there was a SSIS Connection Wizard introduced but nothing really exciting.

    Also, what it does not say here, is that 2008+ is a lot less buggy than 2005 was. Although still not perfect.

     


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, June 30, 2011 1:35 PM
  • SSIS 2005 vs SSIS 2008:

    1.Upgrading from SSIS 2005 to SSIS 2008: Should be as easy as opening each package up and re-saving it in 2008.

    o Scripting engine has changed. (You can now write SSIS scripts in C#.)

    o SSIS 2008 uses a newer build of the SQL Server Native Client

    2. SSIS 2008 uses a newer build of the SQL Server Native Client

    3. SSIS 2008 contains an updated OLEDB Provider for Analysis Services 2008 (10.0)

    4. The SSIS 2008 uses VSTA (Visual Studio 2005 Tools for Applications) instead of the VSA (Visual Studio for Applications) scripting engine. (This means you can now write SSIS scripts in C#.)

    5. SSIS 2005 custom developed components will need to be altered to work in SSIS 2008

    6. New Data Profiling Task & Viewer

    7. Enhanced Performance and Caching for the Lookup Transformation

    8. Enhanced Data Flow Engine Tuned for Parallelism

    9. Various smaller enhancements for supporting new data types and TSQL capabilities

    10. Importing Excel 2007 worksheets is native to SQL 2008. In SQL 2005, you have to build a new connection based on the office 12.0 OLEDB client.

    11. The script environment differences are the biggest change between the two builds of SSIS.

    12. When you open/add a SSIS 2005 package which contains a Script Task in SQL Server 2008 BIDS it will automatically attempt to convert the script for you.(Do this.)

    13. You can also attempt to execute the older SSIS 2005package with the 2008 dtexec utility, this operation will also convert the script; however the script conversion is only temporary.

    14. No performance limitations in SSIS Standard edition vs. Enterprise edition

    o Enterprise does have some advances transformations, like Data Mining and Fuzzy

    15. Look ups, that Standard does not have.

    o These advanced operations are likely to be used in

    16. OLAP environments.

    17. Both 2005 and 2008 Enterprise Edition’s killer feature is horizontal partitioning.

    o Strongly recommended for large datasets in OLAP cubes. Can provide huge performance gains.

    o Can be used on any large SQL database for performance

    18. Guideline is approx more than 20 million rows in a single partition max.

     

    References:

    William D Assaf Development Consultant Sparkhound, LLC

    http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx

    • http://blog.scalabilityexperts.com/2008/02/14/the-key-sql-server-2008-

    bi-migration-dts-to-ssis-2008-part-1-of-2/

    • http://blog.scalabilityexperts.com/2008/02/19/the-key-sql-server-2008-

    bi-migration-dts-to-ssis-2008-part-2-of-2/

    • http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/

    archive/2007/06/08/400.aspx

     


    latha
    Thursday, June 30, 2011 1:56 PM
  • 1.Upgrading from SSIS 2005 to SSIS 2008: Should be as easy as opening each package up and re-saving it in 2008.

    In theory, this should be true, but just as a heads up, I have experianced issues with ADO.NET version issues for ADO connection Managers and also, script tasks/components have become corrupted during the upgrade. If there are any issues, then you get a summary of warnings.
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, June 30, 2011 2:18 PM