Swollen SSISDB caused by Continuous Deployment

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job.

The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps

SSIS Server Operation Records Maintenance
TSQL Script: EXEC [internal].[cleanup_server_retention_window]

SSIS Server Max Version Per Project Maintenance
TSQL Script: EXEC [internal].[cleanup_server_project_version]

The problem is we do Continuous Deployment (CD), we use Octopus Deploy to automate our deployment process – it’s so effective we often do multiple releases a day. This in turned caused the log file to swell when a large number of old projects are removed, which results in the drive filling – despite the recovery model being set to simple.

The solution was simple, add an additional log file, located on another drive (I used the backup drive as it has tons of free space), run the job, truncate the log files, then remove the extra log file. Then it was just a question of running the two cleanup stored procedures post deployment.

Another year, another SQL Saturday

Unfortunately this year I’ve not been able to make any of the pre-cons, however the (free) community day looks epic – the hardest part is going to be selecting which session to attend, the final one looking the worst. Aghhh!!! I’m going to have to roll a dice or something!

CDC – Change Data Capture in Detail
Uwe Ricken

What’s new in the SQL Server 2016 for a BI Professional
Prathy Kamasani

Exploring the Azure SQL Database
Murilo Miranda

All things time-related
Kennie Pontoppidan

Data-driven personal decisions with PowerBI and Machine Learning
Ruben Pertusa Lopez

One session that really peak my inter-geek, which had me looking at getting a Microsoft Band – only to find out they are running the stocks low at the moment (they release the first and second one in October, so it looks like the 3 will be out soon).

The (Consumer) Internet of Things on your arm – MS Band & PowerBI connected

The Internet of Things (IOT) gets more and more attraction - not only on the business but also on the customer side. Connected fridges, cars and smart watches - always and everywhere connected! In this session Wolfgang will show you some possibilities of the Microsoft Band 2 SDK: how-to connect and read sensor data out of this device. But what should be done with that data? 
Power BI seems to be an ideal candidate for analyzing and presenting those kind of data. The different types of real-time analytics (Stream Analytics, Power BI API, ..) will be presented and their pros and cons will be envisioned. 
The challenge: Let's prepare a real-time dashboard of Band2 data in Power BI in 60 minutes!

Wolfgang Strasser

It’s also good to see the a small local company getting heavily involved :p

And not forgetting the Micro:Bit sessions on

Imagine If – Train the Trainer Micro:bit session

Want to help inspire young people and deliver a Micro:bit workshop?

If you are a teacher, parent or anyone interested in inspiring the next generation of technical experts; then come along to the Micro:bit session to get you started on how you could deliver a micro:bit workshop for either Primary or Secondary school children to code and create. 

In this session you will be taken through a workshop setup and approach (introduction, 3 coding tasks and a quiz) as well as getting hands on with the BBC micro:bit

This session requires external registration via http://microbit_opt1.eventbrite.co.uk

Amy Nicholson

Error building SSDT package

Today my colleague had a problem opening our BI solution, the solution had multiple projects, including 3 SSDT projects. Although the project builds correctly on both my machine, the build machine and another colleague machine it refused to build stating that the reference to the object in another project was invalid.

After thinking for a few moments, I remembered I had seem this before. The problem was a bug in SSDT. The solution was to click on Tools > Extensions and Updates, then click Updates from the left-menu on the window that appears.

MDSCHEMA_CUBES DMV not returning all cubes

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentation to feed the foreach loop.

However there is a major problem with the DMVs. They only list processed cubes (and their dimensions). So how do you get a list of unprocessed cubes (and their dimensions)?

Answer? Using the Analysis Management Objects (AMO). This will return the same list as SQL Server Management Studio (SSMS), rather then just the processed, like the DMV or Excel lists. I’m currently trying out a few ideas to find the best solution – so as a script component (source) or a stored procedure. As the saying goes, it’s production ready, but its not GitHub ready – not yet anyway.

Thanks again to Chris Webb for pointing me in the right direction


New laptop, error running SSIS package

So today I went to run a SSIS package on my new laptop and bam, error message.

Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.   —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents)     at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     — End of inner exception stack trace —at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package()     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

