CTP3 of SQL Server 2012, code named “Denali”, was made available recently, and it offers a lot of new features for SSIS.  Some of my favorites are:

• SSIS Projects, which is an encapsulation of multiple packages into a single unit that you build, deploy & execute.  See Introduction to SSIS Projects in Denali
• Project and Package Parameters, which are the replacement for configurations.  See Parameters in SSIS in Denali and Configuring Projects and Packages Using Parameters
• Shared Connection Managers, which can be used by multiple packages.  See Project Connection Managers
• Expression Indicator: Variables, Connection Managers and Tasks now have an fx adorner applied to them indicating that there is at least one expression on that object.  See Expression Adorners
• SSIS server: SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance
• Catalog, which is a database that stores deployed projects, environments, and package execution logs.  Each SSIS server has one catalog.  See SSIS Server, Catalogs, Environments & Environment Variables in SSIS in Denali, Deploying to the SSIS Catalog, Managing SSIS Projects through SSMS, Report Authoring on the SSIS Catalog, and Integration Services Catalog in SQL Server 2012
• Environments, which are a wrapper for all environment-specific information (e.g. Connection Strings) and are the replacement for configurations.  They work hand-in-hand with Project and Package Parameters.  See All About Server Environments
• Undo: Finally, the ability to “undo” in the designer.  See Undo, Redo, and new SSIS Toolbox Features
• Greatly improved column mapper, see Column Mapping Improvements in Denali
• Package format changes to make it easier to use in source control (i.e. log differences).  See Package Format Changes in SQL Server Denali
• New Data Quality Services (DQS) cleansing transform.  See video Using Knowledge to Cleanse Data with Data Quality Services, Overview of the DQS Cleansing Transform, Learning Resources for Data Quality Services (DQS) in SQL Server “Denali” CTP3 and SQL Server Data Quality Services – Domain Management
• Variables, now called parameters, can be given descriptions
• When you use the “Run Package” command in SSMS, the package runs on the server, as opposed to the way it is now which runs the package locally.  This clears up the problem I blogged about: SSIS package runs fine until put in a job
• Version control: each version of a package is tracked and you can rollback to a previous version if needed. See SSIS 2012 Project Versions
• New reports that provide an overview of the package tasks and parameters, including execution results and performance statistics (by turning on project logging, SSIS Logging in Denali).  Essentially it replaces the SQL Server agent log file summary into a readable format.  You can also write your own reports: see SSIS Reporting Pack
• Data tap: At any path, capture all the data coming through and output it to a text file to review later.  This is done without having to modify the package.  See SSIS 2012 Data Tap
• No need to capture row counts as there is now a way to query that result on any path in the package (SSIS automatically stores row counts in a table, among other data).  To make this happen, you will need the logging level set to Verbose.  To count such things as rows inserted, updated, and deleted, you should have three separate components (one that does the inserts, one that does the updates, and one that does the deletes) and look in [catalog].[execution_data_statistics] to discover how many rows rows were sent to those components – and there are your row counts
• Offline Connection Managers.  Gives you the ability to set the connection managers to work offline.  See Offline Connection Managers.  Solves the problem I blogged about: SSIS package taking forever to load when you open it?
• New Change Data Capture (CDC) tasks and components. See CDC in SSIS for SQL Server 2012
• In SQL Server 2012, you can now debug the Script component by setting breakpoints and running the package in SQL Server Data Tools.  See Script Component Debugging in SSIS 2012
• Upgraded the scripting engine to VSTA 3.0, which provides a Visual Studio 2012 shell and support for .NET 4.

The big impression I got out of all the new features is that many of them are the equivalent of what you find in a typical “ETL Framework” that many people have created to improve on SSIS.  So, in most cases for new projects you won’t need to use an ETL framework and instead can use the new built-in features of SSIS.  Fantastic!

To migrate your packages from SQL Server 2005/2008 to Denali, make sure to check out Migrating Your Projects to Denali.

More info:

Video from TechEd: What’s New in Microsoft SQL Server Code-Named Denali for SQL Server Integration Services

SSIS Team Blog: What’s New in SQL Server Denali

Jamie Thomson has a post about the SSIS enhancements in Denali CTP3

Jamie Thomson webinar: New SSIS Features and Enhancements in Denali

SQL Server Denali SSIS Enhancements Part 1 of 2

8 Enhancements in Integration Services 2012 for Easier Package Development

Variable enhancements in RC0 [SSIS]

Introducing SQL Server Data Quality Services, plus what’s new in SQL2012 SSIS

Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!

Video Managing SSIS in Denali

SQL Server Integration Services 2012 – Project Deployment Model