SSIS 2008 improvements over SSIS 2005

May 30, 2010


This is continued live blogging from the Donald Farmer seminar from the SQL & BI convention in Tel Aviv of Data Integration in Large Organizations.

SSIS 2008 improvements over SSIS 2005

C# scripting can reference all .net assemblies
VSA replaced by VSTA

Pipeline Scale Up

Tuning SSIS through looking at the buffers on the Data Flow.
In SSIS 2005 you would add buffers and break up flows

Tools > Options > BI Designers > IS Designers:
Control Flow Data Connect
Data Flow Data Connect
Check them both to connect the components automatically

Use Row Sampling component to do performance testing.

One buffer structure goes all through the process.
We load data in the beginning but we don’t need it to move in memory. The problem is when one buffers slows down the process, it slows down for all the buffers before him as well.
In 2008 we have benchmarks for each buffer and if needed, create buffers artificially (rather than manually as we had to in 2005).

Asynchronous component – the input and output are different. We don’t know what have in the middle.
Sort – asynchronous, creates a new buffer as you can exclude a column.
The other components don’t need to be held up when an asynchronous component doesn’t run quickly, as they don’t depend on it.
Advanced Editor for the component: SynchronousInputID exists for synchronous components (and not for asynchronous).

ADO .net support – for improved source UI and added destination adapter (enabling you to use SSIS to load data into providers other than SQL Server).
The performance is still 30% slower because of the use of ADO .net provider, but it enables greater flexibility.
Use OLEDB providers as is, preferably don’t use .Net OLEDB providers through ADO .net providers unless you have to.
When you connect using the ADO .net, you will only be getting a list of tables. If the data isn’t in shape of tables, you won’t see anything in the dropdown list.

Use ADO .net if you have to use ODBC, or if the component you’re using requires it, or if you need it for your Script component.
Your default should still be OLEDB.

Import \ Export Wizard is much smarter:
ADO .net support for source and destination
Data types conversion – does only accurate conversions

Starting Import \ Export from the program menu enables straight execution. Starting it in SSIS Package requires execution to be done manually.  

Persistent cache usage reducing DB and memory usage.
Cache in Lookup enabling more control over sharing and lifetime of reference data.
Cache potential cons – requiring more disk storage and data in it available only for SSIS.

New to SQL Server 2008 that’s useful for SSIS 2008:
SQL Merge Syntax
Change Data Capture CDC (works only against SQL Server or through partner product against other sources)

Enhanced supportability:
In case SSIS crashes, SuperDump collects all the data needed to analyze why it crashed. Enables quicker time to analyze and treat the problem.

Data Profiling which enables you to react to the data quality. Enables analysis of the amount of nulls in a column, the uniqueness of a field etc.

Community Samples on Codeplex – code for you to use freely (SharePoint List adapter for instance).

More connectivity out of the box in SSIS 2008. The Attunity connector which is inside SSIS 2008 moves data into Oracle quicker than with the connector Oracle gives you.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



  1. Siddharth MehtaMay 30, 2010 ב 17:37

    I have a question – Why is there no spatial data support in SSIS ?

  2. Siddharth MehtaMay 30, 2010 ב 17:46

    Is built-in spatial data support planned in the future release of SSIS ?

  3. Ella MaschiachJune 2, 2010 ב 6:00

    Hi Siddharth,

    I admit I only saw the question after a while, and couldn’t ask Mr. Farmer that question myself. I understand the problem, but I can only recommend you vote on it on Connect.

    All the best,

  4. PonnuJune 8, 2010 ב 0:12

    Hi Ella,

    Thanks for more information about SSIS. I would like to read good SSIS books. Please post it.


  5. Ella MaschiachJune 9, 2010 ב 23:43

    Hi Ponnu,

    Well, I found a good post with a good list of interesting things from David Lean. I know Siddharth Mehta also has a book he reccomends.
    Last but not least, there are also these (and other) recommended books on the matter in Amazon:
    Knight’s 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services
    Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution
    Expert SQL Server 2005 Integration Services
    So browse away!

    Hope that helped,