SQL Server “Denali”: Details on the next version of SSIS

CTP3 of SQL Server Code Name “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
• 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 and Deploying to the SSIS Catalog and Managing SSIS Projects through SSMS
• 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
• Undo: Finally, the ability to “undo” in the designer
• Greatly improved column mapper
• Package format changes to make it easier to use in source control (i.e. log differences)
• New Data Quality Services (DQS) cleansing transform. See video Using Knowledge to Cleanse Data with Data Quality Services and Overview of the DQS Cleansing Transform and Learning Resources for Data Quality Services (DQS) in SQL Server “Denali” CTP3
• 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
• 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
• 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)
• 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?

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