Translates as I hadn’t installed the Oracle client and the Microsoft Connectors v3.0. Speaking of the connectors, the Attunity Oracle adapters are amazing, if your connecting SSIS to Oracle, these are a must (especially as they are free!)

Automated SQL Server BI deployments with OctopusDeploy

Today, my pull request for the SSAS deployment script was merged into the Octopus Deploy Community Library.

So what is Octopus Deploy and why should I care?

Octopus is a friendly deployment automation tool for .NET developers.

But don’t be fooled. Octopus Deploy isn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopus to deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008. I use DbUp, a open source library, for database deployment and a custom C# application for SSIS package deployment. Today, we surpassed 1,000 report deployments to production, we’ve also deploy over 270 SSIS package changes in about a year.

So when it came to upgrading our BI platform from SQL 2008 to SQL 2014, one of the key things I want was deployment automation. The SQL 2008 platform required manual deployments which often lead to mistakes and ended up writing the entire day off, per deployment. Unfortunately, my current process was pretty basic. Database deployments are idempotent, it drop any objects and recreated them, every time. This is fine for interfaces where tables only hold in transit data, but for BI, the idea of dropping a staging table with 140 million rows that takes over 4 hours to load doesn’t make me want to do any deployments. Luckily, the problem is already solved. SSDT. And there is already a PowerShell step template on the Octopus Deploy Community Library.

Also moving to SQL 2014 allowed me to use the new ISPAC, again, there is already a PowerShell step template on the Octopus Deploy Community Library. There is even a PowerShell step template for SSRS.

The only thing missing was SSAS. After watching Chris Webb’s video tutorial – Cube Deployment, Processing and Admin on Project Botticelli, I decided it had to use Microsoft.AnalysisServices.Deployment.exe. After a bit of scripting and testing, I managed to write a PowerShell that updates the xml config files for the deployment – it sets the ProcessingOption to DoNotProcess’. It updates the Data source – where the cube will refresh the data from. The script isn’t perfect. For starters, what if you have more then one data source? Also what if your not using SQL Server 2014? Still the great thing about open source is that other can update it. Anyone can improve it, its not reliant on me having free time. So hopefully by the time we move to SQL 2016 someone will have already updated it to work with SQL 2016.

In a future post I’m going to blog about Octopus Deploy in a bit more detail and how I’ve setup my SQL Server BI deployment process (in a lot of detail). I’m hoping to try using Microsoft Visual Studio Team Services to build the Octopus packages. Currently I use a on-prem TeamCity, which is excellent, its just… I don’t like managing extra servers when I can use SaaS. I like development over administration.

I’ll leave you will a screenshot of my deployment screen in Octopus Deploy, and yes, that’s one button press to get my changes into UAT


Joys of C#

On Friday, I had a problem raised, they want to change one of the automatic rename jobs which renames and moves export files to a shared drive. Lucky, I had created a SSIS package, which was a SQL Job that runs on a scheduled every 15 mins, which uses a script task to perform the logic. Because we also used OctopusDeploy, this really was going to be a 5 min change.

The summary of the requirement change was this, currently we export files to:

\Client Name\Month Number-Month Name\

so, for example, for the April export for Client A it would be:

\Client A\04-April\

What they wanted to change it was:

\Client Name\Fiscal Month Number-Month Name\

so, for example, for the April export for Client A would become:

\Client A\01-April\

Again, as this was a script task, it was just a question of adding a new method and adding it in. For reference, here is the method I quickly bosh together:

Thanks to some clever upfront work – with both the script task and OctopusDeploy, this change took 5 mins.

Missing SSIS toolbox in Visual Studio 2013 / SQL Server 2014

Stupidly today, I accidently closed the SSIS toolbox. After a bit of unsuccessful searching in the Visual Studio menus, I gave up and done the IT norm and Google it – hoping I wouldn’t have to reinstall.

Luckily I found James Serra had already blogged about it, the only difference is Microsoft has changed the icon, its still in the same place at least


So when is a Visual Studio Toolbox, not a Toolbox? When it’s a SSIS Toolbox.