Automating build of Integration Services projects

After our experiences with the build process in Analysis Services – expectations were low when we turned our gaze towards Integrations Services (SSIS).

Our overall experience with SSIS has generally been positive – it’s an extremely powerful tool with some interesting performance characteristics. There are some annoying flaws – especially in the Visual Studio integration. When I work with SSIS projects – three or four daily crashes of Visual Studio is not uncommon. Never fear – the power provided by the tool more than makes up for this when it comes to choosing the ETL platform. And buggyness is what service packs are for … isn’t it? 🙂

The first thing you notice when you choose "build" in Visual Studio on an SSIS projects is that Visual Studio doesn’t do anything – except of course copy files to the bin directory. Why? Well, guess you could say that the *.dtsx files contain all the information need to run the packages – there is "nothing to be done".

Automating build of Analysis Services Projects

As you may be aware, an Analysis Services project consists of the following files:

• A project file (*.dwProj)
• One or more  Data sources (*.ds)
• One or more Data source views (*.dsv)
• A database file (*.database)
• One or more cube files (*.cube) with their partition (*.partitions)
• One or more dimension files (*.dim)

You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want – you will see why later.