posted 5/22/2012 by DevinKnight - Views: [2256]
The new project deployment model in SSIS 2012 is the new standard for how packages are created, configured and deployed. Any new packages that are created in SSIS will default to using the new deployment model.
The most basic way of describing this change is now instead of deploying packages we now deploy projects. Projects get deployed to the new Integration Services Catalog, which I’ll cover in a future post. Also, you get the benefit of project parameters. which allow you to pass values very easy across multiple packages in a project. Jamie Thomson has a great series on this topic as well I encourage you to read what he’s written here.
After upgrading to a project deployment model you also lose some capabilities you previously had. For example, configurations that were a staple for how to configure you SSIS packages across multiple environments in previous versions of SSIS are no longer available. The previously mentioned project parameters now replace configurations along with Environments, which live on the Integration Serves Catalog.
Obviously these are a lot of new concepts and totally changes the way you think about deploying and configuring SSIS so I plan on explaining each of these in separate posts. In a previous post I walked you through upgrading your SSIS packages to SSIS 2012. As I described in that post, the upgrade wizard does not change the deployment model for you. So after completing a package upgrade you will be running the legacy package deployment model as labeled below. This means everything developed in the original packages is still available including configurations. You can still run you packages using the package deployment model but I would recommend upgrading for the benefits detailed earlier and just like anything else new at Microsoft the old way of programming will likely be deprecated over time.
Once you are ready to convert from the package deployment model to the project deployment model right-click on the project name and select Convert to Project Deployment Model as shown below.
As soon as you select that you may get prompted with a warning telling you that Data Sources you have in the Solution Explorer will be removed with the project deployment model. These are no longer supported with the project deployment model but are could be replaced with share connection managers. Unfortunately, it does not automatically replace your data sources with shared connection managers. Click OK on the warning.
The start of the wizard details the 7 step conversion process, which includes converting configurations to project parameters and changing execute package tasks. Click Next when ready.
The first step is to select the packages you want to convert and provide and passwords that you may have used to encrypt them. After making your selection click Next and the wizard will load the packages for making the conversion.
You will then be asked to assign a name to the project if different from the original name. New in the new deployment model you can also assign a protection level to the project. Once you’re done here click Next.
The wizard will also upgrade any Execute Package Tasks that it detects. The task had a bit of an overhaul and now when using the project deployment model you now reference packages that are part of the project instead of a file or server that contains the package. The old way of running packages is still available but it’s now referred to as an external reference as opposed to the new project reference. The defaults here are generally acceptable but you should review them then click Next.
Next you will be asked to select configurations to be converted. Remember configurations will be replaced with project parameters. If a configuration is not found for some reason (maybe the file connection couldn’t be made) you can also add it manually here. When ready click Next.
To fully replace the configurations the wizard will create parameters that will store the same information the the configuration did. Ideally if these configured values are the same across multiple packages you would choose to scope these as project parameter instead of package parameters, which is the default. Parameters scoped at the package level are only available within that package. Leaving theses all as project parameters will result in only 2 parameters created even though it appears from my screenshot that I will have 10 parameters because several of these are duplicated. Click Next.
The parameters that you just created can now be configured to have new values if you choose. In my case I have connection strings in my value and I should verify that the provider used is appropriate for the version of SQL Server these packages will run on. Click Next.
You can review the steps that are about to occur if you would like and click Convert when ready.
Once the conversion is complete you should see a screen similar to below. You will notice it also prompt you to save as soon as you close the wizard.
A couple things to note about the conversion. You Solution Explorer will appear different. Notice Data Sources are no longer here, but you do have a Connection managers folder. You’ll find Connection Managers to be a lot more useful and I will detail those in a later blog. You will also see a section called Project.params. These are your project parameters and can be defined by double-clicking on the object in the Solution Explorer.
The parameters are basically variables that can be defined across the entire project. Again are what the wizard used to replace any configurations you used previously. Below you will see what the definition of these project parameter store and then to assign them to configure an object in the package you will use configurations. Luckily the wizard automatically did that for any configurations used previously.
So when you look at any connections or other objects that used configurations you will now find an expression that references the project parameter. Notice below in SSIS 2012 it’s much easier to find connections that have expressions on them because they’re labeled with a little fx icon next to them.
When looking at the expressions on these connection you will see it does indeed reference the project parameter.
The last thing to point out that the wizard did for us is a change to Execute Package Tasks that I had. The change is simple enough but it’s a new concept to 2012. Basically instead of referring to a package that lives in the files system or SQL Server you can now reference package within the project. This is called a Project Reference and the change is done automatically for you using the wizard.
I hope you found this post helpful as you walk through through converting your SSIS packages to use the new project deployment wizard. In my next post I’ll walk you through deploying these package to the Integration Services Catalog.