SSIS - What’s New in SQL Server Denali

SSIS - What’s New in SQL Server Denali

Rate This
  • Comments 17

What’s new in SSIS? In a word: Lots.

It’s been a lot of fun showing off the new SSIS features and functionality – the hardest part has been fitting it all into a 60 minute timeslot. This CTP is close to feature complete – the rest of the time we have for the Denali release will (mostly) be spent improving and polishing what’s there. However, you can expect a couple of new features to slip in for the final release - we have to leave some surprises for RTM, right?

There have been two major themes for SSIS in the Denali release – improving the Developer Experience, and making SSIS solutions easier to Deploy, Configure, and Manage.

Developer Experience

We wanted to make the designer easier to use, as well as deliver productivity enhancements to delight our existing users. Some of the major features here include:

  • Visual Studio 2010
  • New VSTA scripting environment (also based on VS 2010)
  • Undo/Redo
  • Shared Connection Managers
  • Icon marker (“Adorner”) to indicate Connection Managers and Variables with expressions
  • New SSIS Toolbox
  • “Getting Started” window with links to samples and videos
  • Variable window improvements
    • Variables created at the package scope by default
    • Button which allows you to move variables to new scopes
  • New data flow column mapper
  • Improved performance when loading packages in the designer
  • Simplified data viewers
  • New package format makes it easier to diff changes
  • Data Quality Services Cleansing transform

image

Deployment & Configuration

The deployment and configuration improvements revolve around the new SSIS Catalog (or SSIS Server). This catalog is essentially a SQL application - a user database on a SQL instance (SSISDB) with a set of stored procedures and a T-SQL API. In addition to the server, we’ve introduced a new Project model for bundling your packages together to simplify deployment, and a new Parameter model to make configuration and management a little more straight forward. This CTP contains a number of enhancements to what was delivered in the previous CTP, as well as a bunch of new features, including:

  • Automatic capture of package execution logs
  • Various logging improvements
  • Built-in reporting
    • Integration Services dashboard – see all packages that have run on the server in the past 24 hours
    • Performance reports – see a package’s performance over time
    • Error message report – shows failed package executions and related error messages
    • … and more!
  • Data tap functionality – dynamically capture/log data as it flows through the package (without modifying your package)
  • Create SSIS Catalogs on remote machines via SSMS
  • Connection Manager properties are automatically exposed on the server

 

CTP Improvements

We have made further improvements to the new functionality that appeared in the previous Denali CTP – thank you all for your feedback!

  • Improved Deployment and Migration Wizards
  • Project parameters now have their own node in the Solution Explorer
  • Package parameters have their own UI
  • Dependency Services feature (also known as Impact Analysis & Lineage) is now Project Barcelona
  • Removed the Data Sources folder (support for .ds files)
  • Added “Parameterize” option on the right-click context menu for Tasks and Variables
  • Ability to control logging level on the server
  • SSMS manageability improvements

 

Other Changes

In addition to the major themes, we’ve made many small enhancements and quality improvements through out the product. Some of the more noticeable ones include:

  • Annotations auto-grow, wrap when you hit enter, and are persisted as clear-text
  • Auto-save and recovery in BIDS
  • Expression improvements
    • 4000 character limit is removed
    • New functions – LEFT, TOKEN, and TOKENCOUNT
  • Connection Managers validate in parallel
    • Can be taken “offline” individually
  • Better error messages for the Execute SQL Task
  • Option to allow automatic type conversion in the Execute SQL Task parameters and result values
  • Flat file source supports embedded qualifiers and a variable number of columns per row
  • Raw Files now include sort information
  • New Expression Task
  • Scripting engine improvements

