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.

Upcoming Chicago Events!

Wanted to make sure everyone knew about upcoming events:

Magenic BI Summit on Thursday 8/23

This event will provide business-level arguments and strategies for optimizing use of data within the enterprise. Ted Krueger (b | t), Steve Hughes (b | t) and I will be there along with some other local Chicago BI thought leaders in the industry.  Don’t wait, register now!

 

CodeMastery Chicago on Wednesday 9/12

This event has 2 tracks – one for .NET development and one for Data.  In addition to Ted, Steve and myself we will have Rocky Lhotka (b | t) , James Phillips (t), Anthony Handley (b | t), Corey Miller (b | t), Prasanna Ramkuma (b | t), Sandy Fougerousse (b | t).  That’s an excellent line up if you ask me!  As you can see based on the lineup, this will be a very technical day so come out for a great time! Register here.

SQLFriends

SQLFriends Lunch with MVP Jes Borland on Friday 9/21

Come out and have lunch and a great time with Jes and your other SQLFriends. Register here.

SQLintheCityredgate SQL in the City on Friday 10/5

This is the second time this event has been in Chicago and I wasn’t able to go last year so I’m not totally sure what to expect.  However can’t argue with the lineup of speakers, so I’m fully expecting a good time. Register here.

 

SQLSaturdays

SQLSaturday

Don’t forget about the Local User Groups Chi-SSUG

SSIS 2008 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

Prior to SQL 2012 SSIS development is done in Business Intelligent Development Studio (BIDS) which is a Visual Studio shell.  so BIDS 2008/2008 R2 is a Visual Studio 2008 shell.  As such we can look at the documentation for Visual Studio 2008

2008 Managing Configuration Options

So what can be configured for our different environments out of the box?  Nothing.  Zilch, Nada.  In BIDS 2008 R2 and prior, they simply did nothing out of the box.  Configurations could be created through Visual Studio but SSIS has no hooks into them out of the box.

However if you’re willing to install a free codeplex tool or two, we can obtain some additional functionality.

First is BIDSHelper – this project adds additional functionality to the development of SSIS, SSRS and SSAS.  Some of the functionality was so good that SSIS 2012 development included some of it, you can see all the features here.

What can be tapped into with configurations is the Deploy SSIS Packages feature. With this different destinations can be specified per Visual Studio configurations to enable deployment to different environments  The way this works is BIDSHelper creates an XML file called:.dtproj.bidsHelper – within this file the environment destination type and destination are saved.  So when the project is deployed through BIDS it will actually, you know, be deployed.  This file can then be checked in and shared with the entire team.  However all team members will need BIDSHelper installed to utilize the functionality plus for some reason the file must be manually checked out when editing as opposed to the automatic process.

If BIDSHelper is installed on a TFS Build server, this file can also be fed through MSBuild for continuous integration or automatic build and deploy scenarios.  .   In the screenshots you can see that the Deployment is specific to BIDSHelper and how it points to different destinations.

Development Configuration that points to a local file system deployment destination:

SSISConfigs2008Dev

QA Configuration that points to a remote msdb deployment destination:

SSISConfigs2008QA

Behind the scenes, the information is stored in the XML file:

    Development

      SsisPackageStoreFileSystemDestination

      localhost
      Dev

    QA

      SsisPackageStoreMsdbDestination

      QAServer
      QA

Second option is to utilize MSBuild Extension Pack.  Out of the box SSIS 2008 R2 can create a deployment utility.  This is an option that allows for creating a SSISDeploymentManifest file that is an XML file but can be executed (assuming SSIS is installed).  Once executed a wizard will walk through the options for deployment including picking a destination type, destination and changing SSIS configurations provided that option was allowed when creating the manifest file.

SSISDeploy2008

However this is on the client only, MSBuild cannot create this file.  Thus enter the MSBuild Extension Pack.  Once installed on the TFS build server this will add additional MSBuild Tasks, Loggers and TaskFactories .  With regards to SSIS, it allows allows you to create the standard SSIS Deployment Utility from MSBuild.  Jamie Thomson (blog | @jamiet) has blogged about it here.

Happy Configuring!