What’s new in SQL Server 2008 for SSIS – Part one

Improved Scripting

SQL Server 2008 (Katmai) introduces VSTA as our new scripting engine, which replaces the old VSA engine used in SQL Server 2005 (Yukon). There are a number of advantages to the new engine, but I think the feature users are most excited about is that it allows you to use C# as a scripting language (as well as VB.NET). It also makes it easier to reference all .Net assemblies, which enables a number of common scenarios (such as easier web service integration).


Data Flow Task Improvements

Michael Entin provided a great write-up for the enhancements to our data flow engine. It essentially boils down to smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase on multi-processor machines.

One CPU usage graph I really like is the one shown at PASS this year:

Yukon CPU graph

This shows Yukon package execution on a quad CPU machine. You can see CPU #4 is used steadily, while two others spike occasionally. It also looks like usage goes down on #2 when it goes up on #3, and vice versa.

Running the same package using the Katmai engine looks like this:

Katmai CPU graph

This graph shows much better CPU usage distribution. In this case, the package received an 80% performance increase, but your mileage may vary.

Enhanced ADO.Net Support

Katmai now has an ADO.Net source (replacing the DataReader source) and destination adapters. They function a lot like the OLEDB ones, supporting a custom UI, drop down list of tables/views, and query builder. Its properties are also expression-able. This means we now support ODBC as a destination, as well.


Import/Export Wizard

The SQL Server Import/Export Wizard (which generates SSIS packages) has been improved as well.

  • ADO.Net support (which gives you ODBC)
  • Data type conversion - mappings are read from customizable (xml) files, and data convert transforms will be inserted into the data flow if required
  • Scales better - large numbers of tables will be split across multiple data flows (up to five sources per data flow)

Here is an example of the data conversion page:

Import/Export Wizard data conversion mapping 


Cached Lookup

As I mentioned in an earlier post, Jamie Thompson provided a very good write up of the new lookup component. The main feature is that you can now create a lookup cache in a separate data flow using the new Lookup Cache Transform and Cache Connection Manager, and persist it to disk. This cache can be created from any data source supported by SSIS, which removes the need to stage your lookup table in a database.

This persisted cache is especially useful in scenarios where reference data is updated periodically - say, once a week. You can run a package to generate your cache when the update comes in, and re-use it in the rest of your packages until it needs to be updated again.

Another scenario is managing large lookup tables, where a small number of keys are used a large percentage of the time. For example, 5% of your customer IDs show up in 80% of your rows. Instead of caching the entire lookup table, you can use a cached lookup for the top 5%, and cascade another lookup in partial / no-cache mode for the infrequent rows.


As you can see from the screen shot, Lookup also has a new "No Match" output - rows with no matches aren't directly sent to the error output anymore. We also maintain a "Miss Cache" internally, so missing keys aren't constantly re-queried.

More to follow...

Comments (12)

  1. My World says:

    Today was my first day presenting at RDN . It also marked my second and third user group presentations

  2. SQL Server 2008 will be coming out sometime this summer (in theory). At last week’s TechFuse event in Minneapolis, and in blogs I sometimes read, I’ve started to pick up on a number of useful features and improvements that should…

  3. ddouglas says:

    i don’t see where i can use expressions in the ado.net source.  am i missing something?

  4. They’ve posted part of the Advanced Lookup Scenarios presentation I recorded for the SSWUG conference

  5. Nayan Patel says:

    Here is the list of couple of major changes in SSIS 2008. New Script Environment: Script Task now supports

  6. vinczente says:

    Are the functions like "lookup" always just available for OLE DB databases ? What about ODBC databases !

  7. It depends on the transform. Many of them don’t require their own connection managers, but yes, the majority of those that do will support OLEDB.

    The addition of the cache connection manager in 2008 means that you can create lookup caches from any source that SSIS can connect to (including ODBC, via ADO.NET).


  8. vinczente says:

    Thanks for the quick reply.

    I have another question : Is it possible to run a 2008 SSIS package on a sqlserver 2005? Without loosing all theses functionalities.

  9. vinczente says:

    Ok, I found the answer to my question : I can’t.. 🙁

Skip to main content