January 3, 2012

SSIS 2012: Project Deployment Model and Build Configurations

With the introduction of the new project deployment model in SSIS 2012, we now have the ability to deploy a project to  a SSIS server and use parameters to packages and projects. With this, there may be instances where we need to change the name of the SSIS server and the value of parameters as we develop, debug and test packages within SQL Server Data Tools. Depending on the number of servers and parameters your project and package may have, making those changes may be rather tedious and prone to error. I am thinking in the many times people have mistakenly used the wrong connection strings when debugging a package in the old days - I have done it many time.
The good news is that projects using the new project deployment model in SSIS 2012 leverages build configurations from Visual Studio  in a more meaningful way than in earlier versions, making most of these changes a lot simpler. How? I am glad you ask. For one, both, project and package parameters can get their values from build configurations. For two, some project level properties can also be set via build configurations (things like Deployment Server name and project path); which makes debugging and deploying packages in SSDT (formerly known as BIDS)against different SSIS servers much simpler.


Binding Parameter values to Build Configurations

For this, you just need to go to the parameters tab (project or package) and click button with the weird icon. The tooltip actually says “Add Parameter to Configurations'”.

Then you have the opportunity to choose the parameters you want to associate with the multiple build configurations you may have created. In the picture below, you can see how my package has 5 parameters and I had created 2 build configurations, one called “Test” and one called ‘Development”. Now you can enter a value for each parameter and configuration combination.



So, how is this helpful? Well, Assuming that I need to change the value of the 5 parameters at design time as I debug the package in 2 different machines, I just need to switch between build configurations and to have all parameter values changed at once. Switching between build configurations is as simple as selecting a value from a dropdown list.










Binding Project Properties to Build Configurations

Similarly, there are some Build, Deploy and debugging properties of the project that can be assigned using build configurations. For that go properties of the project and select the the properties and the configuration and assigned the desired value.



Lastly, keep in mind that build configurations are not new to SSIS 2012 projects, but I honestly did not find them helpful until now.

5 comments:

  1. Raf, your blog kills me. Days and days of nothing at all accented by moments of sheer coolness!

    JamesNT

    ReplyDelete
  2. It sounds like it is very easy to make a mistake.... it sounds like each dev person needs config for each own machine which sometimes is different... it sounds like developer is the best person to manage ALL configurations incl production? Also it seems we need to deploy entire project even if we make change only one in package.... that might cause issues... and we will have to double check deployment files very carefully to ensure we don't deploy unfinished packages

    I'm not saying I don't like it I just still have a few questions :)

    ReplyDelete
    Replies
    1. Emil, I agree with you that is very easy to make mistakes in picking to the wrong target. It requires discipline and some security model in place. Why in the world would one, as developer, have permissions to make changes in a production server?
      So yes, this is flexible, but it can be messy if not managed properly

      Thanks for your feedback

      Delete
  3. Thanks for your blog, it's nice :)

    I'm testing SQL Server 2012 BI and I have a simple question about the deployment method.
    Is it possible to do the mapping of the environment that I created with SSDT like you explain on this topic on SSMS ?

    ReplyDelete
    Replies
    1. Test, although SSMS runs in Visual Studio shell as SSDT, I can picture how SSMS could leverage such functionality. You would need to try it out.

      Thanks for your comment.

      Delete

I will love to hear your feedback and comments. Thanks.