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!