I recently viewed an excellent presentation on some of the new
features in SQL Server 2012 Integration Services such as shared
connection managers, parameters, undo/redo in the designer, and some
visual changes as well. 
One
of the most significant changes that really caught my attention is
Integration Services’ new Project Deployment Model, specifically the
Integration Services catalog.
The Integration Services catalog is created on your instance of SQL
Server and it is now what stores all of the objects associated with your
project once the project is deployed. A major improvement is that once
you deploy your project, all of the packages are organized into folders
in the catalog. This means no more scrolling through an endless list of
packages installed on your server! Simply find your project, open it up,
and voilà – they’re all there. Security can be configured at the folder
level as well.
Also included in the catalog are handy little objects called
Environments. I bet you already have an idea of what these are for. Once
your package is deployed to your SQL Server, you can easily change it
to run from a development environment to production environment by
setting up a couple of environment variables and configuring the package
to use them. You can also edit any parameters or shared connection
managers you may have created during design time. The beautiful thing is
that you can manage and change all of these types of configurations
through the catalog without having to redeploy your project or packages.
Microsoft put a lot of thought into this aspect of Integration Services and it was well worth it. The increased flexibility makes managing packages and deployments much easier with Integration Services 2012.
One
of the most significant changes that really caught my attention is
Integration Services’ new Project Deployment Model, specifically the
Integration Services catalog.
The Integration Services Catalog
The Integration Services catalog is created on your instance of SQL
Server and it is now what stores all of the objects associated with your
project once the project is deployed. A major improvement is that once
you deploy your project, all of the packages are organized into folders
in the catalog. This means no more scrolling through an endless list of
packages installed on your server! Simply find your project, open it up,
and voilà – they’re all there. Security can be configured at the folder
level as well.
Environments
Also included in the catalog are handy little objects called
Environments. I bet you already have an idea of what these are for. Once
your package is deployed to your SQL Server, you can easily change it
to run from a development environment to production environment by
setting up a couple of environment variables and configuring the package
to use them. You can also edit any parameters or shared connection
managers you may have created during design time. The beautiful thing is
that you can manage and change all of these types of configurations
through the catalog without having to redeploy your project or packages.
Interacting with the Catalog
The Integration Services catalog also includes a variety of views which allow you to query various aspects of the catalog and the packages stored in the catalog. That includes environment details and variables, projects, and permissions – right down to the actual parameter value of a package during execution. Additionally, there are several stored procedures included, giving you the ability to set environment variables and parameters, among other things, through T-SQL.
Versioning
Another important feature of Integration Services is versioning. You can have multiple versions of a project stored in the catalog and decide which version of the project you would like to run. This will come in handy if the new version you just deployed bombs and you need to quickly revert to the prior version that worked.Reports and Dashboards
Oh yeah, one more thing: included in the catalog is the new Integration Services Dashboard. Thanks to built-in logging, the catalog stores all activities during the execution phase of the package when using the project deployment model. The catalog comes equipped with a set of predefined reports available, which will present you with a dashboard overview of the execution of the package. The dashboard also allows you to drill deeper into the report for more detail.Upgrading your legacy 2005/2008 packages
What about upgrading from SQL Server 2005/2008 to SQL Server 2012? There’s good news and bad news on these legacy packages. The bad news is that, yep, the stellar ETL package you just deployed yesterday in SSIS 2008 is now a legacy package. But have no fear, because the good news is that the new project deployment model is fully backwards compatible. With the help of the Migration Wizard, your legacy packages can be converted and deployed to the Integration Services catalog easily, so upgrading to SQL Server 2012 will not be a problem.Microsoft put a lot of thought into this aspect of Integration Services and it was well worth it. The increased flexibility makes managing packages and deployments much easier with Integration Services 2012.
No comments:
Post a Comment