Change Data Capture (CDC)
While not a direct SSIS feature, the new Change Data Capture functionality in 2008 can be taken advantage of in SSIS to do incremental loads. CDC captures changes to SQL Server tables (inserts, updates, deletes), and makes them available in an "easily-consumed, relational format".
CDC was added to Katmai in one of the earlier CTPs, and I've heard that the most recent CTP added some new stored procedures to make using it in SSIS even easier. I've heard that the CDC team is working on posting some samples on Codeplex , and I'll post more details once I get a chance to try them out.
BOL - Improving Incremental Loads with Change Data Capture
Merge
Another engine feature you can take advantage of in SSIS is the merge statement , which allows you to perform insert, update or delete operations on a target table based on the results of a join with a source table.
John Welch had a couple of good posts on Merge , and how to use it from SSIS awhile back that are definitely work checking out.
I've also seen some samples in the works that I'll post about once they are up on Codeplex.
Data Profiling
The new Data Profiling Task let's you analyze SQL tables to determine (and maintain) data quality.
The way it works is you configure the task to do a set of analysis on columns/tables, such as:
- Candidate keys
- Column length distribution
- Null ratio
- Pattern detection
- Value distributions and stats
- Functional dependencies
- Value inclusion
The results are dumped out to an XML file, which you can then load using a special Viewer application.
Combined with a script task, you can use the profiling task to do a quality check on your data before passing it off to your regular data flow ETL.
BOL - Profiling Data with the Data Profiling Task and Viewer
Visual Studio 2008 - Orcas
The next Katmai CTP will introduce Visual Studio 2008 as our Business Intelligence Design Studio. This doesn't change much functionally, but does allow for side by side installations of BIDS - meaning you can edit Yukon packages (with Visual Studio 2005) and Katmai packages (with Visual Studio 2008) on the same machine.
Package Upgrade Wizard
The next CTP will also introduce the bulk of our upgrade work, including our Package Upgrade Wizard. While a 2005 package is automatically upgraded when opened in BIDS in 2008, this wizard lets you upgrade a set of packages all at once. It can read packages from a file share, SQL Server database, or Katmai SSIS service, upgrade them, and dump them to a location of your choosing.
The wizard can be launched from the command line (SSISUpgrade.exe), from BIDS, and from the menu in Management Studio. It will also launch automatically in BIDS when you open up a project which contains Yukon packages.
Improved Memory Dumps
A lot of work has gone into improving our supportability, and part of this was enhancing our memory dump capability for debugging crashes and hung packages.
When a dump is created (the directory is configurable, but defaults to %ProgramFiles%\Microsoft SQL Server\100\Shared\ErrorDumps), we also generate a text file which contains useful debugging information that SSIS developers and support can make use of. We've been using the dumps internally to debug tough pipeline deadlocks and crashes, and so far it's been really helpful.
These dumps will be created automatically during a crash, but can also be triggered by the user.
Dumping with DTExecTwo dumping options have been added to DTExec - /Dump, which takes a semi-colon separated list of error codes (HResults) to dump on, and /DumpOnError, which will trigger a memory dump anytime an error is encountered.
Dumping with DTUtilThe /Dump option added to DTUtil allows you to create a memory dump for a running package without disrupting its execution (at least not for very long). This is very useful if you suspect your package has hung.
[ EDIT: adding a link to the BOL content ]
----
If I've missed something, I'll follow up with a Part 3, but I think I've covered all of the main features. On top of this, there have been many bug fixes and minor improvements. I'll continue covering some of the more important ones later on.