SSIS 2012 Project Configurations

Configurations is a redundant, overloaded term (see what I did there?) when it comes to Visual Studio SSIS projects. When people talk SSIS and configurations mostly it is regarding runtime configurations which allow values to be passed into an SSIS package. That’s not what these posts are about.

Instead these three posts are about Visual Studio Project Configurations and how they work (or don’t work) with SSIS.

  1. What are Visual Studio Project Configurations
  2. SSIS 2008 Project Configurations
  3. SSIS 2012 Project Configurations

As SQL Server Data Tools (SSDT) and SSIS 2012 have been promoted and talked about, one of the things that has been mentioned is that SSIS 2012 now works with configurations. Yay. SSDT is now built on Visual Studio 2010 shell (Yes Visual Studio 2012 was just released and no we are never going to get away from running multiple versions of Visual Studio). Here’s the documentation for Visual Studio 2010 Managing Configuration Options.

For those that haven’t seen SSIS 2012 yet, there have been some significant changes. One of the many changes is SSIS packages are managed as a project in what’s called Project Deployment Model (vs. the package deployment model which we have been using – see the comparison here). Within this model there is the ability to set project level options including Project Parameters which allow values to be passed into the project at runtime. This is the replacement for the SSIS runtime configurations we have used.

So first thing is checking BOL see what is mentioned and what is configurable. Surprisingly I was unable to find much of anything about them in BOL. Course searching for combinations of SSIS, SSDT, VS, Environment, Configuration brings back a whole lot of stuff, per the overloaded terms as already stated above.

So what can be configured using Visual Studio Configurations? Turns out that the only thing that works with VS configurations is setting and changing the values of the project parameters within SSDT. Nothing regarding deployment options, build options, logging options, etc. Here are the dialogs:

ConfigProjParm

ManageParms

Selecting the environment in SSDT will only apply these values within the scope of SSDT. This can be useful for validation purposes against different environments or runtime execution, provided it is being executed within SSDT. So how do we manage automated builds? Well we’re back to installing a codeplex project. Take a look at this where Matt Masson (b | t) has provided msbuild support for ispac file. You might see the trend that SSIS build ispac files, while SSDT builds dacpac files (no we don’t have rspac or aspac deployment files, at least not yet).

Overall SSIS 2012 is a huge improvement and personally I have really been enjoying using it, however I must confess to seeing irony in an integration product not being fully integrated within its own development platform. Where’s the continuous integration? Where’s the code analysis? Where’s the unit testing? Where are those things that allow confidence in creating and maintaining quality code? Yes SSIS has automated logging now through the catalog, great it is easier to investigate crap code in production, however where’s the validation prior to release?

I’m not saying these aren’t difficult problems to solve, and I’m a big proponent of code reviews. However I’m going to continue to trumpet how data, arguably one of many companies’ most valuable assets needs to have quality code surrounding it. Until the data tools mature to provide more, we’re going to continue to have issues.

  • http://sqlblog.com/blogs/jamie_thomson Jamie Thomson

    "Where’s the continuous integration? Where’s the code analysis? Where’s the unit testing? Where are those things that allow confidence in creating and maintaining quality code?"

    It would be hard for me to agree more with this point-of-view. SSIS is still sadly lacking in this regard (much like the whole of SQL Server in general – though the DB engine is going in the right direction with SSDT).

    • http://www.aaronlowe.net Vendoran

      I'm not convinced yet about SSDT. I was really excited about datadude in 2005, 2008 and 2010 and as you know not much innovation or improvements over that time.

      In SSDT I like the localdb instance and think it's a better platform going forward than VSDB, however not having Unit Tests yet is a tough pill to swallow. Looking forward to the next release for them (6 months after VS2012 RTM right? :) ).

      However things like Code Analysis or even basic TSQL formatting haven't been touched. Getting excited about right-click go to function in T-SQL, while I'm thankful to have the feature just feels like…this is it? We still have a long way to go. And this is from someone that wants to be a believer :)

      • http://sqlblog.com/blogs/jamie_thomson Jamie Thomson

        Hmm…I'll respectfully disagree. I'm loving SSDT – I think its a really good step forward.

        Lack of unit testing doesn't bother me – if you were using unit testing before then you still can – simply carry on using your old datadude test projects. If you weren't using it before then you're not missing anything.

        • http://www.aaronlowe.net Vendoran

          Really? Glad to hear you are enjoy it. I was waiting until the next release to really dive in, but I'll take a look at it again. Thanks.

      • http://sqlblog.com/blogs/jamie_thomson Jamie Thomson

        P.S. A tip, rather than "right-click go to function", just press F12 :)

  • http://sqlblog.com/blogs/jamie_thomson Jamie Thomson

    "no we don’t have rspac or aspac deployment files, at least not yet"

    Fancy putting a request on Connect for that? It'd definitely get my vote.

    This is kinda implying something similar – but doesn't explicitly ask for .rspac & .aspac files: https://connect.microsoft.com/SQLServer/feedback/

  • http://texastoo.com Lee

    Good read, thanks for sharing. I'm not sure MSFT is going to put a whole lot into anything other than Azure for awhile, and other parts of SQL Server will suffer unfortunately.