New Features Of SSIS 2012

New Features For Integration Services (SSIS) | New Features in SQL Server Integration Services 2012 | SQL Server 2012 Business Intelligence Enhancements | SSIS 2012 Improvements | What's New In Integration Services | Jumping from SSIS 2008 to SSIS 2012 | What's new in SQL Server Integration Services 2012 | BI Features in SQL Server 2012

Here are the major changes and things to learn to make the transition easier:

1. Read an overview of the new features: SQL Server 2012 (“Denali”): Details on the next version of SSIS.

2. Understand the new SSIS server and new terminology: SSIS Server, Catalogs, Environments; Environment Variables in SSIS in Denali (SSIS server is no longer a separate service that you connect to as it now appears as a node in Object Explorer when connecting to a SQL Server database instance).

3. Create a SSISDB catalog: Create the SSISDB Catalog.

4. Create a project (Introduction to SSIS Projects in Denali) and use Deploy (Deploying to the SSIS Catalog) to deploy your project to the SSISDB Catalog.  Note we now deploy projects instead of packages via the new project deployment model (the legacy package deployment model can be used if needed).

5. Use project connection managers: Project Connection Managers.

6. Make sure to be aware of the new Undo/Redo commands: Undo, Redo, and new SSIS Toolbox Features.

7. Understand Logging (a built-in logging infrastructure that tracks which packages where executed, how long they took, error messages, etc): SQL Server 2012 (“Denali”): SSIS Logging. Set logging to Verbose (Setting a Default Logging Level in the SSIS Catalog) and use the SSIS Reporting Pack to see more info such as row counts.  To get counts of rows updated, deleted, and added, you will need to have a data flow with separate components for each.  If row counts are not needed, just use the built-in logging (Reports for the Integration Services Server).

8. If you have used a SSIS framework in SSIS 2008 it may no longer be needed: Is the SSIS Catalog going to be enough?.

9. Use Parameters and Environments instead of package configurations to assign values to parameters based on different environments.  Parameters are similar to Variables in SSIS 2008: Parameters in SSIS in Denali and Configuring Projects and Packages Using Parameters.  Package configurations are still supported via the legacy package deployment model if needed but you can convert those packages via Converting to the SSIS 2012 Project Deployment Model.  Project Parameters are defined outside of a package hence can be accessed by any package within the Project (so no need for Parent Package Configurations or referencing the same configuration file from multiple packages).  Note that multiple execution environments (i.e. development, QA, production) can be maintained from a single SSIS instance, although I think most solutions will have a SSIS server for each environment.  And in case you create an Environment with a ton of variables and want to copy it (i.e. copy a dev Environment to a qa Environment), here is a trick to copy an Environment: SSIS 2012 Copy or Duplicate Environments.

10. Use Environments to configure Environment Variables to hold connection string info such as server names for each different environment (i.e. ServerA for the FinancialServer for development, ServerB for the FinancialServer for QA).  Again this removes the need to use package configurations: SSIS 2012 Deployments, Lesson Two — Part B.

11. Understand the changes to the Execute Package task: The new Execute Package Task in SSIS in Denali.

12. Be aware we can now call SSIS packages from within T-SQL scripts: Execute SSIS via Stored Procedure (SSIS 2012).

13. We now have version control: SSIS 2012 Project Versions.