What’s New in SSIS for SQL Server 2012 RC0

At the 2011 PASS Summit I presented a Top 10 list of features that had been added since CTP3, or hadn’t gotten much attention so far. Now that the SQL Server Release Candidate 0 (RC0) available, I can start blogging about them! I’ll continue to post more details over the next couple of weeks.

Top 10 Features You Didn’t Already Know About (from PASS)

#1 – Change Data Capture

We’ve partnered with Attunity to provide some great CDC functionality out of the box. This includes a CDC Control Task, a CDC Source component, and a CDC Splitter transform (that splits the output based on the CDC operation – insert/update/delete). It also includes CDC support for Oracle. More details to follow.

#2 – ODBC Support

ODBC Source and Destination components, also from Attunity, and included in the box.

#3 – Connection Manager Changes

RC0 makes some minor improvements to Shared Connection Managers (they are now expressionable), and changes the icons used to designate connection managers that are shared, offline, or have expressions on them. We also added a neat feature for the Cache Connection Manager – it can now share it’s in-memory cache across package executions (i.e. create a shared connection manager, load the cache with a master package, and the remaining child packages will all share the same in-memory cache).


#4 – Flat File Source Improvements

Another feature that was added in CTP3, but worth calling out again. The Flat File Source now supports a varying number of columns, and embedded qualifiers.


#5 – Package Format Changes

Ok, another CTP3 feature – but when I demo’d it at PASS, I did a live merge of two data flows up on stage. And it worked. Impressive, no?

#6 – Visual Studio Configurations

You can now externalize parameter values, storing them in a visual studio configuration. You can switch between VS configurations from the toolbar (like you can with other project types, such as C# or VB.NET), and your parameter values will automatically change to the value within the configuration.


#7 – Scripting Improvements

We upgraded the scripting engine to VSTA 3.0, which gives us a Visual Studio 2010 shell, and support for .NET 4.

Oh… and we also added Script Component Debugging. More about that to follow.

#8 – Troubleshooting & Logging

More improvements to SSIS Catalog based logging. You can now set a server wide default logging level, capture data flow component timing information, and row counts for all paths within a data flow.


#9 – Data Taps

Another CTP3 feature that didn’t get enough attention. This feature allow you to programmatically (using T-SQL) add a “tap” to any data flow path on a package deployed to the SSIS Catalog. When the package is run, data flowing through the path will be saved out to disk in CSV format. The feature was designed to make debugging data issues occurring in a production environment (that the developer doesn’t have access to).


#10 – Server Management with PowerShell

We’ve added PowerShell support for the SSIS Catalog in RC0. See the follow up post for API examples.

Other Changes

  • Updated look for the Control Flow and Data Flow
  • Pivot UI
  • Row Count UI
  • New Expression:
  • BIDS is now SQL Server Data Tools
  • Many small fixes and improvements based on CTP feedback – thank you!!
Comments (20)

  1. Again, great stuff. This will be the best release of SSIS ever.

  2. Ayyappan says:

    Awesome…These new features triggers me to tell everyone to change their current SSIS version to SSIS 2012 ASAP. 🙂

  3.  Some good looking features. Strangely I'm really looking forward to REPLACENULL!

  4. Marlon Ribunal says:

    So with improvement #4, nothing like this dbalink.wordpress.com/…/ssis-2008-flat-file-source-issue will be encountered again. One does not need to use a third party component anymore.

  5. Thats some really nice improvements, now get back to work… can't wait 😉

  6. Sathyanath says:

    #3 and #4 looks gr8… looking more on hiding source columns post Data Conversion.

  7. Ramesh says:

    In future i think SSIS is the best ETL tool. I am eager to work with SSIS 2012 🙂

  8. Srini says:

    Just Fantastic !! My Best are #1,3,4….

  9. Lyle Davis says:

    Would be nice to see connectors to SharePoint lists – to either pull or populate data.

  10. Zaim Raza says:

    does any one have idea regarding SSIS 2012 logging mechanism in detail. i am keen to learn this.

  11. Jamie Thomson wrote an article about report authoring on the SSIS Catalog – that might be what you're looking for.


  12. Kapil Singh says:

    Really gr8 feature has been added but ia m still looking component level parallelism in SSIS.

  13. Leigh says:

    Could you please clarify the decision to deprecate OLE-DB and what that means for SSIS 2012 ?


  14. John Dennison says:

    Fantastic to see that component script debugging has been added at last. Also like the new way to pass down parameters from parent to child packages. Always thought this was the more sensible way to do it.

  15. Harkaran says:


  16. Kalpesh says:

    my customized shortcuts are not working in this version of SSMS( menu Tools/Option/Environment/Keyboard/Query Shortcuts), would you help me to figure out the problem

  17. Todd Robinson says:

    Do you have more updates on the new CDC functionality, especially the CDC support for Oracle?

  18. Scott Nelson says:

    How about easy, out of the box UPSERT support ?

  19. VInoth says:

    In future i think SSIS is the best ETL tool. I am eager to work with SSIS 2012 🙂

  20. Darryl Kerkeslager says:

    Like the Data Taps. Could have used teh support for varying number of columns just yesterday. Now if it would just support SFTP ….