Old ladyHow Big Is Your Package?
Size up and convert your DTS package to SSIS. If you have the biggest DTS package, win a gift card and certificate of a world record package size.  
What are the main differences in SQL Server 2005 SSIS and SQL Server 2008 SSIS? - Nayan Patel
in

Pragmatic Works

Nayan Patel

What are the main differences in SQL Server 2005 SSIS and SQL Server 2008 SSIS?

Here is the list of couple of major changes in SSIS 2008.

New Script Environment:

Script Task now supports VB.net and C# both languages. In the previous version of SSIS only VB.net support was there.


Enhanced SQL Statements:
Transact-SQL supports the use of a MERGE operation in an SQL statement. The MERGE operation enables you to express multiple INSERT, UPDATE, and DELETE operations in a single statement against a specified target table.

Change Data Capture (CDC):
Integration Services includes a new technology called change data capture. This new Database Engine feature captures insert, update, and delete activity that is applied to SQL Server tables. Change data capture also makes the details of these changes available in an easily consumed relational format.

DataFlow task Improvements:
New DataFlow engine has smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase upto 80% on multi-processor machines.

New Data Profiling Task and Data Profile Viewer:
The Data Profiling task is a new task in the Integration Services toolbox. You can use this task inside an Integration Services package to profile data that is stored in SQL Server. The information provided by the profile helps you identify potential problems with data quality. The Data Profiling task provides profiles that help identify data quality problems within individual columns and with column relationships:
Profiles that help identify problems within individual columns

  • The distribution of lengths in the column values.
  • The percentage of null values.
  • The distribution of values in the column.
  • Column statistics for numeric columns.
  • Regular expressions that match string columns.
Profiles that help identify problems with column relationships
  • Candidate key columns.
  • Functional dependencies between columns.
  • The inclusion of the set of values in one column in the set of values in another column.

Enhanced Performance and Caching for the Lookup Transformation:
Performance enhancements to the Lookup transformation include faster cache loading and more efficient lookup operations. These enhancements are possible because of the following features:

  • The ability to take rows that do not have matching entries in the reference dataset and load those rows into the cache.
  • The ability to use separate data flows to load the reference dataset into the cache and to perform lookups on the reference dataset.

New ADO.NET Components:
Integration Services now includes the following ADO.NET components:

  • An ADO.net source component that consumes data from a .net Framework provider and makes the data available to the data flow.
  • An ADO.net destination component that loads data into a variety of ADO.net-compliant databases that use a database table or view.

New Date and Time Data Types:
The following new date and time data types are available in Integration Services:

  • DT_DBTIME2
  • DT_DBTIMESTAMP22
  • DT_DBTIMESTAMPOFFSET
These new Integration Services data types provide the following benefits:
  • Support for a larger scale for fractional seconds.
  • Support of user-defined precision.
  • Support for a time zone offset.

New Debug Dump Files:
You can create debug dump files (.mdmp and .tmp) that provide information about what happens when a package runs. This information can help you in troubleshooting issues that occur when you run the package.

For more information visit the following URLs
Source: simple-talk.com - New feature in SSIS 2008
Source: technet.microsoft.com   - What's New (Integration Services 2008)
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part one
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part two

Published Nov 13 2008, 10:08 PM by Nayan Patel
Filed under: ,

Comments

No Comments

About Nayan Patel

Nayan Patel, MCSD, MCDBA, MCSE is a Product Manager at Pragmatic Works and also Architect/Lead Developer of DTS xChange and SSIS xPress products. He is a consultant, trainer, mentor and developer who is enthusiastic about developing robust BI Tools. He has expertise in many areas of Business Intelligence including Integration Services, Reporting Services, Database Administration, and .Net Software Development. Nayan has created BI and software solutions for Pragmatic Works. He has also worked on several projects related to Windows Applications, Web Applications and BI Applications using Microsoft technologies.
Copyright Pragmatic Works
Powered by Community Server (Non-Commercial Edition), by Telligent Systems