Here are some additional links if you’re looking for more information.

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • Does this CTP (3) break BIDS 2008 as the CTP1 did?

  • Looking forward to the series.  Of course, I'm more looking forward to RTM to get some of this functionality into production!

  • ArthurZ, no - the CTP1 release notes stated that it couldn't be installed SxS with 2008. Since we are providing a brand new BIDS shell (VS 2010), it should install just fine with BIDS 2008. If you run into issues, report it on Connect.

  • Thank you for the quick reply Matt!

    I just seen that VS2010 may actually break and that I need to re-apply SP1 to fix it.

    Besides, the Data Quality Services link takes to a page that has no content, any other to learn more about it?

  • The Data Quality Services team will be posting more information soon. You can also check out the link at the end of the post for their TechEd 2011 presentation.

  • Are their any improvements to Web Services support in this version ?  The fact that We can't connect to Salesforce.com (amongst others) without developing custom code is likely to mean we move to another ETL tool in my organisation

    Leigh.

    www.isql.org.  

  • Leigh - there are some minor bug fixes for the Web Services task, but no major improvements. Due to the nature of web services, I typically recommend using a Script Task / Script Component instead. Using proxy classes from scripts was made a lot easier in 2008, and continues to work well in Denali. Scripts give you more control over the data, and allow you to format it as you'd like.

    If you're looking specifically for Salesforce.com integration, CozyRoc supplies a custom connector for SSIS. www.cozyroc.com/.../salesforce-source

  • Will Crescent be available for Standard Edition of SQL Server or only Enterprise Edition?

  • You'd need to ask on the RS forums / blogs about Crescent, sorry!

  • I have installed SQL Server Denali CTP 3. It looks great. My favorite is SSIS. Look and feel is fabulous. features such as DQS and shared datasources, etc are really great.

    SSIS Denali will be changing the ETL world once again.

    :)

  • Before I get started I just want to say I'm not shooting the messenger here, but I can't leave this alone....

    "•Flat file source supports embedded qualifiers"

    This is utterly embarrassing for Microsoft to have to add to the list of features or improvements.  This is Database-101 and worked fine in SQL 2000 but for some reason was removed or bugged in 2005 and 2008 (yet the text qualifier setting was available in the Import Wizard...go figure).  To boot, MS products (confirmed in my version of Excel 2000 and Excel 2010) export data with text qualifiers yet SQL 2005 and 2008 wouldn't import them (at least not without a workaround or two).

    I'm absolutely confused as to why it wasn't fixed back in 2006 when MANY reported it.  We'll see if it's actually back in place like SO many want it to be...of course I won't see this until we upgrade in another 6 or 7 years.

  • Is there a reason why connections in the global Connection Manager do not accept expressions?  Because of this, I do not see any feature for portability, and hence no need for global connection managers.  It would have great to have expressions for a global connection, instead of having to create local connections, and setting up the expressions individually.

  • thisfutile1 - supporting embedded qualifiers can actually have a pretty significant impact on performance of flat file parsing. If you look around, you might find that many parsers out there don't support them for that reason. we're pretty good about mitigating this performance impact in Denali, but it's something to keep in mind. That said, we pay close attention to feedback and issues reported through MS Connect. Like all development teams, we have finite resources, and can't address every issue in a release, but we do our best to make our users happy.

    Hi Ian - this was a limitation in CTP3 that has since been addressed. You can now set expressions on project level connection managers (but any parameters the expression references must also be declared in the project scope). Can you explain why you need to set an expression on the project connection manager? are you trying to update the value at runtime from within the package?

  • I have some text mining/analytic's coming up and I was wondering if their are any improvements in that area ?  The Term lookup / Term extraction don't really cut it in 2008 r2 (correct me if I'm wrong, but they were unchanged since 2005).  

    Is the current CTP feature complete ?

  • The current CTP is almost feature complete - we announced some new features at SQL PASS as well. I'll be posting some new content once it is publically available.

    As for Term Lookup / Term Extraction, no - I believe some minor bug fixes were made, but there are no new features added to these transforms for SQL 2012. If you're looking for matching functionality, you might take a look at the features in Data Quality Services.

Page 1 of 2 (17 items) 12